Smart questions
Smart answers
Smart people
Join Tek-Tips Forums
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • 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!

Join Tek-Tips
*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 from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

GSMike (Programmer) (OP)
10 Apr 02 15:54
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

JimBulger (Programmer)
10 Apr 02 17:26
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.
Helpful Member!(2)  GSMike (Programmer) (OP)
10 Apr 02 17:30
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

JimBulger (Programmer)
10 Apr 02 17:32
what about the Dir() function?
GSMike (Programmer) (OP)
10 Apr 02 17:49
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

JustinEzequiel (Programmer)
10 Apr 02 23:17
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
JustinEzequiel (Programmer)
10 Apr 02 23:25
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
Helpful Member!(2)  xlbo (MIS)
11 Apr 02 5:02
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
Helpful Member!  mikewoodhouse (Programmer)
11 Apr 02 7:10
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
xlbo (MIS)
11 Apr 02 7:34
Mike - get a user defined type not defined when I try to run this - how do I define this...???
TIA
Geoff
GSMike (Programmer) (OP)
11 Apr 02 10:06
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

GSMike (Programmer) (OP)
11 Apr 02 10:27
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

xlbo (MIS)
11 Apr 02 10:46
Hmmm...it would appear that I don't have a Tools>References option - '97 on NT4 - any ideas ??
Geoff
GSMike (Programmer) (OP)
11 Apr 02 10:50
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

RobBroekhuis (TechnicalUser)
11 Apr 02 10:52
The tools>references is in VBE, not in the Excel application.
Rob
xlbo (MIS)
11 Apr 02 10:52
D'OH - VBE Tools > References

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

excellent
Geoff
GSMike (Programmer) (OP)
11 Apr 02 10:56
that semi-colon solves my problem, too.
reciprocating thanks to you!!

Mike K

GSMike (Programmer) (OP)
11 Apr 02 10:56
reciprocating D'OH!!, also

Mike K

xlbo (MIS)
11 Apr 02 10:58
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
GSMike (Programmer) (OP)
11 Apr 02 11:05
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

xlbo (MIS)
11 Apr 02 11:09
Nice one Mike - now you get a star for your own thread :)
Altogether, a nice little bit o' code
Geoff

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!

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