Solved

Referencing Excel Object Library

Posted on 2013-11-21
7
1,590 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 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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 35

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

821 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