Exporting from a Access Table to Excel PivotTable

[runthis457]

I was wondering how I would go about exporting data from a table in access to a pivotTable in Excel. I know how to set up a Pivot Table so that is nothing new for me, but the question is how to set up the Pivot Table one time, and then at the simple press of a button in access the data from my access table is imported into my PivotTable and I can view the most up to date information.

[alansidman]

This should help you

http://www.vbforums.com/showthread.php?t=376693

Alan

[runthis457]

It looks like this code should do the trick:

Dim rst As DAO.Recordset
    Dim customQuery As String
    Dim cnt As Integer
    
    Dim appExcel As Excel.Application
    Dim wbk As Excel.Workbook
    Dim wks As Excel.Worksheet
    Dim rng As Excel.Range
    
    Dim fileName As String

    Set appExcel = Excel.Application
    appExcel.Visible = True
    Set wbk = appExcel.Workbooks.Add
    Set wks = wbk.Worksheets(1)
    Set rng = wks.Range("A2:I4001")
    wks.Cells(1, 1).Value = "Generating data..."
    
    Set rst = CurrentDb.OpenRecordset(customQuery)
    If (rst.RecordCount > 0) Then
        cnt = 1
        For Each fld In rst.Fields
            wks.Cells(1, cnt).Value = fld.Name
            cnt = cnt + 1
        Next fld
        Call rng.CopyFromRecordset(rst, 4000, 26)
    End If
    
    fileName = ExportDir & "\Molenproductie" & DateTimeFrom & "-" & DateTimeTo & ".xls"
    wks.SaveAs (fileName)
    
    rst.Close

    Set rst = Nothing

However, how would I keep it from overwriting the data each time I export? Like I want it to append the data to it. What I am trying to do is create a monthly report, but I need to export the data daily. So every day (Mon - Fri) I need to export the data, but have it appended (Tues, appended to Mon and so on).