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.


---
Message posted from http://www.ExcelForum.com/

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)

"tolgag " wrote in message
...
Hi Paul,

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


---
Message posted from http://www.ExcelForum.com/

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

-----Original Message-----
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)

"tolgag " wrote
in message
...
Hi Paul,

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


---
Message posted from http://www.ExcelForum.com/



.

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" wrote in message
...
Bob ..one small oversight.Having found the file i now need
to open it! <vbg????
-----Original Message-----
Thank god! I was running out of ideas. Told you it was
not too hard <vbg

Enjoy your next project.

Regards

Bob

"Paul" wrote in message
...
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!



.

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)

"Paul" wrote in message
...
Bob ..one small oversight.Having found the file i now
need
to open it! <vbg????
-----Original Message-----
Thank god! I was running out of ideas. Told you it was
not too hard <vbg

Enjoy your next project.

Regards

Bob

"Paul" wrote in
message
...
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!



.



.

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

Bob

"paul" wrote in message
...
you are a legend bob!