path names in getopenfilename

[Paul Shepherd]

Sub macroopen()
ChDir "\\wxp2000\Joe Docs\Joe\Reports"
Application.GetOpenFilename("Excel,*.xls")
End Sub

how i include the path name in the get file name so that
users dont have to click through all the "layers"

 

[tolgag]

Hi Paul,

You can't change the path in this dialog. It can change only the active
drive or active directory.

 

[Bob Phillips]

Paul,

You can't change the path in the dialog, but I think you already knew that
as you were trying a ChDir before the dialog. This works with local, or
mapped drives, but will not work with a UNC path. That is a bit harder, but
not too hard.

Here is some code that will set the UNC path for you, and GetOpenFileName
will reflect that path.

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

Sub macroopen()
Dim sPath As String

sPath = "\\wxp2000\Joe Docs\Joe\Reports"
If SetUNCPath(sPath) <> 0 Then
Application.GetOpenFilename ("Excel,*.xls")
Else
MsgBox "Error in setting the UNC path - " & sPath
End If

End Sub


Function SetUNCPath(sPath As String) As Long
Dim lReturn As Long
lReturn = SetCurrentDirectoryA(sPath)
SetUNCPath = lReturn
End Function


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Paul]

Whew I am glad you think thats not to hard!!.I cant quite
make sense of how those two routines fit together.I get a
compile error saying the function or sub is not defined

 

[Bob Phillips]

MacroOpen is basically your original macro, but instead of doing a ChDir UNC
path, it calls my SetUNCPath function with that UNC path. The function sets
the path, and returns success or failure. This is tested back in the
Macroopen routine to see whether to do a call to GetOpenFileName.

If you are getting a Function or Sub not defined, you must have made an
error. You can see that the SetUNCPath exists, it is only half a dozen lines
down.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Paul]

i am sorry Bob but this is what i have pasted into my test
book.I get the compile error on the line below my comment
Sub macroopen()
Dim sPath As String

sPath = "\\wxp107475\Brians Docs\Brian\Fin"
If SetUNCPath(sPath) <> 0 Then
Application.GetOpenFilename ("Excel,*.xls")
Else
MsgBox "Error in setting the UNC path - " & sPath
End If

End Sub


Function SetUNCPath(sPath As String) As Long

Dim lReturn As Long
'next line is the one which returns compile error
lReturn = SetCurrentDirectoryA(sPath)
SetUNCPath = lReturn
End Function

 

[Bob Phillips]

Paul,

Did you paste this bit of code into that same code module?

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

It must be outside of, and before, any macros.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[paul]

ahaaa no!

-----Original Message-----
Paul,

Did you paste this bit of code into that same code module?

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

It must be outside of, and before, any macros.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)




.

 

[Paul]

Bob it works like a charm.Now to go back to my new vba
book by reed jacobsen..Hopefully my next project will be
covered by it!!Thanks for your help!

 

[Bob Phillips]

Thank god! I was running out of ideas. Told you it was not too hard <vbg>

Enjoy your next project.

Regards

Bob

 

[Paul]

Bob ..one small oversight.Having found the file i now need

 

[Bob Phillips]

Hi again Paul,

Sub macroopen()
Dim sPath As String
Dim sFile

sPath = "\\wxp2000\Joe Docs\Joe\Reports"
If SetUNCPath(sPath) = 0 Then
MsgBox "Error in setting the UNC path - " & sPath
Else
sFile = Application.GetOpenFilename("Excel,*.xls")
If sFile <> False Then
Workbooks.Open Filename:=sFile
End If
End If

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[paul]

you are a legend bob!

-----Original Message-----
Hi again Paul,

Sub macroopen()
Dim sPath As String
Dim sFile

sPath = "\\wxp2000\Joe Docs\Joe\Reports"
If SetUNCPath(sPath) = 0 Then
MsgBox "Error in setting the UNC path - " & sPath
Else
sFile = Application.GetOpenFilename("Excel,*.xls")
If sFile <> False Then
Workbooks.Open Filename:=sFile
End If
End If

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)




.

 

[Bob Phillips]

Why thank-you sir. If I could only convince my family<vbg>!

Bob