I have a workbook on a network directory. I also happen to have the network
directory mapped on my machine, but various users may have it mapped
differently, or not at all.

I need to verify (on open) that the workbook is still located in the network
directory, and that someone hasn't moved it or saved a copy elsewhere,
because the data they enter needs to be available to all users.

I tried activeworkbook.path, fullname, and curdir, but all of those just
gave me my mapped drive letter (w:)

I did a google search and it looks like WNetGetUniversalName translates the
local path to the full network path, but for the life of me I can't get it
to work in VBA. Does anyone have a code snippet that implements this that
they would be willing to share?

Thanks,
Keith

--
The enclosed questions or comments are entirely mine and don't represent the
thoughts, views, or policy of my employer. Any errors or omissions are my
own.

this is for VB, but should be easily adapted to Excel VBA:

http://support.microsoft.com/kb/192689/en-us
How To Get UNC Path From a Mapped Network Share's Drive Letter


easier might be to use the scripting runtime:

Sub abc()
Dim fs As Object
Set fs = CreateObject("Scripting.fileSystemObject")
For Each dr In fs.Drives
msgbox dr.DriveLetter & " - " & dr.ShareName
Next
End Sub

the above is just an example to demonstrate the objects and two of the Drive
object's properties.

--
Regards,
Tom Ogilvy






"KR" wrote:

I have a workbook on a network directory. I also happen to have the network
directory mapped on my machine, but various users may have it mapped
differently, or not at all.

I need to verify (on open) that the workbook is still located in the network
directory, and that someone hasn't moved it or saved a copy elsewhere,
because the data they enter needs to be available to all users.

I tried activeworkbook.path, fullname, and curdir, but all of those just
gave me my mapped drive letter (w:)

I did a google search and it looks like WNetGetUniversalName translates the
local path to the full network path, but for the life of me I can't get it
to work in VBA. Does anyone have a code snippet that implements this that
they would be willing to share?

Thanks,
Keith

--
The enclosed questions or comments are entirely mine and don't represent the
thoughts, views, or policy of my employer. Any errors or omissions are my
own.