4

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
Luuklag
  • 3,897
  • 11
  • 38
  • 57
Gaussian Blur
  • 628
  • 1
  • 7
  • 10
  • Have you tries closing the excel workbook before shutting down the network drive? It might not like the connection being force down with files still open – Rabbitman14 Jul 26 '16 at 08:57
  • 2
    The `IPC$` session to the machine may still be open. Try removing `\\servername\ipc$` as well. If that doesn't work, see [this question on SU](http://serverfault.com/questions/451387/) and its linked question for more inspiration (not all of the proposed solutions are possible from VB, though). `EnumNetworkDrives` may also give you a clue as to whether the removal actually failed. – Jeroen Mostert Jul 26 '16 at 11:11
  • @JeroenMostert A good suggestion but I get a "this drive does not exist" error. – Gaussian Blur Jul 26 '16 at 17:53
  • @Rabbitman14 my apologies, also a good suggestion but there is a close workbook line just above the Set exWb = Nothing that I cut out – Gaussian Blur Jul 26 '16 at 17:54
  • @JeroenMostert that article has been very helpful in troubleshooting; thanks! +1 – Gaussian Blur Jul 27 '16 at 15:20

1 Answers1

1

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.

CyberClaw
  • 435
  • 3
  • 13
  • So you're saying that using a drive letter works and that UNC doesn't? – Gaussian Blur Jul 26 '16 at 18:32
  • The MS article says the UNC path should work, but then you say it doesn't recognize he drive he is trying to remove. Other than trying adding a slash at the end of your UNC path, I'm at loss why it wouldn't remove it. Might even be a bug on how Windows handles things. I can guarantee you with a drive letter it works though, because I use it to open/close a network folder with username/password multiple times per user/session. – CyberClaw Jul 27 '16 at 09:09
  • Using a letter does work... I'll have to keep working with it as I don't want to risk spamming drives on an error. Thanks for your help! – Gaussian Blur Jul 27 '16 at 14:59