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:
After closing the dialog and then opening the Edit Hyperlink dialog the Address box shows the UNC filename:
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.
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:
After you’ve selected them, they’ll be in the Windows clipboard and you can paste them wherever you want, like into an email.
