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")
lpiiAsked:
Who is Participating?
 
Fabrice LambertConnect With a Mentor Fabrice LambertCommented:
Hi,

Looks like a typical reference problem.
Remove any reference to Ms Excel, and use late binding:
Dim AppExcel As Object
Set AppExcel = CreateObject("Excel.Application") 

Open in new window

1
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
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.  
To be clear, you cannot distribute any of the Office libraries. Your end user would be expected to have those libraries on their machine, and the only way to get those is by purchasing and installing Office. Using the GetObject syntax would ensure that your code would run with any version of Excel, with the caveat that you use methods/functions available to all versions.

That said, if your target machine has Office 2010 installed, this should work, since your 2007 reference (if it's there) would be upgraded.

More likely would be a problem with other references, as Fabrice said. Can you show a screen shot of the references in your application?
0
 
lpiiAuthor Commented:
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.

Refernces for Code
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
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
Fabrice LambertConnect With a Mentor Fabrice LambertCommented:
Most of the time, it is a bad idea to use early binding, references always give troubles.

Additional notes:
- Always write an error handler when acquiring ressources (in your case, recordset and Excel) so you can take proper actions if something goes wrong.
- Check your recordset isn't empty (that is: BOF and EOF are not true).
- In an object hierarchy, either use the with statement, or set a reference instead of dereferencing the whole hierarchy. Your code will be shorter and easyer to read:
With PrsMod.Sheets(1)
    .Range("A2").value = ......
    .Range("A6").value = .....
End With

Open in new window

Or
Dim ws As Object
Set ws = PrsMod.Worksheets(1)
ws.Range("A2").value = .....
ws.Range("A6").value = ....
Set ws = Nothing

Open in new window

- I'm not a big fan of the "garbage collector", so I always explicitly unload objects when I no longer need them:
Dim xl as Object
Set xl = CreateObject("Excel.Application")
    '// random code using xl object

    '// xl is no longer needed, unload it.
Set xl = Nothing

Open in new window

0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
I doubt you need the Extensibility library. That's typically only used if you're automating something in VBA (like adding code to a Module, for example).

DAO 3.6 is not generally needed with 2010 files, and that may be your issue. It would very likely not be installed on a new Win10 machine. Try this:

1. Compile your code - in the VBA Editor, click Debug - Compile. Fix any errors, and continue doing that until the Compile item is disabled
2. Remove the DAO 3.6 reference. Be sure to make note of where the file is located, however, in case you need to re-enable it.
3. Compile your code again.

If your code compiles in step #3, then you don't need the DAO 3.6 reference.

As others have mentioned, if you're using LateBinding (i.e. GetObject or CreateObject) you don't need that Excel reference. You would have to change your DIM statements to remove those specific references to Excel. Instead of:

Dim AppExcel As Excel.Application
Dim PrsMod As Excel.Workbook

You'd do this:

Dim AppExcel As Object
Dim PrsMod As Object

Note that if you remove the Excel reference, you also must be sure that any constants you've used that are specific to Excel are handled in your Access application. Many people will use Early Binding (i.e. include the Reference) when doing development work (since you get Intellisense), and then switch over to Late Binding before deployment.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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).
0
 
lpiiAuthor Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.