Solved

Referencing Excel Object Library

Posted on 2013-11-21
7
1,633 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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 36

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

828 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