Tonight I had to do some work on my financial model from home. Naturally, I copied the files into my "work stuff" folder on my laptop hard drive, and headed out at the end of the day. But as I disclosed in my last post, it now has (shudder) external links in the file. So in order to work effectively on the model, I needed to replicate the file paths.
If I had a network at home, this would be easy, as I could map a network drive to S:\, upload the files there, and I'm good to go. Actually, I do have this, but this means that I'd have to upload the files to my server, work on them, then download them to take them back to work tomorrow. And I'm way too lazy to do all that. So instead I spent some time working on a better alternative. J
I now have a little VB script which maps the S:\ drive to my "C:\My Documents\Work Stuff" folder. I run the script, and Excel doesn't have a clue that it isn't working with the S:\ drive at work. No repointing of links required, not futzing around uploading files to my server.
Here's the script, (I actually replicate the H:\ drive too) which makes use of the DOS SUBST (or Substitute) command:
Dim objShell, strDrive1, strDrive2, strPath
Set objShell = CreateObject("WScript.Shell")
strDrive1 = "H:"
strDrive2 = "S:"
strPath = """C:\My Documents\Work Stuff"""
objShell.Run "cmd /c SUBST " & strDrive1 & " " & strPath, 1, True
objShell.Run "cmd /c SUBST " & strDrive2 & " " & strPath, 1, True
The trick is that I have to remove these mappings using the SUBST command before returning to work, or my regular network drive mappings won't fire since SUBST is persistent. The script to do that is pretty simple as well:
Dim objShell
Set objShell = CreateObject("WScript.Shell")
On Error Resume Next
objShell.Run "cmd /c SUBST H: /d"
objShell.Run "cmd /c SUBST S: /d"
To make it work, just open a text file, drop the appropriate section of code above, and save the file with a .vbs extension. Double click it and the paths should map.
NOTE: The triple quotes on strPath in the first routine are required to feed a single set of quotes to the cmd. This is necessary to allow spaces in the file path.
Hopefully you'll never find this useful, as that means you're taking your work home!
(NOTE: Much of this can now be avoided with products like OneDrive, DropBox and others.)
7 thoughts on “Mapping a Network Drive to a Local Folder”
One thing to say - FileSync.
Does it work with Windows7? 64bit? I only see OS's listed up to XP on their site...
The script you use can also easily be done with a batch command (.cmd/.bat)
I've found that some programs (and this has included Office programs) do not like the DOS SUBST command and actually complains that it's not a network drive or cannot find it. The workaround I've found is to actually use the Map Network Drive under the Tools Menu of Windows Explorer, just point the folder to your local machine and it works like a charm. If you leave the Reconnect at login turned off, you don't have to worry about remapping them when you get back to your office network (provided you actually shut down the machine). Network mapping can also be accomplished using VBScript or batch (net use) files.
Nick,
True, you could use a bat or cmd file. I have a vbs script I run to map my work/home network drives, which is why I went that route.
I'm a little stumped though on your second comment. How can you get a local drive mapped using the Network Drive command? So far as I can see, you only have the option of mapping to \\server\share path. Using drive letters doesn't seem to be an option, which is necessary to do a local drive, no?
First, you would need to share the drive/folder, and for added security, add permissions to only your windows account. Once the drive/folder is shared, you should be able to map it as a Network Drive.
Ah, okay thanks. I'll have to give that a shot!
LiveSync works fine with WIn7, in fact it won't work with XP soon.