Exporting to Excel from Access

[crv]

Hi,

Is there a way to export query results from access to excel, specifying the worksheet (if there are multiple worksheets in the excel file) and the row and column using SQL?

I would be greatful for any help.

Thanks

 

[PHV]

Instead of pushing you may consider to pulling.
In Excel, menu Data -> External Data -> Create query

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[hneal98]

Gerenally what will happen is when you export the query to an existing spreadsheet, a new worksheet will be added with that query result.

 

[sfm6s524]

You could try something like:

Dim XL As Object
Dim XLBook As Object
Dim XLSht As Object
Dim DB As DAO.Database
Dim RS As DAO.Recordset

' Of course, add error checking....

Set XL = GetObject("C:\TEST.XLS") ' Whatever your existing XL file is.
Set XLBook = XL.Parent
Set XLSht = XL.Worksheets("Sheet2") ' Whatever existing Sheetname is.
Set DB = CurrentDb
' Open RS with existing query or SQL.
Set RS = DB.OpenRecordset("qryData")
' or
Set RS = DB.OpenRecordset("SELECT ... FROM ... WHERE ... ORDER BY ...")
' Set upper left of data.
XLSht.Range("B2").CopyFromRecordset RS
'XLBook.Visible = True ' Optional
XLBook.Windows(1).Visible = True
XL.Save
XLSht.Application.Quit
Set XLSht = Nothing
Set XLBook = Nothing
Set XL = Nothing

Ken

 

[mpm32]

Hi,

this is an old thread but I am trying to use the code here.

I am using this

Private Sub Command13_Click()

  Dim XL As Object
  Dim XLBook As Object
  Dim XLSht As Object
  Dim DB As DAO.Database
  Dim RS As DAO.Recordset

  Set XL = GetObject("C:\pbfstax000000.xls")
  ' Whatever your existing XL file is.
  Set XLBook = XL.Parent
  Set XLSht = XL.Worksheets("Working Template")  ' Whatever existing Sheetname is.
  Set DB = CurrentDb
  ' Open RS with existing query or SQL.
  Set RS = DB.OpenRecordset("DailyExportQuery2")
  ' or
    ' Set upper left of data.
  XLSht.Range("A3").CopyFromRecordset RS
  'XLBook.Visible = True    ' Optional
  XLBook.Windows(1).Visible = True
  XL.Save
  XLSht.Application.Quit
  Set XLSht = Nothing
  Set XLBook = Nothing
  Set XL = Nothing


End Sub

When I run the code I get an error of "Run-time error '-2147417851 (80010105)' Method 'CopyFromRecordset' of object 'Range' failed.

Any thoughts? I can't seem to figure this out.

Thanks,

Mark

 

[hneal98]

I haven't worked with this, but should there be () after the XLSht.Range("A3").CopyFromRecordset, like this

XLSht.Range("A3").CopyFromRecordset(RS)

Perhaps it does not matter. Just a thought.

 

[PHV]

Why not testing the BOF and EOF properties of RS ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886