Write data from Access to Excel file
12,405
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.
Author by
tksy
Updated on June 04, 2022Comments
-
tksy almost 2 years
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.
-
tksy over 15 yearsthanks that did the job also for writing data then wbexcel.insert "data" is this syntax ok
-
Mitch Wheat over 15 years@tksy - so how about voting his answer up?!?
-
tksy over 15 yearsi d like to but i dont have enough reputation for voting