Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2019
  • Last Modified:

Referencing Excel Object Library

There are over 75 computers on a LAN that run an Access 2003 application.  The application was recently modified to reference the "Microsoft Excel 15.0 object library" and now includes VBA code to open an existing spreadsheet, modify the spreadsheet and save it.  Other computers on the LAN that need to run this Access .mdb program either have an older version of excel or don't have excel installed.  

The program didn't open on a computer that has an older version of excel (with a runtime version of Access 2003), so I found the path that the referenced file is in on the source/development computer (c:\Program Files\Microsoft Office 15\Root\Office15"), created that exact path on the other computer and copied the referenced file (excel.exe) to it.  The program then opened on the other compter, but the code to open a spreadsheet and modify it did not work.  

How can I get the program to work on all the computers?  Everyone needs to be able to not only open the access program but also run the code to modify the spreadsheet.  I was hoping that I could copy version 15 of excel.exe to a network share and reference it from there, but I doubt this would work given that the code to modify the spreadsheet didn't work after creating the same exact path on the other computer and copying excel.exe to it.
0
Declan_Basile
Asked:
Declan_Basile
6 Solutions
 
mbizupCommented:
Use Late Binding in your code instead of early binding.  This will work regardless of versions.

It basically involves declaring your Excel Objects as Objects rather than Excel.Application, etc.

This site is geared towards Word, but the code and explanations apply too Access VBA as well:
http://word.mvps.org/faqs/interdev/earlyvslatebinding.htm
0
 
Rey Obrero (Capricorn1)Commented:
to make this work for all computers, you  need to use Late Binding in your codes..

see this link

http://support.microsoft.com/kb/245115
0
 
mbizupCommented:
The downside to using Late Binding is that you lose the VBA Editor's 'intellisense', which autocompletes code for you.  To handle that, you can write Dim statements to declare your objects for both early and late binding, and comment out declarations depending on whether you are developing (where Early Binding is beneficial for the autocomplete) or releasing your database for production (where you want Late Binding for code that is not version dependent).
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Gustav BrockCIOCommented:
You can stay with early binding, but then you in your deployed file need to reference the oldest version of Excel that can be encountered.

When launched on a machine with a newer version, the application should discover and adapt to this newer version. "Should" means that it is supposed to act this way but for some references it may fail. However, that shouldn't be the case for Excel.

The other way round, adapt to an older version, will - as you have experienced - not work.

/gustav
0
 
PatHartmanCommented:
You can't just move excel.exe around.  Excel must be "installed".  It requires lots of files as well as registry entries.  

Programs with NO version of Excel installed will not be able to use your application if you use early binding.  If you use late binding, they will be able to use the app but not the Excel interface.  With early binding, the references are checked when the app opens and you get sometimes really strange errors if you have a missing reference but with  late binding, the reference isn't checked until you actually use it.  That's why late binding allows you to use the app up to a point.

Here's some code from an app that uses conditional compilation to handle the binding issue.  This method gives you early binding for coding when you need it and late binding later.  You just have to remember to toggle the switch.

' 0 if Late Binding
' 1 if Reference to Excel set.
#Const ExcelRef = 0
#If ExcelRef = 0 Then ' Late binding
    Dim appExcel As Object     'Excel Object
    Dim wbkNew As Object    'Workbook Object
    Dim wksNew As Object    'Sheet Object
    Dim wbkTemplate As Object   'Workbook Object for Template

    Set appExcel = CreateObject("Excel.Application")
    ' Remove the Excel reference if it is present   -   <=======
    On Error Resume Next
    Set ref = References!Excel
    If Err.Number = 0 Then
        References.Remove ref
    ElseIf Err.Number <> 9 Then 'Subscript out of range meaning not reference not found
        MsgBox Err.Description
        Exit Sub
    End If
' Use your own error handling label here
On Error GoTo FormatWeeklyJobStatus_Error
#Else
    ' a reference to MS Excel <version number> Object Library must be specified
    Dim appExcel As Excel.Application      'Excel Object
    Dim wbkNew As Excel.Workbook        'Workbook Object
    Dim wksNew As Excel.Worksheet       'Sheet Object
    Dim wbkTemplate As Excel.Workbook   'Workbook Object for Template

    Set appExcel = New Excel.Application
#End If

Open in new window


Also, as someone already mentioned, you can keep an old version of Excel installed.  If you have multiple versions of Excel (or Word - you can't have multiple versions of Outlook), you can specifically choose to reference the old version rather than the new and that reference will go with the database when you deliver it.  Access will "promote" references so if you have a reference to an older version than what Access finds installed, Access will "promote" the reference to the newer version but it will not "demote" references.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
copied the referenced file (excel.exe) to it.
This is not only a bad idea (and won't work, as PatHartman said), it also violates your Microsoft Licensing agreement. There are no redistributable Excel files, so you can only move a specific version of Excel to another machine by purchasing a licensed copy of Excel and installing it on that machine.
0
 
Declan_BasileAuthor Commented:
Each and every comment was very helpful.  Thank you everyone.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now