File not opening when using UNC path

[makeitwork09]

I am using Excel 2007

Since different users have a different drive letter for the same UNC path,
I would like to set up the VBA code to use the UNC path rather than using
the drive letters.

I attempted to use the following snippet of code, but after selecting the
file to open, the file did not open and I remained in the workbook that
called the open dialog box. As a result, I could not later save the file
with a different name in the same location (that code not shown)

Option Explicit

Private Declare Function SetCurrentDirectory _
Lib "kernel32" Alias "SetCurrentDirectoryA" (ByVal lpPathName As String) As
Long

Private Sub ChangeDirUNC_API()
Dim vFileToOpen As Variant
Dim strCurDir As String

'// Keep Original Dir
strCurDir = CurDir

'// Note: If the UNC path does not exist then
'// It will default to your current one
SetCurrentDirectory "\\something\somethingelse\folder"
vFileToOpen = Application.GetOpenFilename(filefilter:="Text Files *.csv
(*.csv),")
If TypeName(vFileToOpen) = "Boolean" Then Exit Sub

Where am I going wrong or what should I do instead to handle the different drive letter situation.

Thanks

 

[SkipVought]

hi,

In general, I would advise against OPENING a text file with Excel. Rather IMPORT the text file using the Data File IMPORT feature, as you can control the column types to convert or avoid conversion as required.

With this method, you can also PROMPT on refresh using the appropriate path.



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[makeitwork09]

Hi Skip,

What if I was trying to default the user to the dialog box with only Excel spreadsheets, do you know how I would I get the above to work?