Export Query Results to seperate Excel sheets

I'm looking for a way to export several different query result sets to seperate sheets all in a single Excel worksheet.
So when I click an 'Export' button, these 3 query results would be exported to a new Excel workbook, the first query going onto sheet1, the 2nd to sheet2 and so on.
Any thoughts about how I might get this to work?

Thanks in advance for any help or suggestions you might be able to offer.
bdbudAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Eric ShermanAccountant/DeveloperCommented:
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
0
Rey Obrero (Capricorn1)Commented:
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

0
bdbudAuthor Commented:
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!
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Rey Obrero (Capricorn1)Commented:
which one? , you have three lines like that
0
bdbudAuthor Commented:
The first.
0
Rey Obrero (Capricorn1)Commented:
do you see the excel file created?

how does your query1 look like?
0
bdbudAuthor Commented:
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.
0
Rey Obrero (Capricorn1)Commented:
i just tested the codes and it run without errors.

are there any records return by your query1?
0
Eric ShermanAccountant/DeveloperCommented:
>>>>>Thanks.  The TransferSpreadsheet action creates a new workbook for each query that is exported.  This is what I've currently got built.<<<<<<<

If you specify your workbook in the FileName parameter of the TransferSpreadsheet method you will establish a separate sheet within the workbook for each table/query exported.

ET
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bdbudAuthor Commented:
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.
0
Eric ShermanAccountant/DeveloperCommented:
>>>>>> 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
0
bdbudAuthor Commented:
Oh wow, this time it worked!  I wonder if I had a typo in there the first time!  Awesome, thanks again!
0
Rey Obrero (Capricorn1)Commented:
<Only issue is that it will transfer tables, but not query results> NOT true
it will transfer query results as recordsets
0
Rey Obrero (Capricorn1)Commented:
bdbud,

which one did you get to work?
0
bdbudAuthor Commented:
The Query worked.  and the table
0
Rey Obrero (Capricorn1)Commented:
what code did you use? can you post them here
0
Rey Obrero (Capricorn1)Commented:
bdbud,

are you sure you have accepted the correct suggestion?
0
bdbudAuthor Commented:
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.
0
Rey Obrero (Capricorn1)Commented:
np
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.