Login

Name:
Password:
Remember me?
Register

Automating Excel from VB 6.0

written by Mike Rosenblum - Last updated Dec 2004

Contents:

• Automating Excel from VB 6.0
• oExcel vs. Application
• Cleanup
• "Your Code Goes Here"

Automating Excel from VB 6.0

This is a primer on using OLE Automation to create an Excel instance in Visual Basic 6.0 and control Excel from a VB6 Application. The following would also apply for Automating Excel from another Office Application such as Word, Access or Outlook.

The first step in Automating Excel is to add a Reference to the Microsoft Excel Object Library:

1. Within VB 6.0, choose Alt|Project|References...
2. Scroll down until you find "Microsoft Excel 10.0 Object Library" (which could be 8.0, 9.0 or even 11.0). Click within the check box and then click <OK>. Now your Library reference is loaded.

Next you write your code!

A basic shell for creating a new Excel Object Reference and disposing of it cleanly could look like the following:

 
Sub Main() 
    Dim oExcel As Excel.Application 
    Dim oWB As Excel.Workbook 
    Dim oWS As Excel.Worksheet 
     
    Set oExcel = New Excel.Application 
    oExcel.Visible = TRUE ' <-- ** Optional ** 
     
    ' Your Code Goes Here 
    ' Your Code Goes Here 
    ' Your Code Goes Here 
    ' Your Code Goes Here 
     
Cleanup: 
    On Error Resume Next 
    oExcel.DisplayAlerts = FALSE 
     
    Call oWB.Close(SaveChanges:= FALSE) ' <-- ** Or TRUE ** 
    Set oWB = Nothing 
     
    oExcel.Quit 
    Set oExcel = Nothing 
End Sub 

oExcel vs. Application

In the above, your Excel.Application instance is created with the following two lines:

Dim oExcel As Excel.Application 
Set oExcel = New Excel.Application 

From then on you will control your Excel instance by making use of oExcel at all times. Do not use the word "Application" as you would in VBA.

It is a cruel joke played by Microsoft, but any usage of the global 'Application' reference will appear to run correctly within VB 6.0, but in fact this is true only the first time you run your code. Unfortunately, your VB6 App would be unable to release the 'Application' global reference and your Excel instance would "hang", unable to close. The second time running, your code would exhibit all kinds of bizarre behavior.

To be clear: code like the following, which is perfectly legal in VBA, would be disastrous in VB6:

Range("A1").Value = 25 ' <-- 'Application' Is IMPLIED here. 
Application.DisplayAlerts = FALSE ' <-- Uses 'Application'!  No good! 
Workbooks("Book1").Close ' <-- 'Application' Is again implied. 

Instead, use the following equivalent:

 
Set oWS = oExcel.ActiveSheet ' <-- oWS now holds a reference To oExcel. 
 
oWS.Range("A1").Value = 25 ' <-- oWS holds a reference To oExcel. 
oExcel.DisplayAlerts = FALSE ' <-- 'oExcel' instead of 'Application'. 
oExcel.Workbooks("Book1").Close ' <-- Again, use 'oExcel', Not 'Application'. 

In the corrected code, above, 'oExcel' is used to refer to the currently running instance of Excel.

For further reading on avoiding the use of the 'Application' _Global:

1. Excel Automation Fails Second Time Code Runs (MSKB 178510)
2. Error or Unexpected Behavior with Office Automation When You Use Early Binding in Visual Basic (MSKB 319832)

Cleanup

Within the first example at the top of this lesson, note that the cleanup section is careful to (1) close the Workbook with 'oWB.Close', (2) exit the Excel.Application by calling 'oExcel.Quit', and then (3) set both those variables to 'Nothing'.

To help that happen smoothly, the code is making use of 'oExcel.DisplayAlerts = False' and 'On Error Resume Next' to prevent the code from either reporting errors or asking the User for input during the 'Cleanup' phase.

"Your Code Goes Here"

In the main code section, above, within the block reading "Your Code Goes Here" is where you can have fun! Some suggestions for manipulating the Excel environment could look like the following:

 
Dim oRng1 As Excel.Range 
Dim oRng2 As Excel.Range 
 
Set oWB = oExcel.Workbooks.Add 
Set oWS = oWB.Worksheets("Sheet1") 
 
Set oRng1 = oWS.Range("A1") 
Set oRng2 = oWS.Range("B2:E5") 
 
oRng1.Value ="Hello World" 
Call oRng1.Copy(Destination:=oRng2) 

Note that the above carefully avoids any reference to 'Application', either implied or otherwise. All references trace back to 'oExcel', not to 'Application'.

Finding & Repairing Unqualified References The section above, titled "oExcel vs. 'Application'", discusses the errant behavior that a program can exhibit if unqualified _Global references are present. To reiterate, the following coding style is to be avoided:

Set Rng = Range("A1") 
Set Rng = Cells(1,1) 
Set WB = Workbooks("Book1.xls") 

While perfectly valid for VBA coding, the above would cause a VB6 Program using Automation to exhibit strange behavior such as hanging or crashing. Instead, one must fully qualify their references with the Excel.Application instance variable that your program is using. For this tutorial, we have used 'oExcel' as our variable name and so the incorrect code should be corrected as follows:

Set Rng = oExcel.Range("A1") 
Set Rng = oExcel.Cells(1,1) 
Set WB = oExcel.Workbooks("Book1.xls") 

So you can see that the fix is not difficult. Simply adding 'oExcel.' to the front of the unquilified is sufficient. However, how do you find these errant references? The difficulty in finding these errors is that that you will almost never get a run-time error at the location of the offending code! This makes it nearly impossible to hunt down and fix. But there is a solution. What you would want to do is the following: (1) Add a New Module to your Project. (2) Delete all the code within that Module, replacing it with this:

 
Option Explicit 
Option Private Module 
 
