Automating Excel from VB 6.0

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 also be 8.0, 9.0, 11.0, or 12.0, depending on which version of Excel you are using. 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:
Set oWS = Nothing
If Not oWB Is Nothing Then oWB.Close
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 may seem like a cruel joke, 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 could 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 more on this, see post #6 on Finding & Repairing Unqualified References (http://www.xtremevbtalk.com/showthread.php?p=900556#post900556) below.

For further reading on avoiding the use of the 'Application' _Global:
(1) Excel Automation Fails Second Time Code Runs (MSKB 178510) (http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/q178/5/10.asp)
(2) Error or Unexpected Behavior with Office Automation When You Use Early Binding in Visual Basic (MSKB 319832) (http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q319832&)


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)

oWB.SaveAs("Hello World.xls") ' <-- Results saved here. Note that the above carefully avoids any reference to 'Application', either implied or otherwise. All references trace back to 'oExcel', not to 'Application'.


Putting It All Together

Replacing the "Your Code Goes Here" section with the "Hello World" code above, should now give us a combined routine that looks as follows: 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 ***

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)

oWB.SaveAs("Hello World.xls") ' <-- Results saved here.

Cleanup:
Set oWS = Nothing
If Not oWB Is Nothing Then oWB.Close
Set oWB = Nothing
oExcel.Quit
Set oExcel = Nothing
End Sub

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

Best of luck out there...

:),
Mike

Further Info:
For an advanced example of using Automation to turn Excel and Word into a reporting tool, see Garrett Sever's superb coding sample in the Code Library: Using Excel and Word for Reports (http://www.xtremevbtalk.com/showthread.php?t=16520).

Special Thanks
To the Excel Experts: herilane, mark007, tinyjack, Kluz, Italkid and Insomniac, and the rest of the ExVBF team for their questions, comments and guidance... Thanks guys. :)

Early vs. Late Binding

The code examples in Mike's post all make use of what is known as "early binding". Early binding generally involves two steps:
adding a reference to the Microsoft Excel Object Library in your VB6 app
using code like the following to declare and create your Excel objects:
Dim oExcel As Excel.Application
Dim oWorkbook As Excel.Workbook
Set oExcel = New Excel.Application
Set oWorkbook = oExcel.Workbooks.Add
This is called "early binding" because the compiler knows up front what kind of object oExcel will be (namely, an Excel application object).
The alternative to early binding is called "late binding". If you use late binding:
do not create a reference to the Excel object library
use code like the following to declare and create the Excel.Application reference:
Dim oExcel As Object
Dim oWorkbook As Object
Set oExcel = CreateObject("Excel.Application")
Set oWorkbook = oExcel.Workbooks.Add
This is called "late binding" because VB will only find out what kind of object oExcel will be when you create it.
If you use late binding, VB won't be able to draw on the Excel object library. That will require you to make some changes to your code, compared to early binding. More on that later.

Early vs. Late Binding Continued: More about CreateObject and GetObject

Note that the following is an example of early binding, despite the use of CreateObject:
Dim oExcel As Excel.Application
Set oExcel = CreateObject("Excel.Application")
A close relative to CreateObject() is the GetObject() function. It can be used to attain a reference to a currently running instance of Excel, or to create a new one, or even to open an Excel workbook directly. GetObject can be used with both early and late binding. Here's an example with early binding. The following snippet will grab the existing Excel instance if Excel is already running; otherwise it will start up a new instance:
On Error Resume Next
Set oExcel = GetObject(, "Excel.Application")
On Error GoTo 0
If oExcel Is Nothing Then
'Excel wasn't open - open a new one
Set oExcel = GetObject("", "Excel.Application")
End If

Early vs. Late Binding Continued: So Which One Is Better?

Let me say this straightaway: I would use early binding whenever possible. And it is possible in the majority of cases.
The advantages of early binding include:
Your app will run faster
It is easier to write the code, because you can make use of IntelliSense/AutoComplete, as well as the Object browser
You'll get fewer runtime errors, since syntax errors will be picked up at compile time
You can use Excel's built-in constants, like xlCellTypeVisible, xlContinuous, xlNone etc. With late binding, you would have to use the equivalent integer values instead, or declare the constants yourself (more on that later).
The main argument you'll generally hear for late binding is that your app is then more likely to run with all versions of the application you're automating, whereas with early binding you essentially tie yourself to a single version. And if the user doesn't have that version installed on their PC, your app would fail.

However, that argument doesn't really hold for Excel. All the newer versions of Excel include the lower-version object libraries. So in that case I'd still use early binding, and use the lowest available version of the Excel object library - and your code will work with that version as well as all newer versions of Excel. ("Binding low and running high")

Note that this is not true for all Office apps - for Access, for example, MS says that "During development of Access 2000, the Access object model was accidentally modified in a way that breaks both binary (v-table) and dispid compatibility with Access 97" - so late binding would be recommended for automating Access.

If you do not have older versions of Excel available, I would strongly recommend late binding.
Some sources recommend early binding with a later version and hoping that it will work with older versions as well ("binding high and running low"). It may work, but it sometimes also fails completely, so it's generally not advisable for professional solutions, in my opinion.

Late binding would also be useful if you're not sure whether the user has any version of Excel installed at all. With late binding, that'll cause a trappable runtime error, whereas with early binding, your code wouldn't run at all.
You would also use late binding if you're writing a VBScript rather than a VB6 app.

