How to write data from access to the excel file

advertisements

I am trying to use the following code to write data into an excel file

     Dim objexcel As Excel.Application
                     Dim wbexcel As Excel.Workbook
                     Dim wbExists As Boolean
                     Set objexcel = CreateObject("excel.Application")
                     objexcel.Visible = True
                     On Error GoTo Openwb
                     wbExists = False
                     Set wbexcel = objexcel.Documents.Open("C:\Documents and Settings\TAYYAPP\Desktop\test folder\ERROR REPORT2.xls")
                     wbExists = True
Openwb:

                     On Error GoTo 0
                     If Not wbExists Then
                     Set wbexcel = objexcel.Workbook.Add
                     End If

but I'm getting an runtime error object doesn't support property or method in the line

Set wbexcel = objexcel.Workbook.Add

I have referenced the excel object library.


You will need to change this line:

 Set wbexcel = objexcel.WorkBooks.Open( _
    "C:\Documents and Settings\TAYYAPP\Desktop\test folder\ERROR REPORT2.xls")

Note WorkBooks, not Documents

As For this line Set wbexcel = objexcel.Workbook.Add, wbexcel is defined as a workbook, but the line is an action, so:

objexcel.Workbooks.Add
Set wbexcel = objexcel.ActiveWorkbook

EDIT: As an aside, DoCmd.Transferspreadsheet is probably the easiest way of transferring a set of data (query, table) from Access to Excel.