urgent plz; project/library compile error

[KR]

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]

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

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.

[Tom Ogilvy]

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