Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Mapping a drive... 5

Status
Not open for further replies.

GSMike

Programmer
Feb 7, 2001
143
US
Is there a way to map a drive using VBA?
Or do you just have to write a batch file and shell in the batch file from VBA?
Thanking you in advance. Mike K
 
why do you want to map the drive? If you have the UNC it is really unnecessary. Also, the programmatic mapping might conflict with user defined maps.
 
Yes, I see what you mean.
In this case, however, I am testing for the existence of a certain drive, and if non-existent, I am mapping the required drive (the drive is required for a program installation), retrieving the required data from the UNC via the specified drive, doing the install, and then disconnecting the drive.
Thanks for your help. Mike K
 
Do you mean for the drive existence test?
For that I am using the DriveExists property of the FileSystemObject object.
The only piece of the puzzle I'm missing is how to actually map the drive, if it doesn't exist. For this, I can easily use a batch file, but I thought there might be a way to do this in VBA.
Thanks. Mike K
 
there is a way because I did it before (long ago)
However, I cannot find the code
will come back if I do find it
 
could not find it but figured it out
from an MSWord macro, you could use

Code:
Dim dlg As Dialog
Set dlg = Dialogs(wdDialogConnect)

dlg.drive = 0 ' select first available drive letter
dlg.Path = "\\server\drive"
dlg.Password = "yourpassword"
dlg.Execute

Set dlg = Nothing
 
Managed to find something in some archives and cannibalised it - works....but only if the code is run - that may sound dense but this code doesn't work if you call the macro from another sub - you need to write the code into whatever your current code is.
Hope that's clear and here's the code :
Sub mapDrive()

On Error GoTo Line1


Drive = "s"

Open "C:\connect.bat" For Output As #1
Print #1, "NET USE " & Drive & ": /delete /y"
Print #1, "NET USE " & Drive & ": \\Yorkshire\shared"
'Previous line is your new mapping
Close

Call Shell("C:\connect.bat", vbHide)

Open "C:\disconnect.bat" For Output As #1
Print #1, "NET USE " & Drive & ": /delete /y"
Close

Call Shell("C:\disconnect.bat", vbHide)


Exit Sub
Line1:
'Error trapping
MsgBox "A Drive cannot be mapped." & vbCrLf & vbCrLf & _
" Please contact xxxx On" & vbCrLf & vbCrLf & _
" (3707)", Title:=""

End Sub

Please note that this code doesn't seem to overwrite mappings - will create a mapping only if one doesn't exist. Also, it takes a bit of time for explorer to catch up so you can't open / save a file using the new mapping for a coupla secs after it's run.

HTH
Geoff
 
Take a look at the Windows Script Host Object Model (wshom.ocx) which contains a nifty object for playing with network things. I use it mainly in VBScript but (just checked) it seems to work fine in Excel.

e.g.

dim wshNetwork As WshNetwork

Set wshNetwork = new WshNetwork
wshNetWork.MapNetworkDrive "X", "\\MyServer\", True
 
Mike - get a user defined type not defined when I try to run this - how do I define this...???
TIA
Geoff
 
Geoff,
To get rid of that error message, set a reference to the Windows Script Host Object Model. In Excel, go to Tools > References, and then select it.
After that, I did:
Code:
Dim wshNetwork As New IWshRuntimeLibrary.IWshNetwork_Class
wshNetwork.MapNetworkDrive "R", "\\myserver\myshare", True
However, at this point I get a different error message. It says:
Code:
Automation error.
The specified device name is invalid.
mikewoodhouse, any suggestions?? Mike K
 
JustinEziquiel,
I tried your suggestion in Excel, and apparently Excel doesn't make that dialog box available. I went over to MSWord and saw that it does work there.
Thanks for the suggestion. I need to make it work in Excel.
It seems that the way I'm going here is with the FileSystemObject (creating a text file with a bat extension), and then shelling that file in.
That seems to work great for me.
Thanks again. Mike K
 
Hmmm...it would appear that I don't have a Tools>References option - '97 on NT4 - any ideas ??
Geoff
 
Where are you? In the VBA editor in Excel? I.E., What application are you running, and where are you in the app? Mike K
 
D'OH ;-) - VBE Tools > References

got it to work by the way - wshNetwork.MapNetworkDrive "R"
should be wshNetwork.MapNetworkDrive "R:"

excellent
Geoff
 
that semi-colon solves my problem, too.
reciprocating thanks to you!! Mike K
 
Ok - so now I can map a drive much more concisely
GSMike - can you post the code you're using to test for existence of the drive - that plus
sub MapDrive()
Dim wshNetwork As New IWshRuntimeLibrary.IWshNetwork_Class
wshNetwork.MapNetworkDrive "R:", "\\myserver\myshare", True
End Sub

would be a very nice bit of code
Geoff
 
Code:
sub lookforandmapadrive()
dim fso a new filesystemobject, wshN as new iwshruntimelibrary.iwshnetwork_class
if fso.driveexists("R")=false then
   wshN.mapnetworkdrive "r:","\\myserver\myshare",true
end if
end sub
p.s. to get to the filesystemobject library, set a reference to Microsoft Scripting Runtime (via Tools > References, etc.)
Best of success Mike K
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top