Identify the network path a file was opened from (Excel2007)

  • Thread starter Thread starter DataBoy
  • Start date Start date
D

DataBoy

Not sure if I am using the correct keywords/tricky phrases here but...
I have a workbook placed on our network that contains a macro other can use
create a standard report. I want to write into the code, a way to confirm
that they are using the network file (most current) and not a copy they
placed on their CPU. I have tried the whole path, GetDrive, etc. methods but
it only gives me the "mapped name" (ex1: "T:\") and not the actual network
path information (ex2: "\\usatx\stdrpts\E&Ocalc\"). Hope this makes sense,
any ideas how to get to this level of information in example 2 or other
method to confirm they are using the correct file?
 
I googled for some help and found this:
http://www.xtremevbtalk.com/showthread.php?t=146146

I tried this, but I can't test it because I'm not on a network.

Option Explicit
Private Const UNIVERSAL_NAME_INFO_LEVEL As Long = &H1

Private Type UNIVERSAL_NAME_INFO
lpUniversalName As String * 256
End Type

Private Declare Function WNetGetUniversalName Lib "mpr" _
Alias "WNetGetUniversalNameA" _
(ByVal lpLocalPath As String, _
ByVal dwInfoLevel As Long, _
lpBuffer As Any, _
lpBufferSize As Long) As Long

Sub Auto_Open()

Dim lngResults As Long
Dim udtUNCPath As UNIVERSAL_NAME_INFO
Dim myStr As String

myStr = ThisWorkbook.Path

If Mid(myStr, 2, 2) = ":\" Then
'looks like a mapped drive x:\xxxx\xxxx
lngResults = WNetGetUniversalName(Left(myStr, 3), _
UNIVERSAL_NAME_INFO_LEVEL, udtUNCPath, Len(udtUNCPath))
MsgBox Replace(udtUNCPath.lpUniversalName, vbNullChar, "")
Else
MsgBox myStr
End If

End Sub

If it doesn't work, you can try googling for "UNC Mapped VB API" and get lots of
hits.
 
I googled for some help and found this:http://www.xtremevbtalk.com/showthread.php?t=146146

I tried this, but I can't test it because I'm not on anetwork.

Option Explicit
Private Const UNIVERSAL_NAME_INFO_LEVEL As Long = &H1

Private Type UNIVERSAL_NAME_INFO
    lpUniversalName As String * 256
End Type

Private Declare Function WNetGetUniversalName Lib "mpr" _
   Alias "WNetGetUniversalNameA" _
    (ByVal lpLocalPath As String, _
     ByVal dwInfoLevel As Long, _
     lpBuffer As Any, _
     lpBufferSize As Long) As Long

Sub Auto_Open()

    Dim lngResults As Long
    Dim udtUNCPath As UNIVERSAL_NAME_INFO
    Dim myStr As String

    myStr = ThisWorkbook.Path

    If Mid(myStr, 2, 2) = ":\" Then
        'looks like a mapped drive x:\xxxx\xxxx
        lngResults = WNetGetUniversalName(Left(myStr, 3), _
                      UNIVERSAL_NAME_INFO_LEVEL, udtUNCPath, Len(udtUNCPath))
        MsgBox Replace(udtUNCPath.lpUniversalName, vbNullChar, "")
    Else
        MsgBox myStr
    End If

End Sub

If it doesn't work, you can try googling for "UNC Mapped VB API" and get lots of
hits.
Click to expand...

Excellent... it works great... and the additional search criteria
information was very helpful; it always about using the correct works
in the search to get to the right information (I would rate this a 5
out of 5 stars if the link the system provided in the reply email
worked), Thank you!
 
Back
Top