Excel References

[Guest]

Does anyone know how Excel loads the References dialog in the VBE

I have registered a DLL and XL still claims that it can't find it. Is there a registry setting that I have to set for XL to find a DLL

Any help would be greatly appreciated

Thanks

Joe

 

[Rob Bovey]

Hi Joe,

The references list is just a list of COM components from the registry.
A couple ideas: Are you sure that your DLL is a valid ActiveX DLL? What
happens if you use the Browse button to point Excel at your DLL?

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

Does anyone know how Excel loads the References dialog in the VBE?

I have registered a DLL and XL still claims that it can't find it. Is

there a registry setting that I have to set for XL to find a DLL?

 

[Guest]

Rob

If the DLL is listed in the References list and doesn't include the word MISSING in the name, the Browser lists it in the dropdown of available libraries. If MISSING is included, then the browser doesn't include it in the list of available libraries

If I have the References dialog open, I can't get to the Browser (as indicated in Help); I tried that and it didn't work. I had to close the References dialog

I have also noticed that when the XL workbook is opened on other machines, an error appears indicating that it can't find the referenced library (MISSING is present) and the path to the DLL is given as the path that it would have on my machine. For example, I would see D:\WINNT\System32\CTXS.dll listed as the path to the DLL. This is fine for my machine, but for others who local drive is C:, this raises the error that Excel can't find the library

How do I know if the DLL is a valid ActiveX DLL? BTW, I'm using Inno Setup 4 and ISTOOL for the deployment of the XL workbook and the DLL

THanks

Jo



----- Rob Bovey wrote: ----

Hi Joe

The references list is just a list of COM components from the registry
A couple ideas: Are you sure that your DLL is a valid ActiveX DLL? Wha
happens if you use the Browse button to point Excel at your DLL

--
Rob Bovey, MCSE, MCSD, Excel MV
Application Professional
http://www.appspro.com

* Please post all replies to this newsgroup
* I delete all unsolicited e-mail responses

 

[Rob Bovey]

Hi Joe,

<<If I have the References dialog open, I can't get to the Browser (as
indicated in Help); I tried that and it didn't work. I had to close the
References dialog.>>

Let's make sure we're talking about the same thing. In the Excel Visual
Basic Editor (VBE) you choose the Tools/References menu to open the
References dialog. On the References dialog there is a Browse button located
directly below the Cancel button. If you click this button you will get a
File/Open-style Add References dialog. Use this dialog to navigate to and
select your DLL. Click OK and the VBE will register the DLL and add it to
the Available References listbox.

<<I have also noticed that when the XL workbook is opened on other machines,
an error appears indicating that it can't find the referenced library
(MISSING is present) and the path to the DLL is given as the path that it
would have on my machine>>

The MISSING flag in the References dialog means that the flagged
component has not been properly registered on that machine.

<<How do I know if the DLL is a valid ActiveX DLL?>>

The easiest way is to attempt to register the DLL with the Windows
command-line registration tool Regsvr32.exe. Open a command prompt and enter
the following on the command line:

C:\>Regsvr32 C:\YourPath\YourDLL.DLL

where the actual path and file name identify the DLL you are trying to
register. If the DLL is a valid ActiveX DLL you will get a success message
and the DLL will be registered on the machine (registering multiple times
won't hurt anything). If the DLL is not an ActiveX DLL you will get a
failure message indicating that the DLL could not be registered, in which
case it is not the type of DLL that can be referenced through the
Tools/References dialog.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

Rob,

If the DLL is listed in the References list and doesn't include the word

MISSING in the name, the Browser lists it in the dropdown of available
libraries. If MISSING is included, then the browser doesn't include it in
the list of available libraries.

If I have the References dialog open, I can't get to the Browser (as

indicated in Help); I tried that and it didn't work. I had to close the
References dialog.

I have also noticed that when the XL workbook is opened on other machines,

an error appears indicating that it can't find the referenced library
(MISSING is present) and the path to the DLL is given as the path that it
would have on my machine. For example, I would see
D:\WINNT\System32\CTXS.dll listed as the path to the DLL. This is fine for
my machine, but for others who local drive is C:, this raises the error that
Excel can't find the library.

How do I know if the DLL is a valid ActiveX DLL? BTW, I'm using Inno

Setup 4 and ISTOOL for the deployment of the XL workbook and the DLL.

 

[Guest]

Please see below

----- Rob Bovey wrote: ----

Hi Joe

<<If I have the References dialog open, I can't get to the Browser (a
indicated in Help); I tried that and it didn't work. I had to close th
References dialog.>

**Let's make sure we're talking about the same thing. In the Excel Visua
Basic Editor (VBE) you choose the Tools/References menu to open th
References dialog. On the References dialog there is a Browse button locate
directly below the Cancel button. If you click this button you will get
File/Open-style Add References dialog. Use this dialog to navigate to an
select your DLL. Click OK and the VBE will register the DLL and add it t
the Available References listbox.*

This helps but still does not solve the problem. I do appreciate your explanation,
though, because I couldn't understand what MS was talking about when they referre
to the Browser

<<I have also noticed that when the XL workbook is opened on other machines
an error appears indicating that it can't find the referenced librar
(MISSING is present) and the path to the DLL is given as the path that i
would have on my machine>

**The MISSING flag in the References dialog means that the flagge
component has not been properly registered on that machine.*

If I see a component listed in the Registry giving its patha dn
threading model, can I assume that it has been registered
This is what I have seen when the MISSING flag is displayed
in the References dialog

<<How do I know if the DLL is a valid ActiveX DLL?>

**The easiest way is to attempt to register the DLL with the Window
command-line registration tool Regsvr32.exe. Open a command prompt and ente
the following on the command line

C:\>Regsvr32 C:\YourPath\YourDLL.DL

where the actual path and file name identify the DLL you are trying t
register. If the DLL is a valid ActiveX DLL you will get a success messag
and the DLL will be registered on the machine (registering multiple time
won't hurt anything). If the DLL is not an ActiveX DLL you will get
failure message indicating that the DLL could not be registered, in whic
case it is not the type of DLL that can be referenced through th
Tools/References dialog.*

I have done this and have still seen the MISSING flag. Thi
is what has been so frustrating

--
Rob Bovey, MCSE, MCSD, Excel MV
Application Professional
http://www.appspro.com

* Please post all replies to this newsgroup
* I delete all unsolicited e-mail responses

Rob

MISSING in the name, the Browser lists it in the dropdown of availabl
libraries. If MISSING is included, then the browser doesn't include it i
the list of available librariesindicated in Help); I tried that and it didn't work. I had to close th
References dialogan error appears indicating that it can't find the referenced librar
(MISSING is present) and the path to the DLL is given as the path that i
would have on my machine. For example, I would se
D:\WINNT\System32\CTXS.dll listed as the path to the DLL. This is fine fo
my machine, but for others who local drive is C:, this raises the error tha
Excel can't find the library

 

[Rob Bovey]

Hi Joe,

If you have a MISSING reference on a machine where you're sure the
component is properly registered, try unchecking the missing reference and
closing the References dialog, then reopen the References dialog and recheck
the reference.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

 

[Guest]

Rob

Thanks for your help. I'm developing this for end users who will not be able to recheck the component in the dialog.

Is it possible to do this within VBA? Would you use the References.AddFromFile or the References.AddFromGUID? How do you determine the DLL's GUID

Do you know of any routines that could check to see if the DLL is referenced and, if it is not, set a reference to it

Thanks again

Jo
----- Rob Bovey wrote: ----

Hi Joe

If you have a MISSING reference on a machine where you're sure th
component is properly registered, try unchecking the missing reference an
closing the References dialog, then reopen the References dialog and rechec
the reference

--
Rob Bovey, MCSE, MCSD, Excel MV
Application Professional
http://www.appspro.com

* Please post all replies to this newsgroup
* I delete all unsolicited e-mail responses

Please see below

----- Rob Bovey wrote: ----
Hi Joe
<<If I have the References dialog open, I can't get to the Browse

(a
indicated in Help); I tried that and it didn't work. I had to clos th
References dialog.>>>> **Let's make sure we're talking about the same thing. In the Exce Visua
Basic Editor (VBE) you choose the Tools/References menu to open th
References dialog. On the References dialog there is a Browse butto locate
directly below the Cancel button. If you click this button you wil get
File/Open-style Add References dialog. Use this dialog to navigate t an
select your DLL. Click OK and the VBE will register the DLL and ad it t
the Available References listbox.*

This helps but still does not solve the problem. I d

appreciate your explanation
though, because I couldn't understand what MS was talkin about when they referre
to the Browser

<<I have also noticed that when the XL workbook is opened on othe

machines
an error appears indicating that it can't find the referenced librar
(MISSING is present) and the path to the DLL is given as the pat that i
would have on my machine>>>> **The MISSING flag in the References dialog means that the flagge
component has not been properly registered on that machine.*

If I see a component listed in the Registry giving its path

d
threading model, can I assume that it has been registered
This is what I have seen when the MISSING flag is displaye
in the References dialog

<<How do I know if the DLL is a valid ActiveX DLL?>>>> **The easiest way is to attempt to register the DLL with th

Window
command-line registration tool Regsvr32.exe. Open a command promp and ente
the following on the command line

C:\>Regsvr32 C:\YourPath\YourDLL.DL
where the actual path and file name identify the DLL you are tryin

t
register. If the DLL is a valid ActiveX DLL you will get a succes messag
and the DLL will be registered on the machine (registering multipl time
won't hurt anything). If the DLL is not an ActiveX DLL you will get
failure message indicating that the DLL could not be registered, i whic
case it is not the type of DLL that can be referenced through th
Tools/References dialog.*

I have done this and have still seen the MISSING flag. Thi

is what has been so frustrating

--

Rob Bovey, MCSE, MCSD, Excel MV
Application Professional
http://www.appspro.com

* Please post all replies to this newsgroup *

* I delete all unsolicited e-mail responses *

 

[Guest]

Rob

I have another question, if you would be so kind.

When I run the application of my machine (XL 2002 and Windows 2000), after I re-establish the References, XL runs fine. If I run it on a Windows XP machine (w/ XL 2002), and the references are retained by Excel, it gives a Error 71, Disk not ready error. And if I run it on a Windows Server 2000 machine with XL 2002, it gives a Error 52, Bad file name or number error

What is strange is that I am not accessing a disk or providing a file name from within the DLLs, so I'm not sure what to make of this

Do you have any idea what this could be

I have included the workbook and DLL code below

Thanks

Jo

XL workbook Code

Option Explici

Private WithEvents cXL As CTXS.cXLLin

Private Sub cXL_userVerified(pfUserVerified As Boolean
If pfUserVerified The
cXL.Unprotec
Els
MsgBox "The VERICIS® configuration worksheet has not been registered to run on this workstation.", vbCritical, "Error!
MsgBox "Please call Camtronics at 1.800.634.5151 for assistance.", , vbNullStrin
Call ExitAp
End I
End Su

Private Sub Workbook_BeforeClose(Cancel As Boolean
cXL.Protec
Set cXL = Nothin
End Su

Private Sub Workbook_Open(
Set cXL = New CTXS.cXLLin
If Not Err The
cXL.Workbook = ThisWorkbook <-- The code bombs on this line
Els
Call ExitAp
End I
End Su

Private Sub ExitApp(
application.Qui
End Su

This is the code from the cXLLink class in the DLL

Option Explici

Private m_XLWbk As Excel.Workboo
Private f As frmSecurit
Private fUserVerified As Boolea

Public Event userVerified(pfUserVerified As Boolean

Public Property Let Workbook(ByVal wbk As Excel.Workbook
Set m_XLWbk = wb
Call logMsg("Set wbk."
With m_XLWb
.Worksheets(WKS_CTX).Visible = Tru
.Worksheets(WKS_ENABLE_MACROS).Visible = xlVeryHidde
End Wit
Call logMsg("CTX activated."
Call Sho
If Not f.fIsAdmin Then Call InformX
End Propert

Private Sub Class_Initialize(
Set f = New frmSecurit
Call populateScrollAreaCollectio
End Su

Private Sub Class_Terminate(
Unload
Set colScrollArea = Nothin
Set f = Nothin
End Su

Private Sub InformXL(
RaiseEvent userVerified(f.fIsValidUser
End Su

Private Sub Show(
f.Show vbModa
If f.fIsAdmin Then Call ShowWorksheets(pfIsAdmin:=True
End Su

Public Sub Protect(
Call HideWorksheet
End Su

Public Sub Unprotect(
Call ShowWorksheet
End Su

Private Sub HideWorksheets(
Dim wks As Excel.Workshee
For Each wks In m_XLWbk.Worksheet
If wks.Name = WKS_ENABLE_MACROS The
With wk
.Visible = xlSheetVisibl
.Activat
End Wit
Els
If f.fIsAdmin The
Select Case wks.Nam
Case WKS_ECH
wks.Columns(HIDE_COLUMNS_ECHO).EntireColumn.Hidden = Tru
Case WKS_CAT
wks.Columns(HIDE_COLUMNS_CATH).EntireColumn.Hidden = Tru
Case WKS_NUCLEA
wks.Columns(HIDE_COLUMNS_NUCLEAR).EntireColumn.Hidden = Tru
End Selec
End I
wks.Visible = xlVeryHidde
End I
Next wk
m_XLWbk.Sav
End Su

Private Sub ShowWorksheets(Optional pfIsAdmin As Boolean = False
Dim wks As Excel.Workshee
Dim rng As Excel.Rang
Dim t As Strin
Dim tt As Strin

If pfIsAdmin The
For Each wks In m_XLWbk.Worksheet
With wk
.Unprotect "ctxs
.Visible = xlSheetVisibl
.ScrollArea = vbNullStrin
.Rows.EntireRow.Hidden = Fals
.Columns.EntireColumn.Hidden = Fals
End Wit
Next wk
m_XLWbk.Worksheets(WKS_MAIN).Range("Version").Locked = Fals
Els
For Each wks In m_XLWbk.Worksheet
If wks.Name = WKS_CTX Or wks.Name = WKS_ENABLE_MACROS The
wks.Visible = xlVeryHidden
Else
With wks
t = colScrollArea(.Name)
.Unprotect "ctxs"
.Visible = xlSheetVisible
.ScrollArea = t

tt = rowsToHide(t)
.Rows(CInt(Mid(tt, InStr(tt, "/") + 1))).RowHeight = 13.5
.Rows(Left$(tt, InStr(tt, "/") - 1)).EntireRow.Hidden = True

tt = columnsToHide(t)
.Columns(Mid(tt, InStr(tt, "/") + 1)).ColumnWidth = 1.43
.Columns(Left$(tt, InStr(tt, "/") - 1)).EntireColumn.Hidden = True

.Range(t).FormulaHidden = True
.Range(t).Locked = False
.Protect "ctxs"
End With
End If
Next wks
End If
m_XLWbk.Worksheets(WKS_MAIN).Activate
End Sub

 

[Rob Bovey]

Hi Joe,

<<I'm developing this for end users who will not be able to recheck the
component in the dialog.
Is it possible to do this within VBA? Would you use the
References.AddFromFile or the References.AddFromGUID? How do you determine
the DLL's GUID?>>

References.AddFromFile would be easiest, but that's a very quirky way to
handle it. This is really an installation issue. If your installer program
registers the DLL properly on the user's machine then there shouldn't be any
referencing problems, so I'd suggest trying to figure out why the installer
isn't registering the component properly.

Do your users have an earlier version of this component installed? If
so, that may be causing the problem. If that's the case you can unregister
and remove the old version and replace it with the new version.

<<When I run the application of my machine (XL 2002 and Windows 2000), after
I re-establish the References, XL runs fine. If I run it on a Windows XP
machine (w/ XL 2002), and the references are retained by Excel, it gives a
Error 71, Disk not ready error. And if I run it on a Windows Server 2000
machine with XL 2002, it gives a Error 52, Bad file name or number error.>>

Try downloading the code cleaner utility from my web site and run it on
your project. It sounds like you might be experiencing some code corruption.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

Rob,

Thanks for your help. I'm developing this for end users who will not be

able to recheck the component in the dialog.

Is it possible to do this within VBA? Would you use the

References.AddFromFile or the References.AddFromGUID? How do you determine
the DLL's GUID?

Do you know of any routines that could check to see if the DLL is

referenced and, if it is not, set a reference to it?

Thanks again,

Joe
----- Rob Bovey wrote: -----

Hi Joe,

If you have a MISSING reference on a machine where you're sure the
component is properly registered, try unchecking the missing reference and
closing the References dialog, then reopen the References dialog and recheck
the reference.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


close

about the same thing. In the Excel

Visual button will
get a navigate to
and and add
it to talking
about when they referred path
that it patha

easiest way is to attempt to register the DLL with the