I have three queries that hold daily, monthly and yeartodate information.. What I do now is manual but am looking for a way to automate this process, of taking the 3 queries, export to Excel WS, automatically create 3 pivot tables, in Excel, then create the master report with the 3 pivot table information...
I want the 3 tables to go on the same sheet if possible.
Here is an example about how to work with Excel from Access.
Const c_strXLSFile As String = "C:\Documents and Settings\SinnDHo\My documents\Access\CF_Sit\CF_Sit_2\Billing\Ack_19005.xls"
Const c_strXLSNewFile As String = "C:\Documents and Settings\SinnDHo\My documents\Access\CF_Sit\CF_Sit_2\Billing\Ack_19005.xls"
Dim appXL As Excel.Application
Dim rst As DAO.Recordset
' Create an instance of Excel.
Set appXL = New Excel.Application
' By default, the instance of Excel is not visible.
' It's better to see what happens when in development.
' Later on you can comment this line.
appXL.Visible = True
' This will open an existing Excel workbook (Workbook = .xls file).
' While this will create a new workbook.
' Save the new workbook giving it a name.
' We can select an existing sheet in the Excel workbook.
' Or we can create a new sheet.
.ActiveSheet.Name = "Dependencies"
' Write something in a cell in the selected sheet
' and format it.
' Select the cell.
' Write something in it.
.ActiveCell.FormulaR1C1 = "Dependencies in database:"
' Merge cells "B1" to "D1" and format the contents.
.Font.Name = "Arial"
.Font.Size = 14
.Font.Underline = xlUnderlineStyleSingle
.Rows("1:1").RowHeight = 25
' Open a recordset based on a query ("Query_1") and export its contents
' beginning at a given cell address ("A5") in the selected sheet.
Set rst = CurrentDb.OpenRecordset("Query_1", dbOpenSnapshot)
Set rst = Nothing
' Save the workbook and close it.
' Close the instance of Excel: This is very important !
' Setting appxl to nothing does not close Excel.
' Clean memory when done.
Set appXL = Nothing