CODEWITHSUNDEEP
sqlvbams-access
jimmyluder123
jimmyluder123

Reputation: 11

How to export multiple queries into one Excel worksheet

I have some queries in my Access database. I know to Export These into one Excel workbook but in different Sheets. I want to list the results of the queries into one sheet and add one empty row and a caption between the results.

I don't know how can i handle it, could someone help me?

Upvotes: 0

Views: 2651

Answers (1)

Harassed Dad
Harassed Dad

Reputation: 4704

Set a reference to Microsoft excel in the Access Vb Editor

Sub ExportQueries
Dim xl as New Excel.Application  'start up excel
dim wb as workbook
dim ws as worksheet
dim r as range
set wb = xl.workbooks.add      'add a workbook
set ws = wb.worksheets(1)      'point to first sheet
set r = ws.range("a1")         'point to a cell
r = "my first caption"
set r = r.offset(1,0)
'dim rs as new recordset        'ADO
Dim rs as recordset     'DAO
'   rs.open "myquery",currentproject.connection  'ADO
 Set rs = Currentdb.OPenrecordset("myquery")  'DAO


 '*************************Copy field headings into excel
 Dim f as field   
 dim x as integer
 For each f in rs.Fields
    r.offset(0,1)=f.name
    x = x+1
 next f
  set r = r.offset(1,0)
  '****************************End field headings


r.copyfromrecordset rs   'copy results into xl
rs.close
set r = r.end(xldown).offset(2,0) 'point to cell 2 below end of first set of results
r = "my next caption"
set r = r.offset(1,0)
rs.open "myotherquery",currentproject.connection
r.copyfromrecordset rs
rs.close
set r = r.end(xldown).offset(2,0)
'and so on

end sub

Upvotes: 2

Related Questions