One challenge that Excel analysts face in the Business world is the mapping of network drives to different drive letters, learn how to overcome the issue of writing VBA code specific to one drive letter by using universal naming conventions…
The Universal Network Drive Problem
In the business world an Excel analyst often find themselves writing VBA scripts and creating Excel files that need to be shared around the company. A common problem can occur when sharing Excel files around a business and that is not all users will have their network drives mapped to the same letter as you the creator.
For example you create an Excel Dashboard that will be used around the company by lots of other staff members. In that Excel Dashboard you have created a button that links to a Reference file which has been saved on the shared network so everyone can access it. When you create the link in VBA the shared network drive could be mapped to your Z drive but for other users some may have it mapped to their U drive or X drive therefore the link will not work for them, Excel will look in the wrong place.
Your VBA to open the reference file might look something like below, with Z drive specified as the location:
1 2 3 4 5
Sub OpnRef() Application.Workbooks.Open ("Z:\Shared Report Area\Reference Docs \Reference1File.xlsx") End Sub
The code will test fine on your machine however if a different end-user has mapped that shared location to their U drive Excel will not be able to find the file as it will look for the folder in that users Z drive which may not even exist.
The solution is to change the way you specify the location of the file using VBA and for this you utilise the UNC, Universal Naming Convention. More detail can be found on the UNC here but the basic premise is that you are changing the location of the file to use the full server path location, the format it takes is:
We can use this way of locating a file/folder in our VBA scripts for Excel. So the previous VBA script that specified the file was on the Z drive would now look like this:
1 2 3 4 5
Sub OpnRef() Application.Workbooks.Open ("\\Server\Share\Shared Report Area\Reference Docs \Reference1File.xlsx") End Sub
The section for \\Server\Share replaces the drive letter and after that you specify the file path as per normal, remembering to still point to the specific file to open.
\\Server\Share is the uniform way of describing the location, you will need to change this part to your exact server name and shared area. If you are struggling to determine this you can usually get assistance from an the in-house IT team.
To do it yourself the quickest way is to open Windows Explorer and locate the path that points to shared area in question, for example if you are mapped to the Z drive it will say something along the lines of:
Shared on ‘xxxx\xxxx\xxxx’ (Z:)
Your \\Server\Share section is everything in-between the apostrophes, so that’s the bit reading xxxx\xxxx\xxxx in the above example.
Using the UNC location rather than being specific to the drive letter is a simple habit to get into and will ensure that all VBA scripts your write can be transferred around the business without additional support or future changes.
You can find a UNC location through windows explorer and use that to replace the drive letter in your VBA scripts.
Using the UNC will save you time in the future so get into the habit of using this neat process today!