VBA Code to Obtain the file path in UNC format

[AndrewEng]

Hi,

I've read these forums a lot but this is my first time to register and post on here.

I am trying to put together some VBA code for Excel to obtain UNC file paths for files on a network.

I can get the filepath with a drive letter OK (e.g. P:\projects\drawings\plan01.dwg)
I am having trouble converting this to a UNC type path with server name (e.g. \\servername\shareddrive\projects\drawings\plan01.dwg)

I am passing the filepath (with drive letter) to the function 'GetUNC' below using a variable called strMappedDrive. There seems to be a problem with the function. The debugger stops at the Sharename line (starting strShare...) which is supposed to get the UNC.

I originally found the code below on another thread and it worked OK. However I have changed it from "early binding" to "late binding" and it is not working.

I need late binding so that different users can use the code without having to reference libraries. If I used early binding then I find the code only works if you enable Microsoft Scripting. I am using Visual Basic for Applications 6 and MS Excel 2013. I am also a beginner at VBA so go easy on me please!

Please Login or Register  to view this content.

[LJMetzger]

Hi AndrewEng and welcome to ExcelForum,

Thank you for using code tags. The following code courtesy of Microsoft may help you solve your problem. The code also uses 'Late Binding'.

Please Login or Register  to view this content.

I hope this helps.

Lewis

[AndrewEng]

Lewis,
Thanks for your reply. The code works thank you.
Andrew