Greetings! This is my first post on the PODA blog, I hope you enjoy it.
If you are saving files to networked drives, or reading data from files on networked drives, or doing *anything* with networked drives, you should be using UNC filepaths. This is necessary because you (or your network admin) can map a drive to any letter. If your application is being used by more than one person, each computer might have the same drive mapped to different letters. Your code, if hardcoded to a specific drive letter, will then fail (insert complicated workaround here). Or, your network admin might decide to move or reassign drive letters. Existing code would then break.
But fortunately, constructing UNC paths is easy. You don't even need to use VBA to do it (although I will demonstrate one method for doing so via VBA).
1: Windows Explorer Method
Windows Explorer lists network drives, giving you clues to the UNC path. Here's a sample listing:
Let's say you have an Excel workbook located at the following path:
G:\Apps\Files\Department\DataWkbk2009-12.xls
In this example, G:\ is the mapped drive on your computer.
When you open Windows Explorer, the G drive is listed as "Data on 'CompanyDrive' (G:)". The UNC path, therefore, is
\\CompanyDrive\data\Apps\Files\Department\DataWkbk2009-12.xls
Basically, it's \\Server\Volume\Original Filepath. This path can be used in your VBA project without worrying that the drive letter might change or be different from computer to computer. We can even use constants to designate each network drive's root folder, changing the path at runtime, i.e.
Const DATA_FILES_PATH As String = "\\CompanyDrive\data\"
2: Windows Script Host Object Model Method
The Windows Script Host Object Model (WSHOM) can reveal the UNC paths for your mapped drives. Here are a few sample procedures you can use to
- list all the mapped network drives on your computer (UNC path only) and
- return an array of all the mapped network drives on your computer (UNC path only).
List mapped network drives
Using the WshNetwork Object, we can iterate through the EnumNetworkDrives Collection and print the UNC path of each drive. Simply call the below function from any VBA-enabled program (Word, Excel, Outlook, etc).
Sub ListNetworkDrives()
Dim WshNetwork As Object
Dim drivesList As Object
Dim i As Long
Set WshNetwork = CreateObject("WScript.Network")
Set drivesList = WshNetwork.EnumNetworkDrives
For i = 0 To drivesList.Count - 1 Step 2
Debug.Print drivesList.Item(i + 1)
Next
End Sub
Return an array of mapped network drives
If instead we wanted to return an array of UNC paths, we would use the following procedure. It loops through the EnumNetworkDrives Collection and assigns each UNC path to a slot in the array.
Function GetNetworkDrives() As Variant
Dim WshNetwork As Object
Dim drivesList As Object
Dim i As Long
Dim netDrives() As String
Set WshNetwork = CreateObject("WScript.Network")
Set drivesList = WshNetwork.EnumNetworkDrives
ReDim netDrives(0 To (drivesList.Count / 2) - 1)
For i = 0 To UBound(netDrives)
netDrives(i) = drivesList.Item((i * 2) + 1)
Next
GetNetworkDrives = netDrives
End Function
A sample procedure for using the GetNetworkDrives function is as follows:
Sub TestNetworkDrives()
Dim i As Long
Dim strdrives() As String
' return list
strdrives = GetNetworkDrives
For i = 0 To UBound(strdrives)
Debug.Print strdrives(i)
Next i
End Sub
These procedures could be part of a larger framework of verifying that a mapped drive exists, and map it (using the WshNetwork.MapNetworkDrive Method) if necessary.
3: The quick-and-dirty method
If you open Windows Explorer and go to Tools » Disconnect Network Drives, you'll actually be presented with a list of your mapped drives along with the drive letter and the UNC path!
If you don't want to try methods 1 or 2, this is the simplest way to get the UNC path for a particular drive letter.
Bonus: Emailing links to networked files
You may want to email links to networked files, instead of attaching them to an email.
This is ideal when you want to limit network bandwidth, control file access, and generally make sure different versions of your applications aren't floating around your office. In fact, I recommend it as a best practice, if you are using common network shares in an office environment.
But there seems to be some confusion about exactly how to format those links. And you'll create broken links if you hardcode the drive letter into the filepath, and your recipient has that drive mapped to a different letter. So you need to use UNC paths in your emails.
Using the example above, I want to email a link to the following file to a co-worker:
G:\Apps\Files\Department\DataWkbk2009-12.xls
We've already determined that, based on the drive information ("Data on 'CompanyDrive' (G:)") that the UNC path for this file is
\\CompanyDrive\data\Apps\Files\Department\DataWkbk2009-12.xls
To format this for email, prepend
I construct the links in Notepad and paste them into Outlook, because if you try and type the whole thing directly in Outlook, it can sometimes break the link unintentionally, even if the path is correct (especially if there are spaces). Once you complete the link and press Enter, it turns into a hyperlink. Send a test email to yourself to see how it works. You'll be a hero and everyone will be asking how you did it.
Follow Us