• Mapping a Network Drive to a Local Folder

    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 re-pointing 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:
    Code:
    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:
    Code:
    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.)

     

    I'm afraid that you must be logged in to comment or leave a testimonial. I wish it could be otherwise, but I'm trying to keep my site spam free for everyone's benefit. If you don't yet have an account it's completely free to sign up, and a very quick process. Simply click here to Register. Not only can you post a comment here, but it gives you full access to posts questions in our forum as well!

     

    If you already have an account, and just haven't logged in yet, what are you waiting for? Login Now!

  • MVP Logo
  • Recent Forum Posts

    Eflats1

    Auto fill web form

    I am close to completing the macro I need to auto fill a web form. I understand the basics of the code and how they reference the html elements but I...

    Eflats1 Today, 01:25 PM Go to last post
    alansidman

    Pivot table and date groupings

    No toe stepping here. I am at the early stages also. And as we already know, there are many ways to accomplish the same result in Excel. Glad to see...

    alansidman Yesterday, 10:32 PM Go to last post
    p45cal

    Pivot table and date groupings

    I don't mean to stand on other's toes - I'm getting to grips with Power Query etc. and wouldn't mind some feedback.
    I experimented and ended up...

    p45cal Yesterday, 08:14 PM Go to last post
    p45cal

    Hiccups in exporting data to Excel from Notepad

    …and I did it with a macro. See attached with a button which runs the code below. It adds a new sheet to the workbook.

    Code:
    Sub blah()
    Dim Headers()
    ...

    p45cal 2019-05-17, 07:26 PM Go to last post
    alansidman

    Hiccups in exporting data to Excel from Notepad

    Using Power Query, I uploaded your text file and then applied the following Mcode

    Code:
    let
        Source = Csv.Document(File.Contents("C:\Users\alans\Desktop\usergroup.txt"),[Delimiter=",",
    ...

    alansidman 2019-05-17, 07:08 PM Go to last post