Export Query Results to seperate Excel sheets

Straight from the Help menu ....


TransferSpreadsheet Method [Access 2003 VBA Language Reference]
The TransferSpreadsheet method carries out the TransferSpreadsheet action in Visual Basic.

expression.TransferSpreadsheet(TransferType, SpreadsheetType, TableName, FileName, HasFieldNames, Range, UseOA)

expression Required. An expression that returns one of the objects in the Applies To list.

 AcDataTransferType
AcDataTransferType can be one of these AcDataTransferType constants.
acExport
acImport default
acLink
If you leave this argument blank, the default constant (acImport) is assumed.
 

 AcSpreadSheetType
AcSpreadSheetType can be one of these AcSpreadSheetType constants.
acSpreadsheetTypeExcel3
acSpreadsheetTypeExcel4
acSpreadsheetTypeExcel5
acSpreadsheetTypeExcel7
acSpreadsheetTypeExcel8 default
acSpreadsheetTypeExcel9 default
acSpreadsheetTypeLotusWJ2 - Japanese version only
acSpreadsheetTypeLotusWK1
acSpreadsheetTypeLotusWK3
acSpreadsheetTypeLotusWK4
Note  You can link to data in a Lotus 1-2-3 spreadsheet file, but this data is read-only in Microsoft Access. You can import from and link (read-only) to Lotus .WK4 files, but you can't export Microsoft Access data to this spreadsheet format. Microsoft Access also no longer supports importing, exporting, or linking data from Lotus .WKS or Microsoft Excel version 2.0 spreadsheets by using this method.

If you leave this argument blank, the default constant (acSpreadsheetTypeExcel8) is assumed.
 

TableName  Optional Variant. A string expression that's the name of the Microsoft Access table you want to import spreadsheet data into, export spreadsheet data from, or link spreadsheet data to, or the Microsoft Access select query whose results you want to export to a spreadsheet.

FileName  Optional Variant. A string expression that's the file name and path of the spreadsheet you want to import from, export to, or link to.

HasFieldNames  Optional Variant. Use True (1) to use the first row of the spreadsheet as field names when importing or linking. Use False (0) to treat the first row of the spreadsheet as normal data. If you leave this argument blank, the default (False) is assumed. When you export Microsoft Access table or select query data to a spreadsheet, the field names are inserted into the first row of the spreadsheet no matter what you enter for this argument.

Range  Optional Variant. A string expression that's a valid range of cells or the name of a range in the spreadsheet. This argument applies only to importing. Leave this argument blank to import the entire spreadsheet. When you export to a spreadsheet, you must leave this argument blank. If you enter a range, the export will fail.

UseOA  Optional Variant. This argument is not supported.


ET

you can use this sub

Sub exportExcel()
Dim rs As DAO.Recordset
Dim xlObj As Object, Sheet As Object, iCol As Integer

    Set xlObj = CreateObject("Excel.Application")
    xlObj.Workbooks.Add
    xlObj.Visible = True
    Set rs = CurrentDb.OpenRecordset("query1")
    Set Sheet = xlObj.activeworkbook.Worksheets("Sheet1")
        For iCol = 0 To rs.Fields.Count - 1
            Sheet.cells(1, iCol + 1).Value = rs.Fields(iCol).Name
        Next
    Sheet.range("A2").CopyFromRecordset rs  'copy the data
   
    Set rs = CurrentDb.OpenRecordset("query2")
    Set Sheet = xlObj.activeworkbook.Worksheets("Sheet2")

        For iCol = 0 To rs.Fields.Count - 1
            Sheet.cells(1, iCol + 1).Value = rs.Fields(iCol).Name
        Next
    Sheet.range("A2").CopyFromRecordset rs
       
    Set rs = CurrentDb.OpenRecordset("query3")
    Set Sheet = xlObj.activeworkbook.Worksheets("Sheet3")

        For iCol = 0 To rs.Fields.Count - 1
            Sheet.cells(1, iCol + 1).Value = rs.Fields(iCol).Name
        Next
    Sheet.range("A2").CopyFromRecordset rs
   
    'save the excel file
    xlObj.activeworkbook.saveas "C:\MyExcel.xls"
   
    Set Sheet = Nothing
    xlObj.Quit
    Set xlObj = Nothing
End Sub

Thanks.  The TransferSpreadsheet action creates a new workbook for each query that is exported.  This is what I've currently got built.

Capricorn, I tried the code, but got an error:  Class does not support Automation or does not support expected interface.

Errored on:      Sheet.range("A2").CopyFromRecordset rs  'copy the data

Any thought?
Thanks again for the help!

which one? , you have three lines like that

The first.

do you see the excel file created?

how does your query1 look like?

It does create the Excel file and places the column headers starting at A1, thats when it stops.  Like it doesn't recognize the CopyFromRecordset action.

i just tested the codes and it run without errors.

are there any records return by your query1?

Yeup, the query is returning records.  I wonder if I have to set references or something???  Something in my Excel setup might not be correct.

Capricorn, that worked.  Hmm.  If I only would have know.  Only issue is that it will transfer tables, but not query results, so I'll have to have it save the data to a table first then transfer.  This should work though for my purposes.

Thanks.

>>>>>> Only issue is that it will transfer tables, but not query results, so I'll have to have it save the data to a table first then transfer.  This should work though for my purposes.<<<<<<<

You can use TransferSpreadsheet method with  a query or a table.  The query should be a Select query.

Are you getting some type of error using a quer???


ET

Oh wow, this time it worked!  I wonder if I had a typo in there the first time!  Awesome, thanks again!

<Only issue is that it will transfer tables, but not query results> NOT true
it will transfer query results as recordsets

bdbud,

which one did you get to work?

The Query worked.  and the table

what code did you use? can you post them here

bdbud,

are you sure you have accepted the correct suggestion?

Wow.  I must be new or something...  I'm sorry, I got confused as to who was suggesting what.  I was not able to get the code that Capricorn posted to work.

Estherman's suggestion to use the TransferSpreadsheet action worked without incident.  It accomplished the task of exporting multiple query result sets to individual worksheets in a single workbook.

Thank you both for the suggestions.

np