GetOpenFilename with shared folders (aka ChDrive) (Excel 97 SR-2)

Topic

New Reply

I would like to use the GetOpenFilename method to open up to 100 files in a shared folder. It seems that I need to change the drive to make it work but I can’t get the syntax right. The shared folder was created on a PC named “PC1” and the shared folder name is “PC1-DATA”.

ChDrive “shared pc name” or ChDrive “shared pc name” doesn’t work. Strangely ChDrive “Server name:shared pc name” doesn’t generate an error message but this still returns the default path on the local machine. I expected this code to work but it doesn’t like the ChDrive “PC1”.

Sub SharedImport()
Dim wb as Integer
Dim FileToImport

ChDrive “PC1”
ChDir “PC1PC1-DATA”
FileToImport = Application.GetOpenFilename(“Report File (*.rpt), *.rpt”, , “Import report files into Excel”, , True)

For wb = 1 To UBound(FileToImport)
Workbooks.OpenText FileName:=FileToImport(wb), Origin:=xlWindows, StartRow:=1, Type:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1))
Next wb
End Sub

If I use the macro recorder it simply gives me “ChDir shared pc nameshared folder name”. This doesn’t work if you don’t manually move to the shared folder first. The same holds true if I use Application.DefaultFilePath = “shared pc nameshared folder name”.

I can use workbooks.open filename:= “shared pc nameshared folder namefile name” to open a single file but don’t know how make it work with the GetopenFilename method with multselect enabled.

I’m stuck on this one & really would like to make it work.
Thanks – John

Reply | Quote

Replies

John,

I can’t exactly duplicate your problem but how about this. Map the shared directory to a drive letter, say X, on your local computer and then use ChDrive “X:” This works for me.

Regards,

Kevin Bell

Reply | Quote

Kevin,

I tried that also but it didn’t work. I believe this is because the share is in network neighborhood and not a mapped drive. Maybe my syntax was wrong. What I did on the remote PC named PC1 was turn on sharing for drive C and tried the following in my VBA code from another PC on our network:

ChDrive “PC1C”; ChDrive “PC1C:”; ChDrive “PC1:C” – None of these worked

Do you know if I have to use the “shared pc nameshared drive” syntax or simply the “shared drive” syntax?

I’ll give this share another name (like X) that does’nt correspond to any other drive that may exist & try again.

John

Reply | Quote

John,

I don’t think you need to change drive or directory as the full path is included in the filename returned by the getopenfilename method. I have a small PC-network at home and tried the following code to open workbooks on other PCs with success:

Option Explicit
Public FileName
Sub OpenFilesOnNetworkDrive()
    Dim i As Integer
    Dim Filt
    Dim FiltIndex
    Dim Title
    Title = "Select files"
    Filt = "Excel files (*.xls), *.xls," & "Text files (*.txt), *.txt"
    FiltIndex = 1
    FileName = Application.GetOpenFilename(FileFilter:=Filt, FilterIndex:=FiltIndex, _
                          Title:=Title, MultiSelect:=True)
    For i = 1 To UBound(FileName)
       Workbooks.Open FileName:=FileName(i)
    Next i
End Sub

Reply | Quote

Hans,

The problem that I have resides with the end users. They are not that savvy & they must decide from various files which ones they need to open. What I’m trying to do is have the GetOpenFilename dialog box startup in the correct directory (hence the ChDRive & ChDir) instead of them navigating from their default directory through network neighborhood & then to the shared folder on the PC they wish to connect with.

Any thoughts on what I’m doing wrong (or missing) would be greatly appreciated. Thanks again!

John

Reply | Quote

I don’t have a network available, so I can’t try anything. However, I think that ChDrive only works with the old DOS drive letters. It does not understand UNC specifications. Therefore, I think that the only way to make this work is to map a drive letter to the shared directory on the machine where the macro is running, and then ChDrive to that drive letter.

Reply | Quote

Legare,

That’s what I was afraid of when I couldn’t find any documentation on ChDrive other than what was in the help file. I”ll investigate your suggestion when I get back to work.

Thanks again – John

Reply | Quote

John,

I tried the ChDir and it works for me. I leave out the last backslash in the string containing the path.

Option Explicit
Public FileName
Sub OpenFilesOnNetworkDrive()
    Dim i As Integer
    Dim Filt
    Dim FiltIndex
    Dim Title
    Title = "Select files"
    Filt = "Excel files (*.xls), *.xls," & "Text files (*.txt), *.txt"
    FiltIndex = 1
    ChDir "Serverserver_cMy Documents"
    FileName = Application.GetOpenFilename(FileFilter:=Filt, _ 
       FilterIndex:=FiltIndex, Title:=Title, MultiSelect:=True)
    If Not IsArray(FileName) Then Exit Sub
    For i = 1 To UBound(FileName)
       Workbooks.Open FileName:=FileName(i)
    Next i
End Sub

Reply | Quote

Hello

Works for me too, even with the final backslash in the path (excel 97). Thanks for all the info on this very useful thread

Cheers

Mark

Reply | Quote

Sorry, no it didn’t work – it didn’t error either…

In the end changing drive first and then changing path worked ok. In this case, template.xls and test.xls are on a mapped network drive in the same folder (not the root):

Dim wbkCopyFrom As Workbook
Set wbkCopyFrom = Workbooks(“test.xls”)
Dim strPath As String
strPath = wbkCopyFrom.Path
ChDrive Left(strPath, 1)
ChDir strPath
Application.Workbooks.Open (“template.xls”)

Don’t know if this is an option for you

Mark

Reply | Quote

Mapping the shared drive/directory as a network drive did the trick. Thanks to Kevin, Hans, Legare et al for your help. I guess that will teach me to pull my head out of the code & look around once in a while!!

John

Reply | Quote