Launch Excel and Open a Spreadsheet from VB6 - January 2004

If you download this tip please consider making a modest donation of a $1.00 or more.

One application I created launches Excel from VB6, causes Excel to open a spreadsheet file then executes an imbedded macro. A new instance of Excel was started each time the user opened the spreadsheet. The user asked me to first check to see if Excel was running and if so use that instance rather than launch a new one. So I did some research - learned more about application automation than I wanted - but did figure out how to accomplish the objective using both early and late binding. I created a class module with some methods that will launch Excel and open a file. I generalized the code and made it the January 2004 tip-of-the-month.

July 2010 - Read Excel Values - You may want to view the July 2010 tip which shows how to read Excel data into a collection. Click here to view that tip.

Demo Program

I wrote a demo VB6 program to illustrate the use of the class. The demo:

frmExcel

Is Excel Running? - Click the button to test if an instance of Excel is already running. Actually this test is performed automatically every second using a timer but the underlying code demonstrates the 'IsRunning' method.

Launch Excel then Open a New Workbook - Click the button to launch Excel then open a 'blank' worksheet. If Excel is running the button caption changes to 'Open a New Workbook in the Existing Instance of Excel'.

Excel Filename - The fully qualified Excel file to be opened. Use the 'browse' button to search for a file and open it.

Launch Excel then Open 'Filename' - Click this button to launch Excel and open the defined file. If Excel is running the button caption changes to 'Open Filename in the Existing Instance of Excel'.

Early and Late Bound Versions

I created two class modules - one using early binding and one late binding. The early binding class module is called 'cExcelEB'. The late binding one is called 'cExcelLB'. Typically you would use just one of the modules in your application. I used a conditional compilation argument [CCA] to specify early or late binding, for example in the Project Properties Make dialog:


When CCA ExcelEarlyBinding is set to zero early binding is not used.
When set to 1 early binding is used.
Demo default is zero - late binding.

Add a Reference to Excel

Add a reference to the Excel type library if early binding is used, for example:


I used Excel version 10 type library in this example. Other versions could be used.

Methods and Properties

Early Bound
Early Bound - Uses Excel objects.

Late Bound
Late Bound - Uses type 'Object'

cExcelEB - Early Method Operands Returns Example
        Dim oExcelApplication as Excel.Application
Dim oExcelWorkbook as Excel.Workbook
Dim obj as New cExcelEB
Is Excel running? IsRunning   True or False If obj.IsRunning Then
Start Excel Start oExcelApplication _
[, bNewInstance]
True or False obj.Start oExcelApplication
Open a File StartFile oExcelApplication, _
oExcelWorkbook, _
sFilename _
[, bNewInstance]
True or False obj.StartFile oExcelApplication, oExceWorkbook, "MyXls.xls"
  bNewInstance   True or False Force a new instance, default is true.
cExcelLB - Late Method Operands Returns Example
        Dim oExcelApplication as Object
Dim oExcelWorkbook as Object
Dim obj as New cExcelLB
  IsRunning   True or False If obj.IsRunning Then
  Start oExcelApplication, _
[, bNewInstance]
True or False obj.Start oExcelApplication
  StartFile oExcelApplication, _
oExcelWorkbook, _
sFilename _
[, bNewInstance]
True or False obj.StartFile oExcelApplication, oExceWorkbook, "MyXls.xls"
  bNewInstance   True or False Force a new instance, default is true.

Sample Calling Sequence

'Early Bound
Dim oExcelApplication As Excel.Application
Dim oExcelWorkbook As Excel.Workbook
Dim o As New cExcelEB
Dim sFileName As String

sFileName = "MyXls.xls"
With o
    If .StartFile(oExcelApplication, oExcelWorkbook, sFileName, False) Then
        ...
    End If
End With
'Late Bound
Dim oExcelApplication As Object
Dim oExcelWorkbook As Object
Dim o As New cExcelLB
Dim sFileName As String

sFileName = "MyXls.xls"
With o
    If .StartFile(oExcelApplication, oExcelWorkbook, sFileName, False) Then
        ...
    End If
End With

The Demo Program Uses These Modules

Module - Click for More Info Description Used in the Demo To
cExcelEB.cls
cExcelLB.cls
Launch Excel The subject of this tip.
Include one in your application.
CDIALOG.CLS Browse for file by Francesco Balena Browse for Excel file.
modAddBackSlash.bas Add a Backslash if Needed Add a backslash to App.Path, if needed.
modSleep.bas Delay program execution. Simulate 'testing' message.
modConnectToWebSite.bas Connect to a web site Show this info when Help/Info is
selected from the demo.
modInfoAbout.bas DoInfo, DoAbout, DoTip Link to this page and provide helpful information

Excel VBA Help

Where do I find help for Excel? I found the best way to find out how to use Excel methods and properties is to launch Excel then:

Excel Tools/Macro/Visual Basic Editor Menu
From Excel Select this menu tree.

Enter a Search Argument
In VBA press F1 then enter a search argument.

Excel Help
Searched Results - Help for VBA Excel

Download VB Code

Click here to download the VB 6 source code [18k].

If you download this tip please consider making a modest donation of a $1.00 or more.
If you download this tip please consider making a modest donation.

Previous Tip   Tip Index   Next Tip

Home

Updated 2010/07/07