Changing to Network Directory
 

I'm trying to create a macro, which when executed, will
display the "File Open" dialog box for Excel. However I
want the dialog box to open up in a Network directory.

The below macro executes just fine when tested in a blank
workbook. When I add the code to the application I've
created I can't seem to get to the Network directory.

Can anyone recommend a solution or tell me why this macro
isn't working?

Given:
The Network Workgroup is "Advantage".
The Network computer name is "Advint01".
The Network Drive is: "D:\" but displayed as "D Drive".
The Operating System on the desktop is "Windows XP"
The Operating System on the Network is "Windows 2000"
The Excel Version is "2002"

Code:

Sub File_Open_Network()
' Uses the File Open dialog box for the Network
' directory where all estimate files are store
ChDir "\\Advint01\D Drive\Quotes\Contracting"
fileToOpen = Application.GetOpenFilename_
("WorkSheets (*.xls), *.xls", , "Network Drive")
End Sub

Changing to Network Directory
 

' Placed at the top of the module
Private Declare Function SetCurrentDirectoryA Lib _
"kernel32" (ByVal lpPathName As String) As Long

Sub ChDirNet(szPath As String)
' provided by Rob Bovey
Dim lReturn As Long
lReturn = SetCurrentDirectoryA(szPath)
' Debug.Print lReturn
If lReturn = 0 Then Err.Raise vbObjectError + 1, "Error setting path."
End Sub

Sub File_Open_Network()
' Uses the File Open dialog box for the Network
' directory where all estimate files are store
'
' use chdirnet rather than chdir
'

ChDirNet "\\Advint01\D Drive\Quotes\Contracting"
fileToOpen = Application.GetOpenFilename_
("WorkSheets (*.xls), *.xls", , "Network Drive")
End Sub


--
Regards,
Tom Ogilvy

"Dan Marr" wrote in message
...
I'm trying to create a macro, which when executed, will
display the "File Open" dialog box for Excel. However I
want the dialog box to open up in a Network directory.

The below macro executes just fine when tested in a blank
workbook. When I add the code to the application I've
created I can't seem to get to the Network directory.

Can anyone recommend a solution or tell me why this macro
isn't working?

Given:
The Network Workgroup is "Advantage".
The Network computer name is "Advint01".
The Network Drive is: "D:\" but displayed as "D Drive".
The Operating System on the desktop is "Windows XP"
The Operating System on the Network is "Windows 2000"
The Excel Version is "2002"

Code:

Sub File_Open_Network()
' Uses the File Open dialog box for the Network
' directory where all estimate files are store
ChDir "\\Advint01\D Drive\Quotes\Contracting"
fileToOpen = Application.GetOpenFilename_
("WorkSheets (*.xls), *.xls", , "Network Drive")
End Sub