How to refer to Excel objects in Access VBA? How to refer to Excel objects in Access VBA? vba vba

How to refer to Excel objects in Access VBA?


I dissent from both the answers. Don't create a reference at all, but use late binding:

  Dim objExcelApp As Object  Dim wb As Object  Sub Initialize()    Set objExcelApp = CreateObject("Excel.Application")  End Sub  Sub ProcessDataWorkbook()     Set wb = objExcelApp.Workbooks.Open("path to my workbook")     Dim ws As Object     Set ws = wb.Sheets(1)     ws.Cells(1, 1).Value = "Hello"     ws.Cells(1, 2).Value = "World"     'Close the workbook     wb.Close     Set wb = Nothing  End Sub

You will note that the only difference in the code above is that the variables are all declared as objects and you instantiate the Excel instance with CreateObject().

This code will run no matter what version of Excel is installed, while using a reference can easily cause your code to break if there's a different version of Excel installed, or if it's installed in a different location.

Also, the error handling could be added to the code above so that if the initial instantiation of the Excel instance fails (say, because Excel is not installed or not properly registered), your code can continue. With a reference set, your whole Access application will fail if Excel is not installed.


First you need to set a reference (Menu: Tools->References) to the Microsoft Excel Object Library then you can access all Excel Objects.

After you added the Reference you have full access to all Excel Objects. You need to add Excel in front of everything for example:

Dim xlApp as Excel.Application

Let's say you added an Excel Workbook Object in your Form and named it xLObject.

Here is how you Access a Sheet of this Object and change a Range

Dim sheet As Excel.WorksheetSet sheet = xlObject.Object.Sheets(1)sheet.Range("A1") = "Hello World"

(I copied the above from my answer to this question)

Another way to use Excel in Access is to start Excel through a Access Module (the way shahkalpesh described it in his answer)


Inside a module

Option Explicitdim objExcelApp as Excel.Applicationdim wb as Excel.Workbooksub Initialize()   set objExcelApp = new Excel.Applicationend subsub ProcessDataWorkbook()    dim ws as Worksheet    set wb = objExcelApp.Workbooks.Open("path to my workbook")    set ws = wb.Sheets(1)    ws.Cells(1,1).Value = "Hello"    ws.Cells(1,2).Value = "World"    'Close the workbook    wb.Close    set wb = Nothingend subsub Release()   set objExcelApp = Nothingend sub