Results 1 to 7 of 7

Thread: Change Drive

  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Change Drive

    New software at work downloads a prn file to a location on our network within Network Neighborhood. I recorded a macro which changes directory and opens a specific file. When I run the recorded macro it brings up a dialog box within the current directory.
    1- How can the macro be made to change drive to Network Neighborhood?
    2- How can the macro be made to allow the user to select a file (like *.prn) from the dialog box (instead of the file recorded in this macro?


    ' ChDir "Lv40bQDLSGL2KFLRFNPVDCP"
    ' Workbooks.OpenText FileName:="Lv40bQDLSGL2KFLRFNPVDCP03PAY.PRN", _
    ' Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
    ' xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
    ' Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
    ' Array(2, 1))
    'End Sub

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

    Re: Change Drive

    I think the GetOpenFileName method of the application object might help you. Have a look at
    this thread

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Change Drive

    Hans,
    I tried
    ChDir "Lv40bQDLSGL2KFLRFNPVDCP"
    FileName = Application.GetOpenFilename(FileFilter:="*.*", Filterindex:=2, Title:="Select a file")

    Thanks

    and received Method 'GetOpenFilename' of object_Application' failed. Clicking on help says "Topic does not exist"

    Don't I need to ChDrive "network neighborhood" (or something like this) first?

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

    Re: Change Drive

    As you can see from my previous post, the syntax is like this

    FileName = Application.GetOpenFilename (FileFilter:= sFilter, Filterindex:= Iindex, Title:=sTitle)

    where sFilter is a string e.g. "Excel files (*.xls), *.xls, All Files(*.*), *.*". In this example, you have two different file types, namely *.xls and *.* file types. Each file type is described by some descriptive text and the *.extension separated by commas. Two different file types, this means that the filterindex can be either 1 or 2.

    In your post, you only defined 1 file type and thus the filterindex can only be equal to 1.

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Change Drive

    Thanks Hans. Got it now. Just one more thing, I've got to navigate through several levels of sub-dirs to get to these files.

    I've tried
    ChDrive "network neighborhood"
    and
    ChDrive "Lv40b"
    but neither work.

    Do you know of a way to change the drive so I can address the Network Neighborhood?

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

    Re: Change Drive

    Maybe you should consider the network as another directory and not as another drive. Then ChDir is the command you have to use. I suggest you to use the GetOpenFileName method, browse to a network directory and after selecting a file on that directory, print or display the filename, which is returned by the GetOpenFileName method. As this is a string, it should contain the full path of the file, showing you how VBA accesses the file.

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Change Drive

    Thanks Hans. On your advise I have modified the macro to include the GetOpenFilename statement.

    FileName = Application.GetOpenFilename(FileFilter:="All Files(*.*), *.*", Filterindex:=1, Title:="Select a file")

    I've tried to include the following to access a drive which under Explorer appears in the Network Neighborhood (like ChDrive "F:"
    ChDir "Lv40bQDLSGL2KFLRFNPVDCP"
    But our network security must be preventing access. Guess I'll have to ask our IS department.

Posting Permissions

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