PDA

View Full Version : Solved: open network drive



CCkfm2000
11-30-2006, 05:17 AM
hi all..


i'm trying to

1. Connect to a network drive.
2. Open spreadsheet.
3. Update the links ( automatically done ).
4. Save and exit spreadsheet.
5. Close network connection.

Below is the vba code I?m using.

Sub opennetworklinkfile()
Shell ("net use x: /d")
Shell ("net use x: \\festini\coldstore$ (file://\\festini\coldstore$) /user:wms\coldxp coldxp")
ChDir "X:\Data Transfer"
Workbooks.Open Filename:="X:\Data Transfer\Link to the Gold Plan file.xls", _
UpdateLinks:=3
Range("H1").Select
ActiveWorkbook.Save
ActiveWindow.Close
Shell ("net use x: /d")
End Sub

I get a run-time error ?76? ?Path Not Found?. :banghead:

any ideas.

thanks

CBrine
11-30-2006, 07:20 AM
Here's a little more rebuost way to map the network drive.



Dim WshShell As Object, WshNetwork As Object
'Map Drive
DriveLetter = "H:" 'must be capitalized
RemotePath = "\\ServerName\common (file://ServerName/common)"
WshNetwork.MapNetworkDrive DriveLetter, RemotePath


Give that a try and let us know what happens.

HTH
Cal

CCkfm2000
11-30-2006, 08:40 AM
thanks for the reply CBrine,

still lost with your code, so i've been playing around with my code and got it to work.



Sub opennetworklinkfile()
Shell "net use X: \\festini\coldstore$ (file://\\festini\coldstore$) /user:WMS\coldxp coldxp"
ChDir "X:"
Workbooks.Open Filename:="X:\Data Transfer\Link to the Gold Plan file.xls", _
UpdateLinks:=3
Range("H1").Select
ActiveWorkbook.Save
ActiveWindow.Close
Shell "net use X: / delete"
Shell "net use X: / d"
End Sub


if you have any more ideas please reply back.

thanks again.

CBrine
11-30-2006, 09:01 AM
CCKfm2000,
No worries, as long as your have it working. There is some reduntant code you may want to remove.


sub opennetworklinkfile()
Shell "net use X: \\festini\coldstore$ (file://festini/coldstore$) /user:WMS\coldxp coldxp"
ChDir "X:"
Workbooks.Open Filename:="X:\Data Transfer\Link to the Gold Plan file.xls", _
UpdateLinks:=3
ActiveWorkbook.close true
Shell "net use X: / delete"


Shell "net use X: / d"
End Sub

CCkfm2000
11-30-2006, 09:19 AM
thanks CBrine,

will update my spreadsheet.