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

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?

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

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.

DataBoy wrote:

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?

--

Dave Peterson

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

On Sep 24, 4:35*pm, Dave Peterson wrote:
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.

DataBoy wrote:

Not sure if I am using the correct keywords/tricky phrases here but...
I have a workbook placed on ournetworkthat 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 thenetworkfile (most current) and not a copy they
placed on their CPU. I have tried the wholepath, GetDrive, etc. methods but
it only gives me the "mapped name" (ex1: "T:\") and not the actualnetwork
pathinformation (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?

--

Dave Peterson

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!