VBA, Excel Automation From Other Applications

Aug 31, 2015 by azurous in Automation

In this article I will explain how you can automate an Excel workbook from another application. There are basically two methods for automation:

Early binding
Late binding

I have explained the difference between these two methods when I was explaining about word automation:

VBA, Automating Word From Excel

–

Contents

Excel Automation, Early Binding:

Similar to what was explained in the article VBA, Automating Word From Excel, in order to automate excel using early binding, you must first add reference to the Excel object library. This can be done through the Tools>>Reference drop down menu:

Look for the Excel object library. As explained in the article VBA, Automating Word From Excel, the version installed on my system might be different than the one on yours. I have the Microsoft Excel 14 Object Library installed, there might be a different version installed on your system:

The code below will create an Excel application and open an empty workbook:

Option Explicit Sub main() Dim objExcel As Excel.Application Dim objWorkbook As Excel.Workbook 'create new application Set objExcel = New Excel.Application 'make it visible objExcel.Visible = True 'add a empty workbook Set objWorkbook = objExcel.Workbooks.Add End Sub

Result:

–

Excel Automation, Late Binding:

As explained in the article VBA, Automating Word From Excel in late binding there is no need to add reference to the applications object library before automation. The code below automates an Excel application with an empty workbook:

Sub main2() Dim objExcel As Object Dim objWorkbook As Object 'create new application Set objExcel = CreateObject("Excel.Application") 'make it visible objExcel.Visible = True 'add a empty workbook Set objWorkbook = objExcel.Workbooks.Add End Sub

Result:

–

Referencing Sheets:

Normally when you wanted to reference a sheet, you could use something like this:

sheet2.cells(1, 1) = ... sheets(2).cells(2, 2) = ... sheets("sheet2").cells(2, 2) = ...

None of the expressions above are valid when you are using automation. With automation you can only use statements like the ones below:

objWorkbook.sheets(2).cells(2, 2) = ... objWorkbook.sheets("sheet2").cells(2, 2) = ...

Where objWorkbook is a reference to the automated workbook object.

–

Referencing Cells:

Cells can be referenced using statements like the ones below:

objWorkbook.sheets(2).cells(2, 2) = ... objWorkbook.sheets("sheet2").cells(2, 2) = ...

–

Referencing Ranges:

Ranges can be referenced using statements like the ones below:

objWorkbook.Sheets(2).Range("A1:B2") = ... objWorkbook.Sheets("sheet2").Range("A1:B2") = ... objWorkbook.Sheets(2).Range( _ objWorkbook.Sheets(2).Cells(1, 1), _ objWorkbook.Sheets(2).Cells(2, 2)) = ... objWorkbook.Sheets("sheet2").Range( _ objWorkbook.Sheets("sheet2").Cells(1, 1), _ objWorkbook.Sheets("sheet2").Cells(2, 2)) = ...

–

Excel Constants:

There are series of constants in Excel:

xlThin xlThick XlThemeColor.xlThemeColorAccent1 xlDouble xlDashDotDot

Early binding: If you are using early binding you can refer to them as shown below:

Excel.xlThin Excel.xlThick Excel.XlThemeColor.xlThemeColorAccent1 Excel.xlDouble Excel.xlDashDotDot

Late Binding: When using late binding the only method to use these constants is to replace them by their value. This can be achieved in several ways. One method would be using a code similar to the one below in the Excel VBA editor:

MsgBox (xlThin) MsgBox (xlThick) MsgBox (XlThemeColor.xlThemeColorAccent1) MsgBox (xlDouble) MsgBox (xlDashDotDot)

A message box will popup with displaying the value of each of these constants. For xlThin the value is:

So for example instead of using the line below that would change the border weight to thin:

Range("A1").Borders(xlTop).Weight = xlThin

We would have to use something like this:

objWorkbook.Range("A1").Borders(-4160).Weight = 2

Active Cell, Active Sheet, Selection, Active Chart, …:

In order to reference these we would need to reference them through the excel application:

objExcel.ActiveCell objExcel.ActiveChart objExcel.ActiveSheet objExcel.Selection