path names in getopenfilename

  • Thread starter Thread starter Paul Shepherd
  • Start date Start date
P

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"
 
Hi Paul,

You can't change the path in this dialog. It can change only the active
drive or active directory.
 
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)
 
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
 
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)
 
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
 
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)
 
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)




.
Click to expand...
 
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!
 
Thank god! I was running out of ideas. Told you it was not too hard <vbg>

Enjoy your next project.

Regards

Bob
 
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)
 
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)




.
Click to expand...
 
Back
Top