Using Access to automate a Pivot Table in Excel

[Gasman]

Paul,
What about making the Excel subroutine "CreatePivotTable" into a public function, and then call that function from MS Access?

Tony,
The problem I have is I am not sure what works with 2007 and what does not. Initially I thought 2007 could not do pivottables by VBA, but that proves not to be the case.

The op had a CreatePivotTable statement inside another function and my excel balked at that.

Plus I am not that great a coder. It is mostly trial and error with me. By the time I have tested some code and gone back to post, someone has normally got there before me.

 

[Gasman]

OK, I have come back to this and spotted this.

'Insert Data Field
With ActiveSheet.PivotTables("PaidPivotTable").PivotFie lds("Sum of Premium Credit")
.Orientation = xlDataField
.Function = xlCount
.Name = "Sum of Premium Credit"
End With
With ActiveSheet.PivotTables("PaidPivotTable").PivotFie lds("Sum of Policy Count")
.Orientation = xlDataField
.Function = xlCount
.Name = "Sum of Policy Count"
End With

I used the macro recorder to get the code for the Sum?
That produces xlSum but starts at xlCount when dragged in.?

This code works from Access.?

Sub CreatePivotTable()
'PURPOSE: Creates a brand new Pivot table on a new worksheet from data in the ActiveSheet
'Source: www.TheSpreadsheetGuru.com
    Dim xls As Excel.Application
    Dim wkb As Excel.Workbook
    Dim sht As Excel.Worksheet
    Dim pvtCache As Excel.PivotCache
    Dim pvt As Excel.PivotTable
    Dim StartPvt As String
    Dim SrcData As String

    Set xls = New Excel.Application
    Set wkb = xls.Workbooks.Open("C:\Temp\PivotTableVBA.xlsm")

    'Determine the data range you want to pivot
    SrcData = "Sheet1!" & Range("A1:T4").Address(ReferenceStyle:=xlR1C1)

    'Create a new worksheet
    Set sht = wkb.Sheets.Add

    'Where do you want Pivot Table to start?
    StartPvt = sht.Name & "!" & sht.Range("A1").Address(ReferenceStyle:=xlR1C1)

    'Create Pivot Cache from Source Data
    Set pvtCache = ActiveWorkbook.PivotCaches.Create( _
                   SourceType:=xlDatabase, _
                   SourceData:=SrcData)

    'Create Pivot table from Pivot Cache
    Set pvt = pvtCache.CreatePivotTable( _
              TableDestination:=StartPvt, _
              TableName:="PaidPivotTable")

    'Insert Row Fields
    With sht.PivotTables("PaidPivotTable").PivotFields("Agent Number")
        .Orientation = xlRowField
        .Position = 1
    End With

    With sht.PivotTables("PaidPivotTable").PivotFields("Agent Number")
        .Orientation = xlRowField
        .Position = 1
    End With
    With sht.PivotTables("PaidPivotTable").PivotFields("Agent Name")
        .Orientation = xlRowField
        .Position = 2
    End With
    With sht.PivotTables("PaidPivotTable").PivotFields("Agent State")
        .Orientation = xlRowField
        .Position = 3
    End With
    With sht.PivotTables("PaidPivotTable").PivotFields("Home Agency Number")
        .Orientation = xlRowField
        .Position = 4
    End With
    With sht.PivotTables("PaidPivotTable").PivotFields("Home Agency Name")
        .Orientation = xlRowField
        .Position = 5
    End With

    'Insert Column Fields
    With sht.PivotTables("PaidPivotTable").PivotFields("Year")
        .Orientation = xlColumnField
        .Position = 1
    End With

    'Insert Data Field
    'With sht.PivotTables("PaidPivotTable").PivotFields("Sum of Premium Credit")
    '.Orientation = xlDataField
    '.Function = xlCount
    '.Name = "Sum of Premium Credit"
    'End With
    'With sht.PivotTables("PaidPivotTable").PivotFields("Sum of Policy Count")
    '.Orientation = xlDataField
    '.Function = xlCount
    '.Name = "Sum of Policy Count"
    'End With

    sht.PivotTables("PaidPivotTable").AddDataField sht.PivotTables( _
                "PaidPivotTable").PivotFields("Premium Credit"), "Sum of Premium Credit", xlSum

    sht.PivotTables("PaidPivotTable").AddDataField sht.PivotTables( _
                "PaidPivotTable").PivotFields("Policy Count"), "Sum of Policy Count", xlSum

    'Format Pivot Table
    sht.PivotTables("PaidPivotTable").ShowTableStyleRowStripes = True
    sht.PivotTables("PaidPivotTable").TableStyle2 = "PivotStyleMedium9"
    sht.PivotTables("PaidPivotTable").RowAxisLayout xlTabularRow
    sht.PivotTables("PaidPivotTable").NullString = "0"

    'Hide subtotals
    sht.PivotTables("PaidPivotTable").PivotFields("Agent Number").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
    sht.PivotTables("PaidPivotTable").PivotFields("Agent Name").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
    sht.Columns("B:N").AutoFit

    Set pvt = Nothing
    Set pvtCache = Nothing
    Set sht = Nothing
    Set wkb = Nothing
    xls.Visible = True

End Sub

HTH