Tutorial/FAQ: How to Automate Excel from VB6 (or VB5/VBA)
Do you want to create or read an Excel file from your program?
Perhaps you want to create a report with some data, a few formulas, and some graphs, or maybe get some particular data out of a spreadsheet?
The good news is that Excel has been designed to let you do all of this and more; you can treat it like other objects that you would use from VB, and you can perform programmatically all of the tasks that you can manually. In the rest of this tutorial you will find out how.
Please note that you must have Excel installed to do any of this, and so must your users!
Note that as an alternative to automation you can use an Excel file as a database, which does not require Excel to be installed - however it is not as easy to use as other database systems, due to unusual SQL syntax (eg: "SELECT * from `Sheet1$A1:`"). This method is not discussed in this document, please search the Database Development forum for previously asked questions which cover this.
Contents
2 - In the beginning - Adding a Reference to Excel
3 - Opening Excel
4 - Creating/opening a Workbook
5 - Preparing to work with data (setting and closing App/Book/Sheet references)
6 - Reading and writing data
7 - Basic formatting
8 - Finding "special" cells (bottom row etc)
9 - Installation Issues
10 - Using a Late-Bound connection to Excel
11 - "Excel doesn't close"
12 - How do I write code to … ? [a.k.a. recording macro's]
13 - Useful functions and downloads
14 - Frequently Asked Questions
.
In the beginning - Adding a Reference to Excel
To start your journey into the world of automating Excel, you first need to tell your program that you want to reference Excel, to do this:
Go to the "Project" menu, and select "References". You will be presented with a long list of available references, just scroll down to "Microsoft Excel X.X Object Library" (where X.X is a version number - see post #14 for a list), then tick it and press OK.
NB: if it isn't in the list, there is probably an error with your installation of Excel. You can try to select the reference file manually by clicking the "browse" button, the file you need it is likely to be called something like "ExcelX.olb" (for Excel XP or later it seems to be "Excel.exe" instead).
As an aside, if you are releasing your software to various users then having a reference isn't the method you should use, but don't worry about it for now (this is the method you should use whilst developing your software, as it provides auto-complete features and Help while you are writing your code).
.
Creating/opening a Workbook
The example in the previous section was very basic, and to be honest pretty pointless too. In order to actually do anything you need to have a Workbook to interact with.
There are times when you want to create a new Workbook, and times when you want to work with an existing one.
To create a new Workbook:
VB Code:
Dim oXLApp as Excel.Application 'Declare the object variables
Dim oXLBook as Excel.Workbook
Set oXLApp = New Excel.Application 'Create a new instance of Excel
Set oXLBook = oXLApp.Workbooks.Add 'Add a new workbook
oXLApp.Visible = True 'Show it to the user
Set oXLBook = Nothing 'Disconnect from Excel (let the user take over)
Set oXLApp = Nothing
To open an existing workbook, just change the " Set oXLBook =" line in the above example to this:
VB Code:
Set oXLBook = oXLApp.Workbooks.Open("c:\my folder\my workbook.xls") 'Open an existing workbook
By default a new Workbook has the number of sheets which are specified in the users options. You can set this option via code, but should change it back afterwards (else the user will have the option that you set). To do this you need a few extra lines around the "'Add a new workbook" line:
VB Code:
…
Set oXLApp = New Excel.Application 'Create a new instance of Excel
Dim iSheetsPerBook as Integer 'Add a new workbook (with one sheet)
iSheetsPerBook = oXLApp.SheetsInNewWorkbook
oXLApp.SheetsInNewWorkbook = 1
Set oXLBook = oXLApp.Workbooks.Add
oXLApp.SheetsInNewWorkbook = iSheetsPerBook
oXLApp.Visible = True 'Show it to the user
…
.
Preparing to work with data (setting and closing App/Book/Sheet references)
So far, we have got as far as having a Workbook open within the Excel application, which is getting better but doesn't let us do what we want - interact with the data!
For those of you who have experience in this area, you will hopefully know already that we are missing a vital ingredient in our examples so far - the Worksheet. All data in a workbook is held in specific worksheets, so we should be using them.
Now it is time to start with the proper examples. There are a few different ways of modifying/reading data, but all of them start with the same basic block of code, which we will call "Part A":
VB Code:
Dim oXLApp as Excel.Application 'Declare the object variables
Dim oXLBook as Excel.Workbook
Dim oXLSheet as Excel.Worksheet
Set oXLApp = New Excel.Application 'Create a new instance of Excel
Set oXLBook = oXLApp.Workbooks.Add 'Add a new workbook
Set oXLSheet = oXLBook.Worksheets(1) 'Work with the first worksheet
Note that you could open an existing workbook instead of creating a new one, by using the code in the previous post.
This should be followed by some sort of modification/reading code, which we will come to in the next section - the important part for now is to finish properly, because if you don't then you won't see any of the changes you have made!
When you have finished working with the data you can show, save, save as, or just close the workbook. There are different methods for each of these options, all of which we will call "Part B", which one of these you use is up to you (and will probably vary depending on the project you are writing at the time).
Part B(1) - Show the workbook: (best while you are writing or testing!)
VB Code:
oXLApp.Visible = True 'Show it to the user
Set oXLSheet = Nothing 'Disconnect from all Excel objects (let the user take over)
Set oXLBook = Nothing
Set oXLApp = Nothing
or, Part B(2) - Save the existing workbook you opened:
VB Code:
Set oXLSheet = Nothing 'disconnect from the Worksheet
oXLBook.Close SaveChanges:= True 'Save (and disconnect from) the Workbook
Set oXLBook = Nothing
oXLApp.Quit 'Close (and disconnect from) Excel
Set oXLApp = Nothing
or, Part B(3) - Save the workbook to a new file:
VB Code:
Set oXLSheet = Nothing 'disconnect from the Worksheet
oXLBook.SaveAs "C:\My Documents\My File.xls" 'Save (and disconnect from) the Workbook
oXLBook.Close SaveChanges:= False
Set oXLBook = Nothing
oXLApp.Quit 'Close (and disconnect from) Excel
Set oXLApp = Nothing
or, Part B(4) - Close the workbook (but don't save):
VB Code:
Set oXLSheet = Nothing 'disconnect from the Worksheet
oXLBook.Close SaveChanges:= False 'Close (and disconnect from) the Workbook
Set oXLBook = Nothing
oXLApp.Quit 'Close (and disconnect from) Excel
Set oXLApp = Nothing
Once you have these two chunks of code you are ready to start the fun.
.
Finding "special" cells (bottom row etc)
As you may already know, when you are editing a spreadsheet manually you can press Ctrl and an arrow key to go to the next/last cell in that direction which contains text. There are also methods of performing this via code.
Here is one method to find the last used row/column in the sheet:
VB Code:
LastRow = oXLSheet.UsedRange.Rows.Count
LastCol = oXLSheet.UsedRange.Columns.Count
NB: UsedRange is a special pre-defined range which contains the entire used area of the sheet.
Here is an alternative - but note that this method assumes that every row has data in all columns (and vice versa). If this is not the case, it is safer to use the options above/below instead.
VB Code:
LastRow = oXLSheet.Range("A1").End(xlDown).Row
LastCol = oXLSheet.Range("A1").End(xlToRight).Column
There is also another built-in function in Excel for detecting used ranges, the .SpecialCells function retrieves a number of "special range" values depending on the constant value passed to the function. Here is the equivalent to the code above (works by going to the 'last' used cell):
VB Code:
LastRow = oXLSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
LastCol = oXLSheet.Cells.SpecialCells(xlCellTypeLastCell).Column
Some of the other parameters for the SpecialCells method are:
- xlCellTypeAllValidation - Cells having validation criteria
- xlCellTypeBlanks - Empty cells
- xlCellTypeComments - Cells containing notes
- xlCellTypeFormulas - Cells containing formulas
The full list can be seen in the Help or the Object Browser.
.
Using a Late-Bound connection to Excel
The code so far in this tutorial has been Early-Bound (i.e.: VB knows in advance what Excel functions are available to you), which as seen in the previous section can cause serious issues.
Unless you can guarantee that all of your users have the same version of Excel installed as you do, I would strongly recommend converting to Late-bound for software that you release "into the wild".
The down-side to using this is that you no longer get some of the nice features of the VB IDE (like the drop-down lists of properties and methods that appear when you type oXLApp. ), so it may be a good idea to convert to this method once your code is finished.
There are four steps to convert your code:
1) Replace Excel data types with Object.
If you have:
VB Code:
Dim oXLApp as Excel.Application
Dim oXLBook as Excel.Workbook
Dim oXLSheet as Excel.Worksheet
...
You need to change it to:
VB Code:
Dim oXLApp as Object
Dim oXLBook as Object
Dim oXLSheet as Object
...
Note that you should also do this with any other variables that you have declared as Excel.something
2) Change the initialisation of the application object.
If you have:
VB Code:
Set oXLApp = New Excel.Application
You should replace it with:
VB Code:
Set oXLApp = CreateObject("Excel.Application")
Note: if Excel isn’t installed, this line of code will cause an error – you should deal with this by using error handling in an appropriate way for your project.
3) Define the constants that you have used.
Excel constants (e.g.: xlLeft) are defined in the object library; however you will no longer have a link to this, so they wont be defined any more!
There are two main options here; the first is to use the Object Browser to find the values/declarations for each one you have used (very slow, and prone to errors!), and the other (much simpler) is to add a pre-made module to your project which declares them all for you. You can find a link to one which Microsoft produced in the “useful functions and downloads” section (post #13).
4) Remove "Excel object Library" from the list in "Project" -> "References"
Your code is now late-bound, and should work with all versions of Excel that support automation (as long as you haven’t used special functionality which wasn’t available in earlier versions).
.
How do I write code to … ? [a.k.a. recording macro's]
There are many things which can be done with Excel that haven't been listed here, and there is a good reason - Excel will tell you the code you need!
If you record a macro in Excel, it is 'written' in VBA (a subset of VB), and this code can be copied almost directly into your VB program, with only a few modifications to use the objects you have created rather than the default Application/Sheet/etc objects [failure to do this part will most likely cause your program to have bugs, or even cause your computer to crash].
To record a macro:
- Go to "Tools" -> "Macro" -> "Record new macro".
- Manually do the things that you want your program to do.
- Press the "stop recording" button (a blue square).
- Go to "Tools" -> "Macro" -> "Macros...", which will show a list of macros.
- Select the macro you recorded, and click on "Edit" to see the code.
There are 4 steps you should perform when taking code from a macro to use in your program, they are:
- Remove un-needed code (as Excel often does extra things, such as writing all values from an options screen).
- Change ActiveDocument, Application etc to suit the variables in your code.
- Change Selection to oXLApp.Selection.
- Prefix all instances of Range/Cells/Rows/Columns/etc with your sheet object.
Example 1:
The following code is created if you select a range, then set it to Bold and Italic:
VB Code:
Range("C9:F16").Select
Selection.Font.Bold = True
Selection.Font.Italic = True
In this case there is no un-needed code, so we can skip step 1.
There is no use of ActiveDocument etc, so we can also skip step 2.
Following step 3 gives us this:
VB Code:
Range("C9:F16").Select
oXLApp.Selection.Font.Bold = True
oXLApp.Selection.Font.Italic = True
And step 4 gives us this:
VB Code:
oXLSheet.Range("C9:F16").Select
oXLApp.Selection.Font.Bold = True
oXLApp.Selection.Font.Italic = True
As mentioned in the previous post, it is best to avoid Selection altogether. This can be done by changing the code to this:
VB Code:
oXLSheet.Range("C9:F16").Font.Bold = True
oXLSheet.Range("C9:F16").Font.Italic = True
..or you can do it like this instead (a bit more efficient, and easier to read):
VB Code:
With oXLSheet.Range("C9:F16")
.Font.Bold = True
.Font.Italic = True
End With
Example 2:
As another example, here is part of the code created if you go into "File"->"Page Setup", then set the orientation to Landscape, and set the "rows to repeat at top" to $1:$2
VB Code:
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$2"
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
... lots of lines removed! ...
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
End With
Following step 1 gives us this:
VB Code:
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$2"
End With
With ActiveSheet.PageSetup
.Orientation = xlLandscape
End With
Which can be shortened to this:
VB Code:
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$2"
.Orientation = xlLandscape
End With
From there, following step 2 gives us this:
VB Code:
With oXLSheet.PageSetup
.PrintTitleRows = "$1:$2"
.Orientation = xlLandscape
End With
In this case, there is nothing to do for steps 3 and 4.
.
Useful functions and downloads
Excel constants module
This is a pre-written module which contains all of the Excel constants (e.g.: xlLeft), to enable you to keep the constant names in your code when you are using Late-binding. Just add the module to your project! (NB: this download also contains similar modules for the other Office products).
This is provided by Microsoft, and can be downloaded here: http://support.microsoft.com/kb/112671 (alternative)
Note that the above file is designed for Excel 97, so it does not contain constants which have been added more recently (it does however contain almost everything you need). You can see a list of constants in the Excel 2003 constants page, which contains those for Excel 97 plus newer ones.
xl_col and xlColName
Functions for converting column 'name' to/from column number.
Simply add these functions to your code (in a module if you want to use them from multiple forms).
VB Code:
Function xl_Col(ByRef Col_No) As String
'returns Excel column name from numeric position (e.g.: col_no 27 returns "AA")
'by Si_the_geek (VBForums.com)
'Only allow valid columns
If Col_No < 1 Or Col_No > 256 Then Exit Function
If Col_No < 27 Then 'Single letter
xl_Col = Chr(Col_No + 64)
Else 'Two letters
xl_Col = Chr(Int((Col_No - 1) / 26) + 64) & _
Chr(((Col_No - 1) Mod 26) + 1 + 64)
End If
End Function
'example usage:
sColName = xl_Col(7)
VB Code:
Function xl_ColNo(Col_Name) As Integer
'returns an Excel column number from its name (e.g.: col_name "AA" returns 27)
'by Si_the_geek (VBForums.com)
Col_Name = UCase(Trim(Col_Name))
Select Case Len(Col_Name)
Case 1: xl_ColNo = Asc(Col_Name) - 64
Case 2: xl_ColNo = ((Asc(Left(Col_Name, 1)) - 64) * 26) _
+ (Asc(Right(Col_Name, 1)) - 64)
End Select
End Function
'example usage:
iColNo = xl_ColNo("Z")
.
Frequently Asked Questions
Does the user need Excel installed?
Yes (how can you automate something that isn't there?), and you cannot install it without them having a valid licence for it.
What library version number should I use?
To be honest any which you have installed will normally be fine, however if you want to use "new" functionality that has been added in newer versions of Excel, you need to select the one which provides that functionalility.
Since the introduction of Excel 95, the versions haven't been obvious (until then there were "proper" version numbers like 5.0). The reference that you use in your program reflects the internal version number, rather than the displayed number.
The version numbers are:- 7.0 - Excel 95
- 8.0 - Excel 97
- 9.0 - Excel 2000
- 10.0 - Excel XP
- 11.0 - Excel 2003
How do I work with a copy of Excel that is already open?
This will connect the application object to an open copy of Excel:
VB Code:
Set oXLApp = GetObject(,"Excel.Application")
Please note that there is no way to specify which open copy of Excel to work with, so if there is more than one open you cannot predict which one will be referenced.
Note also that if there are no instances of Excel running, this line of code will cause an error.
How do I call an Excel Macro from VB?
See this example in the Microsoft Knowledge base:
http://support.microsoft.com/default...b;en-us;194611
Do I really need a specific WorkBook/Worksheet/etc object?
Yes you do. When you are testing it might not be a problem, but when your program is being used you may notice strange errors coming up, or your program failing, or (even worse) modifying the wrong documents.
Why would this happen? Excel is capable of having multiple documents open, and can also be manipulated by the user. If you don't reference specific WorkBooks/Worksheets/etc then you are at the mercy of which one Excel thinks you want - it usually assumes the one that is currently active. If a user opens/creates a workbook then their workbook/sheet becomes active, rather than the one that you intended to work with.
How do I detect events that happen in a visible workbook?
See this thread: http://www.vbforums.com/showthread.php?t=305203
.