Excel 2003 with VBA - "Can't find project or library"

[johnacooke]

I have a problem with Excel 2003 that manifests itself as follows: can't find project or library

Background
==========

Two years ago, I built a sophisticated payroll management system for contractors for a small business, using Microsoft Excel 2003,
extended by VBA.

It has been used successfully on a network of 3 PCs operating under Windows XP.

The system is based up on a workbook (call it XXX.xls), supplemented by a DLL written in VB6, named XXX.dll. Most of the VBA lies in the DLL to facilitate maintenance.

The workbook and its associated DLL are installed on each PC, with a shortcut to XXX.xls placed on the Desktop.

When activated, XXX.xls inquires a SQL Server database on a network server to display a drop-down combo-box of names and businesses.

Assuming the user picks a suitable candidate (say "MyBusiness"), XXX.xls will either:

create a workbook MyBusiness.xls in the folder "MyBusiness" on the server,
or open an existing workbook called MyBusiness.xls.

Problem
=======

The problem arose when a fourth PC was added to the network, and when a failed PC was replaced.

Double-clicking XXX.xls on either of these PCs produces the error message "can't find project or library", when the user selects any business,
but only on these machines.

The DLL is present and should be available. Control can be obtained by manipulating the Tools | References dialog, but this is very tedious, since there are more than 200 separate workbooks.

I have tried executing the following command when logged in as administrator:

regsvr32 C:\XXX\XXX.dll

but to no avail.

I have manually checked the list of DLLs used by Excel and my DLL on three of the four PCs (i.e. XXX.dll and other Microsoft DLLs),
looking for disparities. Though there are some, none should account for the behaviour observed.

Three of the four PCs share the same build state for Excel.

I am somewhat perplexed. I cannot help thinking that there might be some environmental settings (e.g. privileges, permissions, access rights) that are affecting the operation.

I have researched the problem, but not come up with a solution.

Has anyone encountered anything similar to this problem, and more importantly, found a solution?

I would be grateful for any insights or possible leads that might enable me to solve the problem.

Many thanks.

 

[GS]

If the newer machines run later OSs than XP then I'm thinking your DLL
is not being registered due to permissions. You must have admin
privileges to modify the Registry in the way you're describing. If you
use a Setup.EXE installer then that's the place to reg the DLL.

I'm not getting a sense of how the XLSs access the DLL, or how a ref is
set in the VBA project for the workbooks. I'd be inclined to use an
addin so the project workbooks don't contain code. Better yet is to
make a VB6.EXE that uses its own instance of Excel, and dispense with
the DLL altogether. Now you have nothing to register because all the
code is in your EXE.

Alternatively, you could make your DLL a COMAddin that gets loaded into
any non-automated instance of Excel.

You do know you can *tag* workbooks to identify them as your project's
working file, right?

Finally, there is a mechanism for using DLLs reg-free via Olaf
Schmidt's DirectCom.dll if you want to avoid having to register
anything. Also, with a VB6.EXE you can use a manifest to run reg-free
on XP or later OSs.

HTH

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[witek]

I have a problem

1. check tool/references in VBE. See if there is any "missing" library.

2.
If it was written in VB6 i can almost be sure that one of VB6 libraries
is missing.
Start with the most popular comdlg32.ocx
If it does not help install VB6 on that computer and see if it works.

you can start with runtime libraries first from
http://support.microsoft.com/kb/192461


3. download dependency walker from http://www.dependencywalker.com/
load your xxx.dll and check what other libraries used by your dll are
missing


4. If you have source code debug your dll on that computer.

 

[johnacooke]

I have a problem with Excel 2003 that manifests itself as follows: can't find project or library



Background

==========



Two years ago, I built a sophisticated payroll management system for contractors for a small business, using Microsoft Excel 2003,

extended by VBA.



It has been used successfully on a network of 3 PCs operating under Windows XP.



