Output data to different tabs in new excel workbook

[Tadynn]

Does anyone know how to create an excel workbook and then copy the results of 3 different SQL queries into that workbook, but on separate tabs?

The 3 queries that I wish to run are as follows:

into TAB1:

DoCmd.RunSQL (SELECT [#TempMoveTrans].Loggon, Format(TimeValue([EffectiveDate]),'hh') AS [Time], Count(*) AS Movements
FROM [#TempMoveTrans]
GROUP BY [#TempMoveTrans].Loggon, Format(TimeValue([EffectiveDate]),'hh'))

into TAB2:

DoCmd.RunSQL (SELECT [#TempMoveTrans].[Loggon], Count(*) AS Movements
FROM [#TempMoveTrans]
GROUP BY [#TempMoveTrans].[Loggon]
ORDER BY Count(*) DESC)

into TAB3:

DoCmd.RunSQL (SELECT IIf([Movement]="Inventory Move","Inventory Moves",IIf([Movement]="Replenishment","Replenishments",IIf([Movement]="Putaway","Putaways",IIf([Movement] Is Null,"(Unknown)&quot))) AS [Move Type], Count(*) AS Movements
FROM [#TempMoveTrans]
GROUP BY IIf([Movement]="Inventory Move","Inventory Moves",IIf([Movement]="Replenishment","Replenishments",IIf([Movement]="Putaway","Putaways",IIf([Movement] Is Null,"(Unknown)&quot)))
ORDER BY Count(*) DESC)

Thanks, Tadynn

 

[MinusM]

There may be many ways to do this like TransferSpreadSheet.

Here I am using OutputTo. Each of the SQLs should be saved a queries for this. The queries are saved temporarily in different Workbooks and then copied to the first one. The other 2 are deleted afterwards

Give full path name of the file


Dim exApp As Excel.Application, newSheet As Worksheet
Set exApp = CreateObject("Excel.Application&quot

DoCmd.OutputTo acOutputQuery, "FirstQryName", acFormatXLS, "ActualFileName", False

DoCmd.OutputTo acOutputQuery, "SecondQryName", acFormatXLS, "TmpFileName1", False
DoCmd.OutputTo acOutputQuery, "ThirdQryName", acFormatXLS, "TmpFileName2", False

exApp.Workbooks.Open "ActualFileName"
exApp.Workbooks.Open "TmpFileName1"
exApp.Workbooks.Open "TmpFileName2"
exApp.Workbooks(2).Worksheets(1).Copy after:=exApp.Workbooks(1).Worksheets(1)
exApp.Workbooks(3).Worksheets(1).Copy after:=exApp.Workbooks(1).Worksheets(2)
exapp.Workbooks(1).Close SaveChanges:=True
exapp.Workbooks.Close

set exApp=nothing
Kill "TmpFileName1"
Kill "TmpFileName2"

Best of luck

 

[SuicidED]

Hi, have a look at this, it may be of assistance. thread705-737567