Need to bypass the drive letter (i.e C Drive) and use the drive name

Good afternoon.

I am having real difficulty in changing my Macro to bypass the drive letter and use the drive name.

Basically, their are 20 people in my team which will require access to the file I have built. The macro in the file currently uses Z:\ as this is what my PC is set to. Others on the same drive (letter) the file works fine, however other are set to X:\ or Z:\ and the macro errors.

I have found the server name which is \\gbrcrotpc0c1002\Life_Ent by going into Programs/Accessories/Comand Prompt

I have pasted two examples below as I am by far experienced in using Macros (my first attempt to be honest).

The first one works with others on the Y:\ as mentioned above, the second is me trying to bypass the letter so regardless of which letter they have used, it can still access the file.

Please tell me there is a way of doing this, as I wont be able to get everyone to change their "letter".

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(Because this is your first posting, I have added the code tags. Please read our rules and adhere to them in the future.)

If using the UNC path, as you are in the 2nd example, works then use it.

PS Can you add code tags?

It looks like the second one should work. Have you tested on machines that have different Drive letters?

Where is the workbook containing the code located in relation to the files you are trying to reference in the code?