Get UNC Filenames

Get UNC Filenames

If you work on a network with mapped servers or drives you may sometimes need a full UNC filename in it’s unmapped state. For example, computers in different departments may refer to the same location by different mappings. Say you work in the Pastry Procurement department and your computer refers to \\AcmeServer0023\Top_Secret_Pie_Recipes as T:\Top_Secret_Pie_Recipes, but your co-worker in Puddings and Pecan Puffs sees it as P:\Top_Secret_Pie_Recipes. If you send them a link using your mapping of “T:\” it won’t work for them. This post solves that problem by converting the mapped path – the one that starts with “T:\” – to its UNC equivalent – the one that starts with “\\AcmeServer0023.”

There are some fine methods out there for doing part of this conversion using VBA such as this comment by Emily at Daily Dose of Excel. That post also includes a method Dick discovered using the generally-reviled Web toolbar (I used to have a macro whose sole function was to hide that useless thing whenever it popped back up) but it only works for open workbooks.

This post incorporates existing Office functionality whereby links created with mapped drives actually show the UNC drive when you edit them. Here I’ve opened the Add Hyperlink dialog and selected a file. Note that the Address box at the bottom shows the mapped filename:

Hyperlink dialog

After closing the dialog and then opening the Edit Hyperlink dialog the Address box shows the UNC filename:

Edit hyperlink dialog

I like this method because it gives you the whole shebang and it works for all file types, not just Excel workbooks. Of course, it’s boring to generate more than a couple, so I wrote some VBA to automate the process. That way I can point to one or more files and paste their UNC monikers wherever I want.

This is a fairly trivial task and I’d rather not wipe out Excel’s Undo stack with the VBA. Since this hyperlink trick works just as well in Word, I thought I’d do it in a quickly opened, used, and then closed, instance of Word.

(You could modify this to open another Excel instance and use that, since you won’t lose the Undo stack across instances. You could also check if Word is already open and use that instance. I used Word because, since I don’t have addins in it, I think it opens faster. I don’t bother checking for an open instance because the whole thing only takes a couple of seconds as is.)

The code is pretty straightforward. It uses late binding for the Word objects, so that it doesn’t care what version of Word you’re using (and so that JP won’t give me grief if he’s still reading this blog). It also uses a Windows DataObject to hold the filenames and copy them to the clipboard.

Sub Browse_To_File_and_Copy_UNC_Names_to_Clipboard()
Dim fdFileDialog As FileDialog
Dim FileName As String
Dim FileNames As String
Dim doFileNames As DataObject
Dim SelectedItemsCount As Long
Dim wrdApp As Object
Dim wrdDoc As Object
Dim TempLink As Object
Dim i As Long

Set fdFileDialog = Application.FileDialog(msoFileDialogOpen)
With fdFileDialog
    .ButtonName = "Select"
    .FilterIndex = 1
    .InitialView = msoFileDialogViewDetails
    .Title = "Select Files"
    .ButtonName = "Select"
    .AllowMultiSelect = True
    .Show
    If .SelectedItems.Count = 0 Then
        GoTo Exit_Point
    End If
    Set doFileNames = New DataObject
    SelectedItemsCount = .SelectedItems.Count
    Set wrdApp = CreateObject("Word.Application")
    Set wrdDoc = wrdApp.Documents.Add
    For i = 1 To SelectedItemsCount
        Set TempLink = wrdDoc.Hyperlinks.Add(Anchor:=wrdApp.Selection.Range, Address:=.SelectedItems(i))
        FileName = TempLink.Address
        FileNames = FileNames & FileName & vbCrLf
    Next i
    FileNames = Left(FileNames, Len(FileNames) - 1)
    doFileNames.SetText FileNames
    doFileNames.PutInClipboard
End With

Exit_Point:
On Error Resume Next
wrdDoc.Close False
wrdApp.Quit
End Sub

When you run the code this dialog pops up. You can select one or more files:

File browser

After you’ve selected them, they’ll be in the Windows clipboard and you can paste them wherever you want, like into an email.

Pasted UNCs

Speak Your Mind

Your email address will not be published. Required fields are marked *

To post code, do this: <code> your vba here </code>