The system is based up on a workbook (call it XXX.xls), supplemented by a DLL written in VB6, named XXX.dll. Most of the VBA lies in the DLL to facilitate maintenance.



The workbook and its associated DLL are installed on each PC, with a shortcut to XXX.xls placed on the Desktop.



When activated, XXX.xls inquires a SQL Server database on a network server to display a drop-down combo-box of names and businesses.



Assuming the user picks a suitable candidate (say "MyBusiness"), XXX.xls will either:



create a workbook MyBusiness.xls in the folder "MyBusiness" on the server,

or open an existing workbook called MyBusiness.xls.



Problem

=======



The problem arose when a fourth PC was added to the network, and when a failed PC was replaced.



Double-clicking XXX.xls on either of these PCs produces the error message "can't find project or library", when the user selects any business,

but only on these machines.



The DLL is present and should be available. Control can be obtained by manipulating the Tools | References dialog, but this is very tedious, since there are more than 200 separate workbooks.



I have tried executing the following command when logged in as administrator:



regsvr32 C:\XXX\XXX.dll



but to no avail.



I have manually checked the list of DLLs used by Excel and my DLL on three of the four PCs (i.e. XXX.dll and other Microsoft DLLs),

looking for disparities. Though there are some, none should account for the behaviour observed.



Three of the four PCs share the same build state for Excel.



I am somewhat perplexed. I cannot help thinking that there might be some environmental settings (e.g. privileges, permissions, access rights) that are affecting the operation.



I have researched the problem, but not come up with a solution.



Has anyone encountered anything similar to this problem, and more importantly, found a solution?



I would be grateful for any insights or possible leads that might enable me to solve the problem.



Many thanks.

Thank you to "GS" and "witek" for your responses.

GS:

I am 90% convinced that permissions (or lack of them) are responsible for the problem that I am experiencing.

I didn't use a set up program to install the software; I just copied files into a folder and set up a shortcut on the desktop. It seemed to work OK. I am a minimalist where coding is concerned.

The DLL contains a number of user-defined functions that Excel uses. In compiling the DLL using VB6, I created an object called clsExternal containing the functions, having GlobalMultiUse instancing.

When I did my initial research before starting this project, I looked at Add-Ins, but decided that they had their own set of problems, so dismissed them.

Only 5% of the useful VBA code resides in the Excel workbook; the rest lies in the DLL.

At the same time, I also discarded the COMAddin route; perhaps, this was a mistake in hindsight.

The DLL is identified by setting up a reference to it in the VBE at design time, through the familiar Tools | References mechanism.

I know that the DLL is present, since I had placed it in the relevant folder on installation. It isn't missing; it just can't be found for some reason!

I am not sure what you mean by "tagging workbooks to identify them as your project's working file". Can you point me to some suitable reference, please?

Perhaps, it was a miracle that the original software worked at all, and only did so by inadvertentedly exploiting loopholes.

Perhaps also, I should not have based my solution on Automation.

witek:

I did think about missing items, but could not find any. Perhaps, I didn't try hard enough. I will do so now.

both:

I will certainly examine your advice more closely, and experiment. I am grateful for your feedback and suggestions.

 

[GS]

Your reply suggests the Tools>References being used at design time is
not removed before distribution, and so the XLSs rely on 'early
binding' to use the DLL. You mention "network PCs" and so perhaps they
all don't point to the same location for the ref.

If you're sure the DLL is properly registered then it should be
accessible by your VBA projects. Optionally, you could not register it
and use a Declare statement to an internal function that receives args
for which properties/methods you want to use from VBA. This would
require a methodology be implemented that employs CallByName, and use
delimited args for passing parameters. I used to go this route when
using DLLs that were registered on the host machine. I use a different
approach now that all my apps (VB6/VBA) run reg-free so they can be
portable (used from a flash drive or memstick). I still use CallByName
as described, though.

