Write data from Access to Excel file

Question

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 want the plural - Set wbexcel = objexcel.Workbooks.Add — braX
– braX, Commented Jun 12, 2018 at 19:23

Fionnuala · Accepted Answer · 2008-11-11 13:35:09Z

7

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.

edited Nov 11, 2008 at 13:35

answered Nov 11, 2008 at 12:33

Fionnuala

91.4k7 gold badges121 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

tksy Over a year ago

thanks that did the job also for writing data then wbexcel.insert "data" is this syntax ok

Fionnuala Over a year ago

Here is the language reference msdn.microsoft.com/en-us/library/aa220733(office.11).aspx. Don't forget that you can record macros and be guided by the code generated.

Mitch Wheat Over a year ago

@tksy - so how about voting his answer up?!?

tksy Over a year ago

i d like to but i dont have enough reputation for voting

tksy Over a year ago

I have managed to populate the cells with data i want and save it. while saving the file i save it in xls and csv format. but the csv format is not opening properly is it some encoding problem?

Mitch Wheat · Accepted Answer · 2008-11-13 09:34:42Z

I have got this code which works fine

Dim objexcel As Excel.Application
                     Dim wbexcel As Excel.Workbook
                     Dim wbExists As Boolean
                     Dim objSht As Excel.Worksheet
                     Dim objRange As Excel.Range


                     Set objexcel = CreateObject("excel.Application")
                     objexcel.Visible = True
                     On Error GoTo Openwb
                     wbExists = False
                     Set wbexcel = objexcel.Workbooks.Open("C:\Documents and Settings\TAYYAPP\Desktop\test folder\reports\ERROR REPORT2.xls")
                     Set objSht = wbexcel.Worksheets("Sheet1")
                     objSht.Activate
                     wbExists = True
Openwb:

                     On Error GoTo 0
                     If Not wbExists Then
                     objexcel.Workbooks.Add
                     Set wbexcel = objexcel.ActiveWorkbook
                     Set objSht = wbexcel.Worksheets("Sheet1")

                     End If

but I want to add one more check that if the file exists then I want to see if its is populated with values and if so then I want the next set of values to be populated from the end. As of now it is overwriting the existing values

Collectives™ on Stack Overflow

Write data from Access to Excel file

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related