Solved

Linking Office Programs with VBA

Posted on 2014-02-16
4
417 Views
Last Modified: 2014-02-16
I have an application written in MSAccess 2010.  It is a large and complex application and it works fine.  I distribute this application with the MSAccess runtime environment.  This environment only contains the Access runtime engine and no additional MS Office software.

I have a user request to add a feature to dynamically create an MSExcel spreadsheet with data from the application.  To include this feature I have to link the Microsoft Excel 14.0 object library so that calls to Excel.Application, and all the workbook, sheet, and cell functionality can be used.

The problem that I have is that if I include this library, then the software won't run on machines that have NO excel installed, even if I don't use this functionality.  It is just a matter of the dynamic linking at load time.  Even more so, if the user has excel 2007, 2003, or any other than 2010, the code won't load either.

So, what I'm looking for is a way to handle load time errors, just like I can handle runtime errors with an "on error" statement.  If I can handle these load time errors, then I can add conditional code that will allow calls to the appropriate routines if needed/allowed/desired.

Anybody have a clue?
0
Comment
Question by:samjomoore
  • 2
  • 2
4 Comments
 
LVL 33

Expert Comment

by:Norie
ID: 39863635
You don't need the reference if you use late-binding, so that would take care of the version problem.

Not sure about how you would handle the case when a user doesn't have Excel installed.

Actually what you could do is something like this.
Dim xl As Object

    On Error Resume Next
    Set xl = CreateObject("Excel.Application")
    On Error Goto 0

    If xl Is Nothing Then
        MsgBox "This function requires Excel to be installed and available.
        Exit Sub
    End If

Open in new window

0
 
LVL 1

Author Comment

by:samjomoore
ID: 39863638
Awesome!  How to I use late binding?
0
 
LVL 33

Accepted Solution

by:
Norie earned 500 total points
ID: 39863664
Well one of the first things you do in late-binding is to change declarations like this,
Dim xl As Excel.Application
Dim ws As Excel.Workbook
Dim rng As Excel.Worksheet

Open in new window

to this.
Dim xl As Object
Dim wb As Object
Dim ws As Object

Open in new window


You would also change something like this,
Set xl = New Excel.Application

Open in new window

to this.
Set xl = CreateObject("Excel.Application")

Another important thing is that if your code uses any of the constants that the other application uses, eg xlUp, xlShiftToLeft etc, then you either need to declare them yourself or replace them with their values.

This is how you would declare xlUp and xlShiftToLeft.
[code]
Const xlUp = -4162
Const xlShiftToLeft = -4159

Open in new window


Those are the main things you need to do.
0
 
LVL 1

Author Closing Comment

by:samjomoore
ID: 39863667
Awesome!  Thank you so much!
0

Featured Post

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.

Question has a verified solution.

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

Suggested Solutions

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
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…

863 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

26 Experts available now in Live!

Get 1:1 Help Now