asked on

Access 2007 Package Installer CreateObject Excel.Application casues 2950 error

I have office 2007 Pro installed on a Win 10 PC. The program runs fine on my PC from within Access and as a package install where Excel 2007 opens with the file.

When I install the program on a Win7 PC with Office 10 Small Business (so no Access 2010) the program runs normally until the last macro where it dies in the function OpenExcel() at the last step that I have shown in the code.

If I comment out this line (and all the other lines that use Excel) the program ends normally (but doesn't open the .xlsm spreadsheet, copy over data into cells). I have not enclosed the rest of the code as I began commenting out 1 line at a time to see where the code fails, and the last line I am showing generates the Action Failed 2950 error and causes the program to close when you click on Stop All Macros. The Macro has 4 Run Commands in it, all work except this macro that points to this function.

I am using the Microsoft Excel 12.0 Object Library

I am installing the package off the root drive, C:\PRISM\ which I have also added in Access as a trusted location.

I am guessing but wonder if it has to do with the Excel 12.0 Object Library, I assume that it would be brought over in the runtime modules but maybe not. I can't test it on my machine because it works on mine.

Function OpenExcel()

Dim DB As DAO.Database
Dim Fil As DAO.Recordset
Dim AppExcel As Excel.Application
Dim PrsMod As Excel.Workbook
Dim PrsFrm As Form

Set DB = CurrentDb()
Set Fil = DB.OpenRecordset("SetupLoc")
Set AppExcel = CreateObject("Excel.Application")

ASKER CERTIFIED SOLUTION

Fabrice Lambert

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Scott McDaniel (EE MVE )

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

lpii

ASKER

Here is the reference screen shot also the whole code, incase you see something else, I will try late binding. All the other code in the functions run fine and uses the same DAO. This is the only function that uses the CreateObject and Excel commands.

Function OpenExcel()

Dim DB As DAO.Database
Dim Fil As DAO.Recordset
Dim AppExcel As Excel.Application
Dim PrsMod As Excel.Workbook
Dim PrsFrm As Form

Set DB = CurrentDb()
Set Fil = DB.OpenRecordset("SetupLoc")
Set AppExcel = CreateObject("Excel.Application")
Set PrsMod = AppExcel.Workbooks.Open("C:\PRISM\PRISM MODIFY.XLSM")
Set PrsFrm = Forms![Prism Export]

AppExcel.Visible = True

PrsMod.Sheets(1).Range("A2").Value = Fil![FilLocXlsx]
PrsMod.Sheets(1).Range("A6").Value = PrsFrm![PayWkEndDT]

End Function

SOLUTION

Fabrice Lambert

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Scott McDaniel (EE MVE )

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Scott McDaniel (EE MVE )

Most of the time, it is a bad idea to use early binding, references always give troubles.

There's good and bad to using Early Binding, just like there's good and bad to using Late Binding. References are troublesome, but in general if handled properly (especially during deployment and installation) you're okay dealing with Early Binding.

Access makes it very easy to deploy your app - just copy and paste the entire file to another machine - which makes it very easy to break that app on the other machine! Properly deployed, with a real Windows installer that checks the target environment for suitability, early binding works just fine.

I do agree that error handling is important, and also garbage collection. One thing to remember:

If you open it, you close it

So when you open the instance of Excel, be sure to Close it when you're finished (assuming you want to, of course).

lpii

ASKER

Thank you both, it was definitely the Early Binding, I spent a lot of time going down the Reference rabbit hole before posting here, I would not have thought of Late Binding as a fix. Thank you once again.