change local drive / folder to network folder

L

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
 
J

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
 
L

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
 
D

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top