Early vs. Late Binding Concluded: Converting code from early binding to late binding

If you're going to use late binding, I would recommend you to use early binding while developing your code, and when you're finished, convert it to late binding. This way you get all the advantages of early binding during coding (AutoComplete, the object browser etc).

I've already briefly mentioned the changes needed, but here they are again:
Remove the reference to the Excel object library
Change the declaration of all Excel objects to the generic Object type
Use CreateObject to create the Excel object, rather than Set and New
(Actually this one should be done first and not last, but it takes longer to explain...) Replace Excel's built-in constants with integer equivalents, or declare them yourself - I prefer the latter, because it makes your code much easier to read. Example:
'early binding:
With xlWB.Charts.Add
.SetSourceData Source:=xlWB.Worksheets("Sheet1").Range("A1:A10")
.ChartType = xlLine
.Location Where:=xlLocationAsObject, Name:="Sheet1"
End With

'late binding:
Const xlLine As Integer = 4
Const xlLocationAsObject As Integer = 2
With xlWB.Charts.Add
.SetSourceData Source:=xlWB.Worksheets("Sheet1").Range("A1:A10")
.ChartType = xlLine
.Location Where:=xlLocationAsObject, Name:="Sheet1"
End With
You can find the integer equivalents through the object browser - just search for the constant name. Another easy way to find the integer values of Excel's constants is to write your app using early binding, and then in break mode use either the Immediate Window to print the values of the constants - or simply hover your mouse over the constant in your code. Note that you need to do this before you remove your reference to the Excel object library.

Finding and Repairing Unqualified References

Within the Automating Excel from VB 6.0 Tutorial, the section 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 unqualified 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 behavior
' 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 XVBT 2004 1202

' ----------------------------------------------------------------------
' 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
Sub Windows()
End Sub
Sub Workbooks()
End Sub
Sub WorksheetFunction()
End Sub
Sub Worksheets()
End Sub

' ----------------------------------------------------------------------
' Method Globals
' ......................................................................

Sub Calculate(RequiredArg)
' Note: The 'Required Arg' is utilized so that the compiler would pick up
' any accidental call to Excel's Calculate(), which does not take
' an argument.
End Sub
Sub DDEExecute()
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 still have any problems after making these corrections, then feel free to post a question on the Excel Forum (http://www.xtremevbtalk.com/f78) and we'll be glad to help you out. :)

Good luck!
Mike

Sometimes, you do everything right, but Excel still won't quit.

Don't give up too soon, for most often there is a reason that can be spotted in your code. If you need help finding what may be causing Excel to hang, post your question in the Excel forum (http://www.xtremevbtalk.com/forumdisplay.php?f=78) (or in the Office Automation forum (http://www.xtremevbtalk.com/forumdisplay.php?f=14), if you're working with a different Office application) and we'll be glad help you out...

But if all conventional methods have failed, sometimes there is no other way than to kill the process when you're done. The problem with this idea, however, is that although we can enumerate all the currently running Excel processes, if there is more than one running, how do we know which one is ours? Killing them all is a terrible solution, as this could be crashing versions that the user is currently working on.

The key to knowing "which one" is making note of the process handle when your application is first created. Then when you are ready to quit, you can make use of this handle to kill the correct process.

When you create your application, get a list of currently running Excel processes. This is easily done using WMI.execquery:
Set WMI = GetObject("winmgmts:")
strQuery = "SELECT * FROM win32_process WHERE Name = 'EXCEL.EXE'"
Set objProcesses = WMI.execquery(strQuery)
Getting the process handles is simply a matter of looping through the results and getting the Handle of each one:
For Each objProcess In objProcesses
strHandles = strHandles & " " & objProcess.Handle
NextAfter you have created the Excel object, run the query again to get the new list of Excel processes. Compare the two lists to find the handle of the new process.

When you're done and ready to quit, terminate the process using a similar approach. Instead of looking for processes with a specific name (like above), we look for processes with a given handle:
strQuery = "SELECT * FROM win32_Process WHERE Handle = " & HandleFinally, to terminate the found process, use objProcess.Terminate.

The full code is a bit longish, so I'm attaching it as a text file instead of posting it all here.

The .Net equivalent of this code, by the way, is much shorter and simpler, and can be seen in the .Net Office Automation tutorial (http://www.xtremevbtalk.com/showpost.php?p=956122&postcount=4).

Finally, if WMI is not enabled on the computer in questions, this code will naturally not work. If you are unsure about the environment where the code will run, add error handling to check for that. (Error handling is of course a good thing in other situations as well.)

As an update to the Finding and Repairing Unqualified References post shown above, another approach to finding and repairing your unqualified references is to temporarily remove the reference to your object model and switch over to late binding. Once you are done you can then switch back to early binding.

This approach is shown by Tushar Mehta, here: http://www.tushar-mehta.com/excel/vba/xl_doesnt_quit/

It's a bit involved, so it should not be the first approach to try, but switching over to late binding would pick up where the Excel library is used to prefix a global such as the Excel.Range("A1") example shown above, which the Automation Prophylactics module would not be able to detect.

It's a rare error, but when you have a hard-to-find issue like this, switching over to late binding can sometimes be the only way to find it.

-- Mike