Solved

Referencing Excel Object Library

Posted on 2013-11-21
7
1,489 Views
Last Modified: 2013-11-25
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
Comment
Question by:Declan_Basile
7 Comments
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 167 total points
ID: 39667372
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
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 84 total points
ID: 39667373
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
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 167 total points
ID: 39667382
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 83 total points
ID: 39668270
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
 
LVL 34

Accepted Solution

by:
PatHartman earned 83 total points
ID: 39669691
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
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 83 total points
ID: 39671273
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
 
LVL 1

Author Closing Comment

by:Declan_Basile
ID: 39676272
Each and every comment was very helpful.  Thank you everyone.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now