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
or the mapped drive method
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: