Results 1 to 11 of 11
  1. #1
    Lounger
    Join Date
    Dec 2001
    Location
    Cromwell, Connecticut, USA
    Posts
    40
    Thanks
    2
    Thanked 2 Times in 2 Posts

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

    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

  2. #2
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Jeddah, Saudi Arabia
    Posts
    243
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    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

  3. #3
    Lounger
    Join Date
    Dec 2001
    Location
    Cromwell, Connecticut, USA
    Posts
    40
    Thanks
    2
    Thanked 2 Times in 2 Posts

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

    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

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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:

    <pre>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
    </pre>


  5. #5
    Lounger
    Join Date
    Dec 2001
    Location
    Cromwell, Connecticut, USA
    Posts
    40
    Thanks
    2
    Thanked 2 Times in 2 Posts

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

    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

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.
    Legare Coleman

  7. #7
    Lounger
    Join Date
    Dec 2001
    Location
    Cromwell, Connecticut, USA
    Posts
    40
    Thanks
    2
    Thanked 2 Times in 2 Posts

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

    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

  8. #8
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    John,

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

    <pre>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
    </pre>


  9. #9
    New Lounger
    Join Date
    Nov 2001
    Location
    Amsterdam
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  10. #10
    New Lounger
    Join Date
    Nov 2001
    Location
    Amsterdam
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    I am a liar

    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

  11. #11
    Lounger
    Join Date
    Dec 2001
    Location
    Cromwell, Connecticut, USA
    Posts
    40
    Thanks
    2
    Thanked 2 Times in 2 Posts

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

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •