Re: Excel References

From: Rob Bovey (Rob_Bovey_at_msn.com)
Date: 03/18/04 

Date: Thu, 18 Mar 2004 10:55:51 -0800

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 *
"Joe" <anonymous@discussions.microsoft.com> wrote in message
news:0C2825A8-DC81-4F89-89EF-EE3ED47199CB@microsoft.com...
> 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 *
>
>
>      "Joe" <anonymous@discussions.microsoft.com> wrote in message
>      news:A7EC9FFA-2E18-470D-8FE1-4A140364A6A5@microsoft.com...
>      > Please see below.
>      >>      ----- Rob Bovey wrote: -----
>      >>      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.**
>      >>             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 referred
>      >             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
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.**
>      >>             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
>      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.**
>      >>            I have done this and have still seen the MISSING flag.
This
>      >            is what has been so frustrating.
>      >>      -- 
>      >      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 *
>      >>>      "Joe" <anonymous@discussions.microsoft.com> wrote in message
>      >      news:9AE3715D-0EBF-4741-A0C0-F2DCEC2CB310@microsoft.com...
>      >> 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,
>      >>> Joe
>      >>>>>>>      ----- 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?
>      >      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 *
>      >>>>      "Joe" <anonymous@discussions.microsoft.com> wrote in
message
>      >>      news:B53CD110-146A-4202-9EA9-CD537084D4E4@microsoft.com...
>      >>> 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
>      >>>>