Solved: Map Network drive and save

[gibbo1715]

All

I need to map a network drive (Q:\) no password when i click a button and then save the workbook into a location on that drive

Can anyone give me a clue how to do this please

Cheers

Gibbo

[Ken Puls]

HI Gibbo,

I found this to map a Shared Network Drive (http://www.utteraccess.com/forums/showflat.php?Cat=&Number=417549&Main=417549) using a windows API call.

I haven't tried it myself, but if you get it to work, some variation of it would make a great addition to the KB. :)

EDIT: Win API calls to add/remove network drives are: wnetaddconnection & wnetcancelconnection

[gibbo1715]

Thanks Ken

Will have a play tomorrow and see if i can get this to work

I ll let u know how i get on

Gibbo

[Justinlabenne]

No api route:
Option Explicit

Public Function fMapDrive(ByVal szDrive As String, _
ByVal szServer As String, _
ByVal szShareName As String, _
Optional ByVal szUserName As String, _
Optional ByVal szPassword As String) As Boolean

Dim szMapRoute As String
Dim WshNet As Object
Set WshNet = CreateObject("WScript.Network")

On Error GoTo ErrorHandle
szMapRoute = "\\" & szServer & "\" & szShareName

If szUserName = "" Then
WshNet.MapNetworkDrive szDrive, szMapRoute
ElseIf szPassword = "" Then
WshNet.MapNetworkDrive szDrive, szMapRoute, , szUserName
Else
WshNet.MapNetworkDrive szDrive, szMapRoute, , szUserName, szPassword
End If

ErrorHandle:
Set WshNet = Nothing
Select Case Err.Number
Case 0
fMapDrive = True
Case -2147024811 'Already mapped
fMapDrive = True
Case Else
MsgBox Err.Description
fMapDrive = False
End Select
End Function


Used like this to save a copy of the calling workbook to the newly mapped or already mapped drive:

Public Sub RemapDrive()
Const szDriveLetter As String = "Q:"
Const szServer As String = "ServerName"
Const szShare As String = "ShareName"


If fMapDrive(szDriveLetter, szServer, szShare) Then _
ThisWorkbook.SaveCopyAs szDriveLetter & "\" & ThisWorkbook.Name
End Sub

[Ken Puls]

Nice, Justin! :)

[gibbo1715]

Thanks all

Below is the quickest way i have found to do what im after ( I ve included Mapping, and Removing Drive) but will have a play with the excellent method above

Gibbo

Sub Button1_Click()
'To Map Drive
Shell "net use Q: \\coms01\coms (file:///coms01coms) '/user:MyDomain\MyUserName MyPassword
ActiveWorkbook.SaveAs " Q:\coms\test.xls (file:///coms01comstest.xls) "
'To Remove Drive
Shell "net use Q: /delete"

'To just save workbook to an area on the network without requiring any mapping the following also seems to work
ActiveWorkbook.SaveAs " \\coms01\coms\test.xls (file://coms01/coms/test.xls) "
End Sub

Dont know where the xls" target="_blank"> (file://coms01/coms/test.<acronym%20STYLE=) bit came from, that shouldnt be there

[doganbaris]

Hello,

all the answer is good ways.

but if you already map a drive on the server, you can not map another drive with different username. This is Windows's blocking.

There is a way, if you want to map more than once driver, go with servername for first connection with user A , and the go with IP address for second connection with user B