By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,758 Members | 1,250 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,758 IT Pros & Developers. It's quick & easy.

adding library references via VBA - worked before but now it doesn't

P: 1

I work on a database that needs to work on French and English version of Windows and Office - and work on computers with any combination of Windows 2000, Windows XP, Office 2000, Office XP, Office 2003.

The database was transferable between all of the above but to make it work it was necessary to remove some reference libraries - in particular, Microsoft Excel object library, ADOX and ADOR because they have different locations depending on what combination of systems resided on the computer one was using.

The removing of the reference libaries and reinstalling on startup worked very well for the past 4 years, but now Access, will not allow it. It stops the VBA when it finds reference to Excel objects in the code before it has executed the code to add the object libraries.

The code I am using to add the libraries is as follows:
Expand|Select|Wrap|Line Numbers
  1. Set fs = CreateObject("scripting.filesystemobject")
  3. Set ref = Application.References("adodb")
  4. 'if error boolAdd is set to true
  5. If boolAdd Then
  6.     'check to see if it a French system
  7.     If fs.folderexists(strDriveLetter & cstrRoot & cstrComFilFr) Then
  8.         Set ref = Application.References.AddFromFile(strDriveLetter & cstrRoot & cstrComFilFr & cstrSysADO & cstrADO)
  9.     'otherwise set it for English system
  10.     Else
  11.         Set ref = Application.References.AddFromFile(strDriveLetter & cstrRoot & cstrComFil & cstrSysADO & cstrADO)
  12.     End If
  13.     boolAdd = False
  14. End If
The problem is that VBA now stops the execution of the code because it says excel.worksheets is a user defined type that is not defined.

Any suggestions?

Oct 3 '06 #1
Share this Question
Share on Google+
1 Reply

Expert 100+
P: 181
I had the similar problem ones, and I tried to use the similar code to reconnect to library database at startup.
So I've seen two possible scenarios:
1. Access MDB updates the reference by himself, althogh the location is not the same.
2. Access fails to update the reference and raises an error, before the execution of my code.

In both cases, the above code of updating references becomes useless, not to mention it will not work in mde.

I'd suggest to use late binding when possible, especially with Excell.
Oct 3 '06 #2

Post your reply

Sign in to post your reply or Sign up for a free account.