How to refer to Excel objects in Access VBA?

by

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.

More Related Contents:

  1. ms access vba code for update records through form
  2. Is it better to use ADO or DAO in Access 2007?
  3. How to loop through all controls in a form, including controls in a subform – Access 2007
  4. SendKeys is messing with my NumLock key via VBA code in Access form
  5. Get all dates between 2 dates in vba
  6. Split Words in the comment text
  7. vba: get unique values from array
  8. Can VBA Reach Across Instances of Excel?
  9. Efficient way to delete entire row if cell doesn’t contain ‘@’ [duplicate]
  10. Too few parameters Expected 1, recordset issue
  11. Deleting a file in VBA
  12. Building Undo Into an Excel VBA Macro
  13. Best workaround for VBA Transpose array length limit?
  14. I use OR to form a multiple condition IF ELSE statement on VBA, it’s not working
  15. VBA- variable in a range, inside a formula
  16. How to Lock the data in a cell in excel using vba
  17. Check for the senderEmailAddress
  18. Removing a string that includes CRLF characters from body of e-mail
  19. Workbooks.Open Method in VBA
  20. Windows().Activate works on every computer except one
  21. How to highlight a cell using the hex color value within the cell?
  22. Difference between Thisworkbook.name and Activeworkbook.name in VBA
  23. Set a cell value from a function
  24. What are the differences between VBA 6.0 and VBA 7.0?
  25. How to name an object within a PowerPoint slide?
  26. How to determine if a date falls on the weekend?
  27. Overflow when multiplying Integers and assigning to Long
  28. What are the differences between using the New keyword and calling CreateObject in Excel VBA?
  29. VBA Check if file (from website) exists
  30. Insert multiple records with a date range in MS Access

Leave a Comment