How to Refer to Excel 12.0/14.0 Object Library in Access 2007 and 2010

[EddieN1]

I am referencing MS Excel 12.0 and MS Access 12.0 Object Libraries in an Access 2007 Database. When I open this with an Access 2007 version, both references stay as 12.0.



When I open the database with Access 2010, the Access 12.0 gets changed to Access 14.0 and Excel 12.0 gets changed to Excel 14.0 and everything is still fine.

However, if I open it in Access 2010, then close it, it appears that the Excel Reference is saved as Excel 14.0. If I then open it in an Access 2007 version, the Access Object Library changes back to Access 12.0 Object Library and is located in C:\Program Files\Microsoft Office\Office12\MSACC.OLB. However, the Excel Object Library remains as Excel 14.0 Object Library, is flagged as MISSING and shows the Location as C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.EXE and, of course, since the User is using Office 2007, can't find it.

Any suggestions would be appreciated.

Thanks, Eddie

[June7]

Maybe don't set library references and use late binding in code.

[EddieN1]

After more research I have decided that the only practical way to do this is through Late Binding. Since I only need this in one Subroutine, I added the binding code to that procedure... and it turned out to be quite easy. I also removed the Reference to Excel 12.0 Object Library since my Late Binding eliminated its need. Thanks for the help, Eddie

[tonez90]

Hi As I have not used Late Binding any chance of posting an example using the Excel example above.
Thanks Tony

[pbaldy]

http://support.microsoft.com/kb/245115

[EddieN1]

Tony,

Don't know if you still need this or not, but here's what I did. This is in an Access database that manipulates data in an Excel Workbook.

Dim xl As Object
Dim Wb As Object
Dim Ws As Object

Const xlLeft As Long = 2
Const xlRight As Long = 4

Set xl = CreateObject("Excel.Application")

xl.Workbooks.Open "C:\Temp\FeeSchedModel.xlsx"

Set Wb = xl.ActiveWorkbook

Set Ws = Wb.Activesheet

Ws.Name = "Sheet1"

etc...

Hope this helps... Eddie