Change Drive

[Arcturus16a]

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

[Hans Pottel]

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

[Arcturus16a]

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?

[Hans Pottel]

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.

[Arcturus16a]

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?

[Hans Pottel]

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.

[Arcturus16a]

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.