locked
Opening an Excel Application in VBA RRS feed

  • Question

  • I'm currently trying to use a module in Access 2010 to open an Excel file and write to specific fields within said file. I have attempted this multiple times from many approaches with little avail. I added a reference to "Microsoft Excel 14.0 Object Library" and I am trying to do this currently:

    Dim xlsxApplication As Object
      
    'Open the Excel application
    xlsxApplication = CreateObject("Excel.Application")
    

    The current error I am recieving is Run-time error '91': Object variable or With block variable not set

    I have also attempted something like this:

    Dim xlsxApplication As Excel.Application
      
    'Open the Excel application
    xlsxApplication = New Excel.Application
    

    Does anyone know the correct way to go about this?


    One must therefore be a fox to recognize traps, and a lion to frighten wolves.
    Monday, May 9, 2011 3:23 PM

Answers

  • I needed to add a Set. My bad

    Dim xlsxApplication As Object
     
    'Open the Excel application
    Set xlsxApplication = CreateObject("Excel.Application")


    One must therefore be a fox to recognize traps, and a lion to frighten wolves.
    • Marked as answer by Don Mon Monday, May 9, 2011 3:33 PM
    Monday, May 9, 2011 3:33 PM

All replies

  • I needed to add a Set. My bad

    Dim xlsxApplication As Object
     
    'Open the Excel application
    Set xlsxApplication = CreateObject("Excel.Application")


    One must therefore be a fox to recognize traps, and a lion to frighten wolves.
    • Marked as answer by Don Mon Monday, May 9, 2011 3:33 PM
    Monday, May 9, 2011 3:33 PM
  • So, you want to control Excel from Access???

    Set a Reference to the Excel Object Library:

    Tools > References > Microsoft Excel xx.0 Object Library

    Option Compare Database
    Option Explicit ' Use this to make sure your variables are defined

    Private objExcel As Excel.Application
    Private xlWB As Excel.Workbook
    Private xlWS As Excel.Worksheet

    Sub Rep()

    Dim strFile As String

    strFile = "C:\FULL_PATH_TO_YOUR_FILE.xls"

    ' Opens Excel and makes it Visible
    Set objExcel = New Excel.Application
    objExcel.Visible = True

    'Opens up the Workbook
    Set xlWB = objExcel.Workbooks.Open(strFile)

    'Sets the Workseet to the last active sheet - Better to use the commented version and use the name of the sheet.
    Set xlWS = xlWB.ActiveSheet
    'Set xlWS = xlWB("Sheet2")

    With xlWS ' You are now working with the Named file and the named worksheet
    'Begin your Excel code here . . .


    End With

    'Do Close and Cleanup
    End Sub

     

    • Proposed as answer by Addicted2Latte Thursday, November 6, 2014 3:57 PM
    Monday, May 9, 2011 5:31 PM
  • Thanks for the information.  This was extremely helpful and exactly what I needed to know.
    Sunday, March 2, 2014 9:09 PM
  • Please click 'Propose as answer' if it was helpful.

    Thanks.


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Sunday, March 2, 2014 9:16 PM