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)
Thanks
This question is related to
vba
ms-access-2007
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)
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
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.
Source: Stackoverflow.com