CODEWITHSUNDEEP
vbaexcel
Gaussian Blur
Gaussian Blur

Reputation: 638

How to access a network excel file with different credentials using vba?

I've created VBA code so that a group of people in our company can "stamp" a word document with a unique number stored in an Excel sheet on a network drive (essentially giving a serial#). These people do not have access to said network drive, but I'd like them to be able to execute the VBA code.

I've been reading over various articles on this site and others for the last couple days, and was able to adapt this post to work for me. However, if I try and execute the VBA code more than once, I get the "multiple connections" error described in the original answer. It seems, then, that the mapped drive is not being removed. Is this a simple syntax issue? Or am i missing something in my RemoveNetworkDrive statement?

ServerShare = "\\Servername\path"
UserName = "domain\username"
Password = "password"

Set NetworkObject = CreateObject("WScript.Network")
Set FSO = CreateObject("Scripting.FileSystemObject")

NetworkObject.MapNetworkDrive "", ServerShare, False, UserName, Password

str_WbPath = ServerShare & "\MRL Number Generator.xlsm"
Set exWb = objExcel.Workbooks.Open(str_WbPath)

'Do Stuff in excel


Set exWb = Nothing
Set FSO = Nothing

NetworkObject.RemoveNetworkDrive ServerShare, True, False

Set NetworkObject = Nothing

Upvotes: 4

Views: 5404

Answers (1)

CyberClaw
CyberClaw

Reputation: 445

I'll tell you I it worked for me. I used a drive, and I removed the drive instead.

'This insures you find a empty drive letter
For i = 67 To 90
    netDrive = Chr(i) & ":"
    If Not fs.DriveExists(netDrive) Then Exit For
Next

'mount network drive on the above selected drive letter
Network.MapNetworkDrive netDrive, ServerShare, False, UserName, Password

'DO STUFF
'DO STUFF

'remove that drive letter
Network.RemoveNetworkDrive netDrive, True, True

I'd put the remove on some error handling as well, otherwise you'll spawn a bunch of drives if you have some error.

Upvotes: 1

Related Questions