Output multiple queries to Excel from ACCESS??

[sonikudi]

Hi guys,

I want to ouput the results of multiple queries to different sheets in the same excel file. The docmd.transferspreadsheet method works great except that if a user makes any changes to the file and saves it, the changes save, and the next time the user triggers the event for the ouput query the command doesn't overwrite the previous changes and the file contains the previous changes made as well. While the docmd.outputto command doesn't allow the user to makes changes and even if the user does the next time you run the command it overwrites the changes the user made....i want something like that with the added benefit of being able to output multiple queries to an excel sheet...
This is what i have as of now:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, _
"Order Subtotals", "C:\Sales.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, _
"qrySalesByCountry", "C:\Sales.xls"
Call OpenSpecific_xlFile("C:\Sales")

Does anyone have any suggestions or ideas?? ..it would be much appreciated!!

Thanks in advance..

 

[Remou]

Have you considered simply linking the data into the spreadsheets using Get External Data from Excel?

 

[sonikudi]

That wouldn't work to well for my case as there are a lot of other things goin on in the code. I want all my code to be in VB in the access file that upon clicking will output the info into the same excel file every time i trigger the event.

 

[JerryKlmns]

The DoCmd.TransferSpreadsheet method, overwrites only the rows that are to be written from the table/query exported. So if you export 123 rows and excel sheet has already 124, the last one is still there. If this is your situation then you do have to delete that sheet first (with automation) and then export. Also if filter is on you can't export at all!

 

[GComyn]

So... there is no builtin function that will allow multiple tables to create multiple sheets in an excel spreadsheet... *sigh*...

guess I'll have to write some code that will do it for me.

gcomyn

 

[JerryKlmns]

GComyn

It will only take you to write four DoCmd.TransferSpreadsheet commands to export multiple tables to multiple sheets in the same workbook.

If you want to export multiple tables to the same worksheet a union query could do it with only one DoCmd.TransferSpreadsheet command.

 

[Irish1957]

I use the following VBA to transfer results of multiple queries and tables to a prepared excel template for reporting.

    Dim wbk As Excel.Workbook
    Dim wks As Excel.Worksheet
    Dim MyTemplate As String 'template file as used to create mulitiple graphs
    Dim MYOutput As String       'Output file buildup
    Dim rst As New ADODB.Recordset  ' Query/table   
    Dim Rows As Long
    Dim Mcolumn as long
    dim mcount as long	

    'Start with clean file built from template file
    MyTemplate = CurrentProject.Path & "\template.xls"
    MYOutput = CurrentProject.Path & "\output.xls"
'create blank output file
    FileCopy MyTemplate, MyOutput
    
    'Create the Excel Application, Workbook and Worksheet and Database object
    Set appExcel = New Excel.Application            'Assigns objects to variables
    appExcel.Visible = True                         'Makes Excel session visible
    Set wbk = appExcel.Workbooks.Open(MyOutput)

rst.Open "SELECT * FROM Table_Query1 ", CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic
Rows = 3 ' start row
mColumn = 4 ' start column
Do Until rst.EOF

With wbk.Sheets("sheet1") ' existing excel worksheet
.Cells(Rows, mcolumn).Value = rst.Fields("Field1").Value ' increment column and row as appropriate
.
.
.End With
rst.MoveNext
Loop
rst.Close

rst.Open "SELECT * FROM Table_Query2 ", CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic
Rows = 3
mColumn = 4
Do Until rst.EOF

With wbk.Sheets("sheet2") ' existing excel worksheet
.Cells(Rows, mcolumn).Value = rst.Fields("Field1").Value ' increment column and row as appropriate
.
.
.End With
rst.MoveNext
Loop
rst.Close

I have prepared the output graphs etc on the template. Found it simple to output create the graphs etc then blank the imported numbers as save as template.