' ====================================================================== 
' Automation Prophylactics 
' ---------------------------------------------------------------------- 
' 
' These routines are protective, preventing one from accidentally 
' making use of "Union()" Or "Worksheets", etc, which defaults To 
' Application.Union() And Application.Worksheets, respectively, And 
' would give the App ghost-reference issues And bizarre behaviour 
' when using out-of-process Automation. 
' 
' Because they are Subs (that Is, are Not Functions) And have no 
' Parameters, errors will be automatically picked up by the compiler. 
' 
' -- By Mike_R of The Code Net 2004 1202 
'     (Used With Permission.) 
 
' ---------------------------------------------------------------------- 
' Property Globals 
' ...................................................................... 
 
Sub Application() 
End Sub 
Sub ActiveCell() 
End Sub 
Sub ActiveChart() 
End Sub 
Sub ActivePrinter() 
End Sub 
Sub ActiveSheet() 
End Sub 
Sub ActiveWindow() 
End Sub 
Sub ActiveWorkbook() 
End Sub 
Sub AddIns() 
End Sub 
Sub Assistant() 
End Sub 
Sub Cells() 
End Sub 
Sub Charts() 
End Sub 
Sub Columns() 
End Sub 
Sub CommandBars() 
End Sub 
Sub Creator() 
End Sub 
Sub DDEAppReturnCode() 
End Sub 
Sub Excel4IntlMacroSheets() 
End Sub 
Sub Excel4MacroSheets() 
End Sub 
Sub Names() 
End Sub 
Sub Parent() 
End Sub 
Sub Range() 
End Sub 
Sub Rows() 
End Sub 
Sub Selection() 
End Sub 
Sub Sheets() 
End Sub 
Sub ThisWorkbook() 
End Sub 
 
' ---------------------------------------------------------------------- 
' Method Globals 
' ...................................................................... 
 
Sub Calculate() 
End Sub 
Sub DDEExecule() 
End Sub 
Sub DDEInitiate() 
End Sub 
Sub DDEPoke() 
End Sub 
Sub DDERequest() 
End Sub 
Sub DDETerminate() 
End Sub 
Sub Evaluate() 
End Sub 
Sub ExecuteExcel4Macro() 
End Sub 
Sub Intersect() 
End Sub 
Sub Run() 
End Sub 
Sub SendKeys() 
End Sub 
Sub Union() 
End Sub 
 
' ---------------------------------------------------------------------- 
' Automation Prophylactics 
' ====================================================================== 
 

Once you've dropped in this Module, the compiler will complain at a number of locations. At each point that the compiler complaints about one of these (now protected) Globals, you'll have to add "xlApp." or "oExcel." to the front if it (depending on what you called your Excel.Application instance). For example, if the compiler complained about:

Set Rng = Range("A1:C3") 

you would need to change it to:

Set Rng = oExcel.Range("A1:C3") 

Once you've fixed all these references, your program should run without any hanging. The above will cover 99% of the problems you will find. The only kinds of problems it will not find are: (1) Use of evaluation brackets such as:

Set Rng = <A1> 

This is not good coding style and will run very slowly (because Application.Evaluate() is being invoked, which is very slow) but is common in VBA code. The above can only be found by hand, or possibly by searching on "<" or ">". Once located, such code should be changed to:

Set Rng = oExcel.Range("A1") 

If there is a complex string to be evaluated withn the brackets, such as

Result = <A1+5> 

then one would instead want to use oExcel.Evaluate(), such as:

Result = oExcel.Evaluate("A1+5") 

(2)Use of Excel Library identifier Genreally the Library identifier 'Excel' should only be used when declaring a DataType, such as:

Dim oExcel As Excel.Application 

or:

Dim CalcMode As Excel.xlCalculation 

The above examples are 100% correct. However, some people occasionally attempt to use the Excel library when calling a method. The following is incorrect usage:

Set Rng = Excel.Range("A1") 

Doing this, the programmer has fooled his/her self into thinking that this is now a fully-qualified reference. But it is not. The above is incorrect, and is exactly the same as writing:

Set Rng = Range("A1") 

which you now know creates problems when using Automation. The fix is the same as always:

Set Rng = oExcel.Range("A1") 

The idea is to use your 'oExcel' (or 'xlApp' variable that holds the Excel.Application reference. In this manner you prevent the system from issuing a new _Global Excel.Application instance that would cause your program to Hang and/or exhibit other ill effects. If you have any comments, suggestions or queries about this article, then feel free to post in the Article Feedback forum and we'll be glad to help you out.

Hopefully, these tips will help you incorporate Excel into your VB6 App with ease.

Best of luck out there...

, Mike

Special Thanks

To the Excel Experts: herilane, mark007, tinyjack, Kluz, Italkid and Insomniac, for their questions, comments and guidance... Thanks guys.

Site menu:

Home | VB | MS Office | Web | Downloads | Consulting | Forum |

Search

Articles	Forum

Latest News

29/12/2021 - The site has been moved to a new server to be maintained for history's sake! It's all pretty old now...

28/05/2006 - MS Office 2007 Beta 2 has now been released.

25/05/2006 - The newly formed Professional Office Developers Association is now open to membership!

22/03/2006 - How to get windows to do a text search on PHP files (or any other filetype) that it ignores by default!

09/03/2006 - New website design is live!

12/02/2006 - First ASP.Net article is online now DropDownListBoundColumn for ASP.Net Datagrid

01/09/2005 - Learn how to create a fixed width text file from an excel worksheet.