Export multiple queries to the same Excel file (different tabs)

[ordendelfai]

I am trying to export multiple queries to the exact same Excel filename, just with different tabs. If I click on a query in Access, then go to File -> Export, and find an Excel file, then Export, Access exports the query into a new tab of the Excel file with the name of the query. I am trying to duplicate that effect using a recordset and loop.

Below is the code that exports fine, but it seems to just overwrite the excel file each time it loops. I have paused the code at each loop and checked the excel file, and the code is in fact exporting each query one by one. If I have 3 queries, only the 3rd query is in the excel file at the end. Please let me know if you have any suggestions.

Private Sub ExportExcel()

Dim FileName As String
Dim strSQL As String
Dim ExRst As DAO.Recordset
Dim ExportObject As String 'Holds the object to be exported

FileName = "C:\AccessOutput.xls"
strSQL = "SELECT tbl_ReportExports.* FROM tbl_ReportExports WHERE ReportID = " & ReportID & " Order By RunOrder;" 'ReportID is grabbed from a global variable
Set ExRst = db.OpenRecordset(strSQL)

If ExRst.EOF Then
    MsgBox "There are no queries to export", , "No Objects"
    GoTo ExitHere
 End If

Do Until ExRst.EOF 'This exports all the queries to the same Excel File
    ExportObject = ExRst!ExportObject
    DoCmd.OutputTo acOutputQuery, ExportObject, acFormatXLS, FileName, False    
ExRst.MoveNext
Loop

ExitHere:
ExRst.Close
Set ExRst = Nothing
Set exApp = Nothing
End
End Sub

A Second question, somewhat related is, can I force the output into a specific Excel version (Such as 97-2002)?

Thanks,

~Joel
As common courtesy and to help other members who might find your thread helpful in the future, please respond if the advice given resolved your issue.

 

[ordendelfai]

Nevermind, I determined that the TransferSheets method duplicates the export process, and allows me to choose the version.

DoCmd.TransferSpreadsheet acExport, 8, ExportObject, FileName, True

~Joel
As common courtesy and to help other members who might find your thread helpful in the future, please respond if the advice given resolved your issue.

 

[AccessGuruCarl]

Do you want all the data on one worksheet?
Or each query on a seperate worksheets.

Seperate is alot easier!!!!

You need to add a reference to Excel

Then add a workheet for each query.
As it runs.

I'll post a link or code example to do this a little later tonight!

Or you can try searching for PDF to Excel in the forums.
This will give you a starting point for some code to reference Excel!

Carl

AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[AccessGuruCarl]

Hi ordendelfai,
Here is the link I mentioned above.

http://www.tek-tips.com/viewthread.cfm?qid=917810

It's for importing excel tabs into access, but the concept is basically the same.

Scroll this thread, for my posting!

Hope this helps

Carl

AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[ordendelfai]

Thanks for the link, that import information may come in handy. The transfersheets method worked perfectly for my export into multiple sheets (each loop referenced the same file, and transfersheets automatically puts each query into a seperate sheet).

Thanks again ;-)



~Joel
As common courtesy and to help other members who might find your thread helpful in the future, please respond if the advice given resolved your issue.

 

[ordendelfai]

Ok, this is wierd. This has been working fine, then all of a sudden, I am running into problems with multiple tabs. What happens is that I export the first query to a named excel file (C:\AccessOutput.xls). Then I output the second query using transfersheets to the same file. It creates a new tab in excel with data.

During that process, alot of the data in the first tab gets skewed (data from different columns is rearranged..).

I can duplicate the problem manually, by choosing a qry, exporting it to the named file. If I open the file, the data in the only tab is fine. Then I choose another qry to export to the same named file, reopen the excel file and the data in the first tab is now skewed, but in the 2nd tab of excel, the data is fine. This is Excel 97. I cannot think of any reason why this would happen?



~Joel
As common courtesy and to help other members who might find your thread helpful in the future, please respond if the advice given resolved your issue.

 

[ordendelfai]

Well, I messed with the TransferSheets method, and used number 5, which is Excel 7, instead of 8, which is Excel 97.

When I use the Excel 7 version, this problem does not occur, but when I use 97, it does.

I suppose this is a fix, but if anyone has any clues why this would happen, I would really appreciate it.

Thanks ;-)

~Joel
As common courtesy and to help other members who might find your thread helpful in the future, please respond if the advice given resolved your issue.