I abandoned using COMAddins because they need to be registered on the
host system to be recognized by Excel. This doesn't serve my need for
them to be portable! (they don't make any mods to the host PC beyond
saving files to the user's preferred folders)
===

Tagging files as belonging to your project:
This is simple to implement by using CustomDocumentProperties (can be
edited by users), OR a hidden DefinedName (not easily edited by users)
to store a value. I use a 'veryhidden' sheet and the DefinedName has
local scope to that sheet (as well as being hidden so it doesn't appear
in the builtin Names lists). Both methods can be set up when creating
new workbooks.
===

<FWIW>
My apps use their own instance of Excel. The UI is customized to the
point that users may not know they're using Excel. My app files use
their own file extensions and, in most cases, can only be opened within
my app because they employ strong FileOpen password encryption. Both
app and Excel settings are stored in INI/DAT/TXT/INF files, some of
which are only used/exist at runtime. I use an XLA for both early/late
versions of Excel and an XLSA with custom XML for UI setup in the later
versions. The XLA stores Excel settings at startup, restores them at
shutdown. It also creates its own custom menubar/toolbars/context
popups. The same DLL method is used for all menuitems is the entry
point to the app code. This employs CallByName and procedure name is
stored in the menuitem`s `Tag` property. Procedure args are stored in
its `Parameters` property as a delimited string. Each procedure knows
how to parse their values from the string. The XLAs run reg-free via
Olaf Schmidt's DirectCOM.dll. It all seems very complex (but really
isn`t due to reusability of common components), but well worth the
effort IMO to avoid any manifestation of "DLL hell" and be able to run
apps that don't leave any messes behind!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[GS]

Just a thought after reading witek's post again...

Is the replacement PC running Vista/Win7? If so, any of the
dependancies used by the DLL need to be distributed and registered on
that PC. I use a manifest (MakeMyManifest.exe) to handle this so
dependancy registration isn't required.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[johnacooke]

I have a problem with Excel 2003 that manifests itself as follows: can't find project or library



Background

==========



Two years ago, I built a sophisticated payroll management system for contractors for a small business, using Microsoft Excel 2003,

extended by VBA.



It has been used successfully on a network of 3 PCs operating under Windows XP.



The system is based up on a workbook (call it XXX.xls), supplemented by a DLL written in VB6, named XXX.dll. Most of the VBA lies in the DLL to facilitate maintenance.



The workbook and its associated DLL are installed on each PC, with a shortcut to XXX.xls placed on the Desktop.



When activated, XXX.xls inquires a SQL Server database on a network server to display a drop-down combo-box of names and businesses.



Assuming the user picks a suitable candidate (say "MyBusiness"), XXX.xls will either:



create a workbook MyBusiness.xls in the folder "MyBusiness" on the server,

or open an existing workbook called MyBusiness.xls.



Problem

=======



The problem arose when a fourth PC was added to the network, and when a failed PC was replaced.



Double-clicking XXX.xls on either of these PCs produces the error message "can't find project or library", when the user selects any business,

but only on these machines.



The DLL is present and should be available. Control can be obtained by manipulating the Tools | References dialog, but this is very tedious, since there are more than 200 separate workbooks.



I have tried executing the following command when logged in as administrator:



regsvr32 C:\XXX\XXX.dll



but to no avail.



I have manually checked the list of DLLs used by Excel and my DLL on three of the four PCs (i.e. XXX.dll and other Microsoft DLLs),

looking for disparities. Though there are some, none should account for the behaviour observed.



Three of the four PCs share the same build state for Excel.



I am somewhat perplexed. I cannot help thinking that there might be some environmental settings (e.g. privileges, permissions, access rights) that are affecting the operation.



I have researched the problem, but not come up with a solution.



Has anyone encountered anything similar to this problem, and more importantly, found a solution?



I would be grateful for any insights or possible leads that might enable me to solve the problem.

Many thanks for all your advice.

I have no control over the PC network involved; it is managed by someone else.

What are the minimum set of permissions needed for ActiveX automation to succeed?

All PCs operate under Windows XP.