Click here to Skip to main content
15,906,628 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have written a macro in one spreadsheet that opens a second spreadsheet to extract data and processes, filters and displays the required information.
It works perfectly well.
Both spreadsheets are in the same folder on a shared drive, and although everyone with access to the shared drive can open the first spreadsheet, when they attempt to run the macro it fails as their machine cannot find the second spreadsheet.
I was puzzled for a while... then I realised that when I wrote it I pointed to the shared file that was mapped to F drive on my machine.
Others have it mapped to Z, or Q etc...

Is there a way of pointing to this file without going around to all 40 pcs in 4 different parts of the country and remapping all the drives?


What I have tried:

Nothing as yet beyond asking collegues and posting here
Spreadsheets are my strong point not network mapping!
Updated 20-Nov-22 10:48am

Unfortunately, there is not "find it on something, somewhere" that works with all mapped drives.
You could look in the registry - HKEY_CURRENT_USER\Network\ followed by each drive in turn - the UNC is under the RemotePath key.
But ... if the shared drive moves to a different computer during an upgrade you are back where you started. I'm also not sure if that part of the registry requires elevation to access

The way I'd do it is to store the paths in a config file (JSON / XML probably) and load the path info from there before appending the file name(s).
Share this answer
Thanks I will try the UNC solution tomorrow when I hit the office.
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900