INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Jobs from Indeed

Mapping a drive...

Mapping a drive...

(OP)
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

RE: Mapping a drive...

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.

RE: Mapping a drive...

2
(OP)
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

RE: Mapping a drive...

what about the Dir() function?

RE: Mapping a drive...

(OP)
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

RE: Mapping a drive...

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

RE: Mapping a drive...

could not find it but figured it out
from an MSWord macro, you could use

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

RE: Mapping a drive...

2
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

RE: Mapping a drive...

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

RE: Mapping a drive...

Mike - get a user defined type not defined when I try to run this - how do I define this...???
TIA
Geoff

RE: Mapping a drive...

(OP)
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:
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:
 Automation error.
The specified device name is invalid.

mikewoodhouse, any suggestions??

Mike K

RE: Mapping a drive...

(OP)
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

RE: Mapping a drive...

Hmmm...it would appear that I don't have a Tools>References option - '97 on NT4 - any ideas ??
Geoff

RE: Mapping a drive...

(OP)
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

RE: Mapping a drive...

The tools>references is in VBE, not in the Excel application.
Rob

RE: Mapping a drive...

D'OH - VBE Tools > References

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

excellent
Geoff

RE: Mapping a drive...

(OP)
that semi-colon solves my problem, too.
reciprocating thanks to you!!

Mike K

RE: Mapping a drive...

(OP)
reciprocating D'OH!!, also

Mike K

RE: Mapping a drive...

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

RE: Mapping a drive...

(OP)
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

RE: Mapping a drive...

Nice one Mike - now you get a star for your own thread :)
Altogether, a nice little bit o' code
Geoff

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close