Go Back  Xtreme Visual Basic Talk > Legacy Visual Basic (VB 4/5/6) > Knowledge Base > Tutors' Corner > Automating Excel from VB 6.0


Reply
 
Thread Tools Display Modes
  #1  
Old 01-15-2004, 10:37 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default 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:
Code:
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:
Code:
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:
Code:
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:
Code:
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 below.

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:
Code:
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:
Code:
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.

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.
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb

Last edited by loquin; 06-27-2008 at 09:01 AM. Reason: Updated per author request
Reply With Quote
  #2  
Old 01-31-2004, 10:56 AM
herilane's Avatar
herilane herilane is offline
Unashamed geek

Retired Moderator
* Expert *
 
Join Date: Jul 2003
Location: London, England
Posts: 8,988
Default Advanced topic: Early vs late binding

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:
  1. adding a reference to the Microsoft Excel Object Library in your VB6 app
  2. using code like the following to declare and create your Excel objects:
Code:
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:
  1. do not create a reference to the Excel object library
  2. use code like the following to declare and create the Excel.Application reference:
Code:
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.

Last edited by herilane; 01-31-2004 at 11:27 AM.
Reply With Quote
  #3  
Old 01-31-2004, 10:57 AM
herilane's Avatar
herilane herilane is offline
Unashamed geek

Retired Moderator
* Expert *
 
Join Date: Jul 2003
Location: London, England
Posts: 8,988
Default

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:
Code:
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:
Code:
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
Reply With Quote
  #4  
Old 01-31-2004, 10:59 AM
herilane's Avatar
herilane herilane is offline
Unashamed geek

Retired Moderator
* Expert *
 
Join Date: Jul 2003
Location: London, England
Posts: 8,988
Default

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.

Last edited by herilane; 12-09-2004 at 11:10 AM. Reason: Added brief discussion of "binding high and running low"
Reply With Quote
  #5  
Old 01-31-2004, 11:02 AM
herilane's Avatar
herilane herilane is offline
Unashamed geek

Retired Moderator
* Expert *
 
Join Date: Jul 2003
Location: London, England
Posts: 8,988
Default

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:
Code:
'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.
Reply With Quote
  #6  
Old 12-04-2004, 09:44 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

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:
Code:
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:
Code:
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:
Code:
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:
Code:
Set Rng = Range("A1:C3")
you would need to change it to:
Code:
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:
Code:
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:
Code:
Set Rng = oExcel.Range("A1")
If there is a complex string to be evaluated withn the brackets, such as
Code:
Result = [A1+5]
then one would instead want to use oExcel.Evaluate(), such as:
Code:
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:
Code:
Dim oExcel As Excel.Application
or:
Code:
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:
Code:
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:
Code:
Set Rng = Range("A1")
which you now know creates problems when using Automation. The fix is the same as always:
Code:
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 and we'll be glad to help you out.

Good luck!
Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb

Last edited by OnErr0r; 10-16-2005 at 12:56 PM. Reason: Requested.
Reply With Quote
  #7  
Old 03-09-2005, 11:16 AM
herilane's Avatar
herilane herilane is offline
Unashamed geek

Retired Moderator
* Expert *
 
Join Date: Jul 2003
Location: London, England
Posts: 8,988
Default

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 (or in the Office Automation forum, 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:
Code:
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:
Code:
For Each objProcess In objProcesses strHandles = strHandles & " " & objProcess.Handle Next
After 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:
Code:
strQuery = "SELECT * FROM win32_Process WHERE Handle = " & Handle
Finally, 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.

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.)
Attached Files
File Type: txt WMI kill Excel.txt (2.7 KB, 575 views)

Last edited by herilane; 03-28-2005 at 09:29 AM.
Reply With Quote
  #8  
Old 04-10-2009, 07:25 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

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
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Automating Excel in a VB app PaulMill Excel 2 08-06-2003 08:44 AM
copy Excel row to VB app fizbim Excel 3 05-27-2003 10:19 AM
Automating excel reports with VB max1mum0v3rdr1v Word, PowerPoint, Outlook, and Other Office Products 9 10-23-2002 07:43 AM
VB link to Excel and back to VB ELV Word, PowerPoint, Outlook, and Other Office Products 36 08-22-2002 05:11 PM
QB to VB Conversion - By AIO BillSoo Tutors' Corner 0 08-06-2002 11:37 AM

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
 
 
-->