Solved

Referencing Excel Object Library

Posted on 2013-11-21
7
1,832 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 51

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 38

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 85

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

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.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

624 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