How to refer to Excel objects in Access VBA?

Question

What declarations I have to make in order to be able to use Excel objects and constants from my Access 2007 VBA script?

Dim wb As Workbook

or

Set objExcelApp = New Excel.Application

or

.Borders(xlEdgeBottom)

Akmolinsk

Answer 1

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.Worksheet
Set 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)

Fyrd

Answer 2

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.

Tower

I completely agree with this, it is however useful to early bind when initially developing code so as to get the benefits of intellisense, then to change over to late binding once it all works. – Sealy

and you'll have to think about declaring a few constants you may want to use – Ether

I was a late binding guy for years, but i have switched to early binding if the .accdb file is on a server and the only way to access it is from that server. I understand where you are coming from though. I always late bind if the file is traveling from computer to computer. – Intersect

Answer 3

Inside a module

Option Explicit
dim objExcelApp as Excel.Application
dim wb as Excel.Workbook

sub Initialize()
   set objExcelApp = new Excel.Application
end sub

sub 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 = Nothing
end sub

sub Release()
   set objExcelApp = Nothing
end sub

Effectually

Maybe a problem with references?! – Frohne

Add a reference to "Microsoft Excel... Object Library" using Tools menu -> References. – Effectually