VBA, Excel Automation From Other Applications

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

  1. Early binding
  2. Late binding

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


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:

Word VBA, Early Binding, Tools Reference
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:

Excel, Automation, Object Library
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, Empty Application


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:

Excel, Empty Application


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:

Excel Constants
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

See also:

If you need assistance with your code, or you are looking for a VBA programmer to hire feel free to contact me. Also please visit my website www.software-solutions-online.com

Leave a Reply

Your email address will not be published. Required fields are marked *

privacy policy