UNC Path

Universal Naming Convention (UNC) is a method of displaying a path that will work for everyone connected to your network. Its yang is mapped drives, which are specific to each machine. For example, I have a server named Server1 and I’ve mapped my Q drive to the Accounting folder under Server1. I could access that folder using the UNC method

\Server1\Accounting\MyBook.xls

or the mapped drive method

Q:\MyBook.xls

If I want my co-worker to open MyBook, she would have to have ‘Q’ mapped the same as me in order to use Q:\MyBook.xls, but the UNC method will work for her and everyone else.

I recently had to convert mapped drives to UNC paths in a VBA project. Actually, I had to compare the .Path property of the ActiveWorkbook to a saved UNC path to determine if the workbook was saved in the proper folder. If the user opened the ActiveWorkbook by navigating through his mapped drive, the .Path property was guaranteed not to match (from a string comparison perspective), and the program would assume that the workbook was saved improperly.

My quick fix was to no longer compare the paths, but compare the files in each of the paths. I include it here only for information, not because you should ever use this method. It got the job done, however, until I could implement a more robust method. I used a three part test to determine if the ActiveWorkbook was saved in my predetermined path: First make sure the workbook was saved (Len(ActiveWorkbook.Path)>0), then make sure the file exists in the path (Len(Dir(gsPATH & ActiveWorkbook.Name))>0), and last make sure the first file in each of the paths was the same (Dir(gsPath, vbNormal)=Dir(ActiveWorkbook.Path & "", vbNormal)). It’s easy to see that this is fraught with danger, but it was quick and was a nice band aid.

Next I went to Karl Peterson’s UncName Page to learn about the API I needed. Karl has a lot of stuff in there that I don’t need, but it was very instructive to read through his code.

Today, I read a post at SpyJournal about sending links via email instead of attaching documents. It was a great post and it’s a good use of the Web toolbar (the only good use to date, by the way). It got me thinking that the Address control on the Web toolbar could be used to get the UNC path for a workbook. The workbook would have to be open and active, but if that is the case, it’s a simple way to get the UNC path.

From the Immediate Window:

?application.CommandBars("Web").Controls("Address:").text
\\Server1\AllCompanies\DKInc\Production\APM1553\Purchase.xls
?activeworkbook.FullName
S:\DKInc\Production\APM1553\Purchase.xls

19 thoughts on “UNC Path

  1. List mapped drive

    Sub NetworkMapDrive()

    Set WshNetwork = CreateObject(“WScript.Network”)
    Set oDrives = WshNetwork.EnumNetworkDrives
    DrivesStr = “Network drive Mappings:” & Chr(13)
    For i = 0 To oDrives.Count – 1 Step 2
    DrivesStr = DrivesStr & “Drive ” & oDrives.Item(i) & ” = ” & oDrives.Item(i + 1) & Chr(13)
    Next
    MsgBox DrivesStr
    End Sub

  2. Emily

    That was a useful piece of code – thank you very much. Good topic too, Dick :-)

    Richard

  3. Emily,

    I just ran into a problem that your code helped solve. Thanks, and thanks Dick for bringing up the topic.

  4. Emily,

    I am having problems getting your code to run –

    Compile error: Syntax error

    This happens on the following lines (both are coloured red in the VBA editor):
    DrivesStr = “Network drive Mappings:” & Chr(13)
    DrivesStr = DrivesStr & “Drive ” & oDrives.Item(i) & ” = ” & oDrives.Item(i + 1) & Chr(13)

    Anyone else have this problem?

    Thanks,

    Simon

  5. Sorry, ignore last comment – Where I had copied and pasted the code from the page the ” characters needed to be replaced in the VBA editor…

    Thanks!

  6. I want this code in C#. I am not getting the text property of my commandbar button. how to handle it in C#?

  7. I need to do the opposite of what you did in your article.
    Our IT company keeps making changes & the UNC links in Excel become invalid because of it.
    We need to use the mapped drive letter system so when the IT guys change things they don’t mess us up.
    The only kind of link I’ve been able to put into Excel is a UNC, I can browse to the mapped drive letter when I insert the link, but after I’m finished it automatically converts it into a UNC.
    How do I keep this from happening? I’m using Excel 2003 SP1

    Thanks in advance.

  8. Emily! THANK YOU!

    MS Access VBA cannot handle UNC when connecting to databases on networked drives. I’ve been writing up an access application for the past month that connects out to a database on a network drive, but everyone using the program has a different drive letter. You’re little bit of code saved the day! I was able to search for the server and determine the drive, then pass that back to a docmd.transferdatabase.

    Bravo!

  9. Hi All

    Set WshNetwork = CreateObject(“WScript.Network”)
    the above line is giving me runtime error 424 any1 experience the problem..
    i changed the code from
    DrivesStr = “Network drive Mappings:” & Chr(13)
    to DrivesStr = “Q:” & Chr(13)
    and
    DrivesStr = DrivesStr & “Drive ” & oDrives.Item(i) & ” = ” & oDrives.Item(i + 1) & Chr(13) to
    DrivesStr = DrivesStr & “Q ” & oDrives.Item(i) & ” = ” & oDrives.Item(i + 1) & Chr(13)

    were these the correct changes?
    appreciate your help thanks,
    Fraz

  10. Hi all,

    I’m having problems writing some VBA code in Excel where the openfile dialog box opens to a specific UNC path, as the drive letter for the path is different on everyone’s PC but alot of people will need to use the macro I’m writing.

    Please can someone show me some basic code that any user can run which will open to a default UNC path?

    Thanks,
    Jack

  11. @Jack
    If you browse to the folder in My Computer using ‘My Network Places’ then the UNC path will be displayed in the address bar. Note that you will need to enable the Folder option ‘Display full path in the address bar’ in Tools>View to see the full path.

    Alternatively, your network administrator should be able to tell you the UNC path to the folder.

  12. Hi Dick,
    Nice article !

    I like this code:

    ?application.CommandBars(“Web”).Controls(“Address:”).text
    \Server1AllCompaniesDKIncProductionAPM1553Purchase.xls

    In my case I have many excel files open at the same time. Is there a way to point directly to a file without having to select/activate it? something like using “ThisWorkbook” instead of “Application”?

    Thank you very much.

  13. I am trying to get network printers to work from buttons in excel. Special users so the easier I make it the easier my life is etc…

    Private Sub CommandButton2_Click()
      shell “cmd /c “ & “\617DOM02HL_QUAYSREACH_XEROXM35”, vbNormalFocus
    End Sub

    This doesn’t seem to work, can anyone see any glaring errors?

  14. Emily’s code works well and confirms that my R: drive is mapped to \\server1\folder1.

    However, when I try to execute a batch file on R: as follows
    Set wsh = VBA.CreateObject(“WScript.Shell”)
    errorCode1 = wsh.Run(“R:\file1.bat”, windowStyle, waitOnReturn)
    the shell behaves as if I called \\server1\folder1\file1.bat! Since the batch file does not work with UNC paths this obviously is a problem.

    Using ChDrive and ChDir beforehand doesn’t help. Does anyone have any ideas?

  15. Sorry, having tremendous issue with this vba….

    my shared location is an IP address, dont really know how to write it on the vba that Emily gave… can someone help me?


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.