Searching for referenced file Excel.exe in VBA scr

Question

I have an Access application that I share with other users. We share the database on a Sharepoint site and each check it out / download it for edits, then put it back in Sharepoint and check it back in. We do it this way because response time if we all share the same copy on a server is unacceptable, and we only need one person to have acces at any one time. That's not my problem right now.

Last week, my old PC died and I replaced it with a new one. The old PC is Windows7, 32 bit, with Office 2010. The new PC is also Windows7, but 64 bit and with Office 2013. As far as I know, I did not change the database format to a new version of Access or make any other changes. The data base still works fine on my new PC.

The VBA script integrates the data base with Excel workbooks and exports data to Excel. One reference I have selected is Microsoft Excel 15.0 Object Library - the same reference I've always used. Now, when I load the database back to the server and other users downloads it, they can open the data base but get an error that says "Searching for reference file Excel.exe", followed by “Undefined function ‘Format’ in expression”.

The other users' PCs are configured the way my old one was, and they were able to use the database with no problems before I converted to Office 13 and saved my copy back to the server.

So my questions are - what is causing this, and more importantly, what can I do to fix it?

Answer 1

"... reference I have selected is Microsoft Excel 15.0 Object Library ..."

That Microsoft Excel 15.0 Object Library is the version for Office 2013. With Office 2010, the version is Microsoft Excel 14.0 Object Library.

The reference was originally 14.0 when you developed and used the db in Access 2010. When opened in Access 2013, Access adjusted the reference to its available version (15.0). Unfortunately, when you try to use the db again in Access 2010, Microsoft Excel 15.0 Object Library is meaningless to that Access version, so it doesn't know to use Microsoft Excel 14.0 Object Library instead.

The fix is to remove that reference and convert your code to use late binding.

Here is a brief example of late binding, copied from Using early binding and late binding in Automation:

' Declare the object as a late-bound object
  Dim oExcel As Object

  Set oExcel = CreateObject("Excel.Application")

Note, if your code uses Excel named constants, you will need to substitute the constants' values for their names, or declare the constants in your code.