We use a standard corporate Excel 2003 install. I just finished a project
and wanted to make sure users were accessing the file from our network (not
a local copy) and added the code below. Most users haven't had any problems,
but on roll-out (after the pilot went fine) we are getting reports of a
compile error (can't find project or library). Looking at the following
code, the only think I can think of is that maybe not all users have the
mpr.dll? What is the appropriate way to determine if it exists, and if it
does, make sure it is added?

Also, the stuff in the code module below was from a web page without any
indication of the original author (and therefore, no copyright). I like to
include references even in internal code wherever possible...I don't sell
stuff for profit, but it is still just considerate to do- so what is the
appropriate protocol when you don't know who's code snippet it is?

Thanks!!
Keith

[The particular line of code where it stops is in the workbook_open;]

GetUNCPath
If UCase(zNetPath) < UCase("\\mynetwork\myfolder") Then 'this was the
highlighted line in my customer's screenshot
MsgBox "Can't find file ", , "File location error"
End If

and GetUNC is:

[and in my code module:]

Public zNetPath

Declare Function WNetGetConnectionA Lib "mpr.dll" _
(ByVal lpszLocalName As String, _
ByVal lpszRemoteName As String, _
cbRemoteName As Long) As Long


Public Sub GetUNCPath()

Dim lReturn As Long
Dim szBuffer As String

szBuffer = String$(256, vbNullChar)
lReturn = WNetGetConnectionA(ActiveWorkbook.Path, szBuffer, 256)

If lReturn = 0 Then
zNetPath = Left$(szBuffer, InStr(szBuffer, vbNullChar))
zNetPath = Left(zNetPath, Len(zNetPath) - 1)
Else
If UCase(ActiveWorkbook.Path) = UCase("\\mynetwork\myfolder") Then
zNetPath = ("\\mynetwork\myfolder")
Else
zNetPath = "not found"
End If
End If

End Sub


--
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.

My guess is that it is having problems with Ucase. Actually, you have a
reference in tools=References in the VBE that will be shown as MISSING on
this users computer. If so, that reference needs to be fixed. This type of
reference issue most often manifest itself on a line of code that calls a
perfectly correct VBA function.

a bandaid fix might be

If UCase(zNetPath) < VBA.UCase("\\mynetwork\myfolder") Then

but you really need to run the code. Get the error. go to the VBE, make
sure your workbook that contains the code is selected in the project explorer
(thus it is the active project in the VBE), then do Tools=References and you
should see one or more references marked as MISSING. This/these need to be
fixed by removing them (best done at your end and sending out a new workbook)
or the reference found by browsing for it in the References dialog.

--
Regards,
Tom Ogilvy


"KR" wrote:

We use a standard corporate Excel 2003 install. I just finished a project
and wanted to make sure users were accessing the file from our network (not
a local copy) and added the code below. Most users haven't had any problems,
but on roll-out (after the pilot went fine) we are getting reports of a
compile error (can't find project or library). Looking at the following
code, the only think I can think of is that maybe not all users have the
mpr.dll? What is the appropriate way to determine if it exists, and if it
does, make sure it is added?

Also, the stuff in the code module below was from a web page without any
indication of the original author (and therefore, no copyright). I like to
include references even in internal code wherever possible...I don't sell
stuff for profit, but it is still just considerate to do- so what is the
appropriate protocol when you don't know who's code snippet it is?

Thanks!!
Keith

[The particular line of code where it stops is in the workbook_open;]

GetUNCPath
If UCase(zNetPath) < UCase("\\mynetwork\myfolder") Then 'this was the
highlighted line in my customer's screenshot
MsgBox "Can't find file ", , "File location error"
End If

and GetUNC is:

[and in my code module:]

Public zNetPath

Declare Function WNetGetConnectionA Lib "mpr.dll" _
(ByVal lpszLocalName As String, _
ByVal lpszRemoteName As String, _
cbRemoteName As Long) As Long


Public Sub GetUNCPath()

Dim lReturn As Long
Dim szBuffer As String

szBuffer = String$(256, vbNullChar)
lReturn = WNetGetConnectionA(ActiveWorkbook.Path, szBuffer, 256)

If lReturn = 0 Then
zNetPath = Left$(szBuffer, InStr(szBuffer, vbNullChar))
zNetPath = Left(zNetPath, Len(zNetPath) - 1)
Else
If UCase(ActiveWorkbook.Path) = UCase("\\mynetwork\myfolder") Then
zNetPath = ("\\mynetwork\myfolder")
Else
zNetPath = "not found"
End If
End If

End Sub


--
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.

KR,

Open your's excel (assuming code is working fine at your machine).
Press Alt + F11 on your keyboard. Go Tools -- References. Check what
is selected than select same options to those machines where code is
not working. It will work after selecting same options.

Regards
Sam




KR Wrote:
We use a standard corporate Excel 2003 install. I just finished a
project
and wanted to make sure users were accessing the file from our network
(not
a local copy) and added the code below. Most users haven't had any
problems,
but on roll-out (after the pilot went fine) we are getting reports of
a
compile error (can't find project or library). Looking at the
following
code, the only think I can think of is that maybe not all users have
the
mpr.dll? What is the appropriate way to determine if it exists, and if
it
does, make sure it is added?

Also, the stuff in the code module below was from a web page without
any
indication of the original author (and therefore, no copyright). I like
to
include references even in internal code wherever possible...I don't
sell
stuff for profit, but it is still just considerate to do- so what is
the
appropriate protocol when you don't know who's code snippet it is?

Thanks!!
Keith

[The particular line of code where it stops is in the workbook_open;]

GetUNCPath
If UCase(zNetPath) < UCase("\\mynetwork\myfolder") Then 'this was the
highlighted line in my customer's screenshot
MsgBox "Can't find file ", , "File location error"
End If

and GetUNC is:

[and in my code module:]

Public zNetPath

Declare Function WNetGetConnectionA Lib "mpr.dll" _
(ByVal lpszLocalName As String, _
ByVal lpszRemoteName As String, _
cbRemoteName As Long) As Long


Public Sub GetUNCPath()

Dim lReturn As Long
Dim szBuffer As String

szBuffer = String$(256, vbNullChar)
lReturn = WNetGetConnectionA(ActiveWorkbook.Path, szBuffer, 256)

If lReturn = 0 Then
zNetPath = Left$(szBuffer, InStr(szBuffer, vbNullChar))
zNetPath = Left(zNetPath, Len(zNetPath) - 1)
Else
If UCase(ActiveWorkbook.Path) = UCase("\\mynetwork\myfolder") Then
zNetPath = ("\\mynetwork\myfolder")
Else
zNetPath = "not found"
End If
End If

End Sub


--
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.


--
sam76210
------------------------------------------------------------------------
sam76210's Profile: http://www.excelforum.com/member.php...o&userid=20885
View this thread: http://www.excelforum.com/showthread...hreadid=526175