change local drive / folder to network folder

[Ludo]

Hi,

I need to read data from various excel files on a netwerk connected
PC.
I can read the info when nothing goes wrong with the filename, but if
there's a typo in the filename (manual input), this one can't be found
on the network PC, so i reply with a messagebox with a selection
Retry / Cancel.

When Retry is selected, i change the default drive to the network
location where the file resides so that the files in the xlDialogOpen
are the ones in this location, but it won't work.

i get an " Run-time error '5' " - " Invalid procedure call or
argument"

The network location is something like this :

\\Kndclt21079\F TMG\Cat\Ate_Hass1\Logging\HASS_PowerMeasurements\

this is part of the code i use:

Dim CurFolder As String
Dim NewFolder As String

CurFolder = CurDir
..
..
..


'open dialog to select TMG file
NewFolder = KastNummer '= \\Kndclt21079\F TMG\Cat
\Ate_Hass1\Logging\HASS_PowerMeasurements\
ChDrive NewFolder
ChDir NewFolder

dlgAnswer = Application.Dialogs(xlDialogOpen).Show
'change back
ChDrive CurFolder
ChDir CurFolder
Exit Sub


Any help apreciated.
Thanks in advance
Ludo

 

[Joel]

Newfolder shouldn't have a backslash as the last charater.
:


I think the better solution is to use the filedialog and set the initial
folder

Set fd = Application.FileDialog(msoFileDialogFilePicker)

'Declare a variable to contain the path
'of each selected item. Even though the path is a String,
'the variable must be a Variant because For Each...Next
'routines only work with Variants and Objects.
Dim vrtSelectedItem As Variant

'Use a With...End With block to reference the FileDialog object.
With fd

'Set the initial path to the KastNummer
'= \\Kndclt21079\F TMG\Cat\Ate_Hass1\Logging\HASS_PowerMeasurements\
.InitialFileName = KastNummer

'Use the Show method to display the File Picker dialog box and
return the
'user's action.
'If the user presses the action button...
If .Show = -1 Then
dlgAnswer = .SelectedItems
end if
end with

 

[Ludo]

Newfolder shouldn't have a backslash as the last charater.

:

I think the better solution is to use the filedialog and set the initial
folder

   Set fd = Application.FileDialog(msoFileDialogFilePicker)

    'Declare a variable to contain the path
    'of each selected item. Even though the path is a String,
    'the variable must be a Variant because For Each...Next
    'routines only work with Variants and Objects.
    Dim vrtSelectedItem As Variant

    'Use a With...End With block to reference the FileDialog object.
    With fd

        'Set the initial path to the KastNummer  
        '= \\Kndclt21079\F TMG\Cat\Ate_Hass1\Logging\HASS_PowerMeasurements\
        .InitialFileName = KastNummer

        'Use the Show method to display the File Picker dialog box and
return the  
        'user's action.
        'If the user presses the action button...
        If .Show = -1 Then
           dlgAnswer = .SelectedItems
        end if
    end with















- Tekst uit oorspronkelijk bericht weergeven -

Hi Joel,

Tried this before and even when i remove the backslash on the end, it
returns me the same error
code i used to strip the last character:

'open dialog to select TMG file
NewFolder = Left(KastNummer, Len(KastNummer) - 1) ' \
\Kndclt21079\F TMG\Cat\Ate_Hass1\Logging\HASS_PowerMeasurements
ChDrive NewFolder
ChDir NewFolder


Ludo

 

[Dave Peterson]

Read Joel's response once more. He doesn't use ChDrive or ChDir. Just the
..initialfilename parm.

(chdrive won't work with UNC paths.)

There is an API function that would work, but try Joel's suggestion first.