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    Thanks

User · 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

User · Answer

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

User · Answer

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

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

Examples related to vba

Examples related to ms-access-2007