Using Access to automate a Pivot Table in Excel

[amhippi]

Hello,

I have been having trouble creating a pivot table in Excel from an Access module. The basic process is I have a series of complex queries in Access that I first export to Excel worksheets (which will be the data source for the pivots). From here, I wish to automate the creation of a pivot table in Excel. I have automated plenty of pivot tables directly from Excel with no issue, but for this particular report the VBA and queries reside in Access and I am creating and naming a new workbook on the fly through VBA.

The issue seems to be with defining the SourceData range in the Pivot Cache. I receive a type mismatch with the pivot cache code snippet below. I have tried to fully qualify the range in a variety of ways, such as (after activating the worksheet in question) and nothing seems to work:

Worksheets("Paid Production Detail").Range("A1:T80000")

Here is the pivot cache code giving me issues. This code will actually create the pivot table container on the worksheet in question, but then it bombs out with the type mismatch.

--------------------------------------------------------------------

With wkb
Set wks = wkb.Worksheets.Add(After:=.Sheets(.Sheets.Count))
wks.Name = "Paid Pivot"
End With

Set PSheet = wkb.Sheets("Paid Pivot")
Set DSheet = wkb.Sheets("Paid Production Detail")

'Define Data Range
LastRow = DSheet.Cells(DSheet.Rows.Count, 1).End(xlUp).Row
LastCol = DSheet.Cells(1, DSheet.Columns.Count).End(xlToLeft).Column
Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)

'Define Pivot Cache - type mismatch here
Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange). _
CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _
TableName:="PaidPivotTable")

 

[vba_php]

u know what the great thing about excel is? the macro recorder! have you tried creating this pivot table with the macro recorder, copying that code into an access database and modifying it so that it applies to the excel worksheet objects that are creating through the access application? that shouldn't be difficult.

 

[amhippi]

u know what the great thing about excel is? the macro recorder! have you tried creating this pivot table with the macro recorder, copying that code into an access database and modifying it so that it applies to the excel worksheet objects that are creating through the access application? that shouldn't be difficult.

----

Yep, the initial code that I use to do this was actually generated from Excel's macro recorder. The issue is modifying it to work when executed from Access.

The code assumes it is being executed from Excel, which qualifies the data source as part of a range or worksheet object inside Excel. I have Excel objects declared to try and get around this, but to no avail.

Dim xls As Excel.Application
Dim wkb As Excel.Workbook
Dim wks As Excel.Worksheet

Set xls = New Excel.Application
Set wkb = xls.Workbooks.Open(XlfilePath)

With wkb
Set wks = wkb.Worksheets.Add(After:=.Sheets(.Sheets.Count))
wks.Name = "Paid Pivot"
End With

(all of this works fine above)

I do have all the Excel references checked as well.

Its really seems as if this is an issue of Access interpreting the one part of the pivot cache code... the source data.

I cannot for the life of me get around it - and there doesn't seem to be any online forum where anyone else has done what I am attempting! I was hoping it was a simple syntax issue, but alas, I have tried a multitude of ways to reference the source data too.

Maybe it can't be done... but for now, I haven't accepted that possibility.

Thanks!

 

[theDBguy]

Hi amhippi. Welcome to AWF!


Are you talking about this part?

Set PCache = ActiveWorkbook.PivotCaches.Create _

If so, have you tried changing it to this?

Set PCache = wkb.PivotCaches.Create _

Just a guess...

 

[amhippi]

I think I have a bad copy and paste in my code snippet. I changed it so many times!

I had it as wkb.PivotCaches.Create because Access has no idea what the Active Workbook is (even after doing a wkb.Activate).

I am changing it back to that, but I think I still have the problem, but going to try it again.

Thanks

 

[theDBguy]

Okay. Let us know how it goes. If it doesn't work, you might consider posting a sample database and Excel files for testing.

 

[amhippi]

Yeah, still bombs.... I might just be building the pivot manually each time. Lord knows I have worked on this code for 2 days to save me 2 minutes when I have to run this report.

That doesn't seem like an efficient use of company time.

 

[theDBguy]

Oh well, sorry we couldn't help you. Good luck!

 

[amhippi]

Thank you sir...

 

[theDBguy]

Thank you sir...

Of course. Hopefully, we'll get lucky next time. Cheers!

 

[Gasman]

How have you defined PCache?

The code in this link works, albeit only once he says.?

https://www.mrexcel.com/board/threa...ves-run-time-1004-error.1116110/#post-5378248

 

[amhippi]

That code block looks like what I am attempting (first exporting data from Access to Excel).

I do have Pivot Cache declared and a lot of this code looks like methods I have tried - but I am going to play with this snippet and see if I have any more luck.

Thanks

 

[Uncle Gizmo]

I have used this excellent code from Bob Larson a Former prolific Contributor to AWF to manipulate Excel. Export A Table Or Query To Excel

It's not exactly the solution to your problem, but it does demonstrate some of the things you can do in Excel from MS Access. I also answered a previous Access World Forum post, and I made a note of my comments and placed the sample file on my website here:- Excel Sheets From Access Table You can either pay for the sample file and download it, or join my YouTube channel and I will send it to you for free!

 

[Gasman]

Beware that the poster says his falls over with subsequent run.
I just supplied it, in case you were missing an element perhaps.?

Perhaps also look at this link and post #7

http://www.utteraccess.com/forum/access-vba-create-pivot-t-t1999702.html

HTH

 

[amhippi]

Yep, nothing working... Will probably just have to build the dang thing each time I run the report. But, just in case somebody is feeling froggy and wanted to see the whole code. I have used this code many times within Excel to build pivot tables, calling it from an Access module - no dice. Dies on the PivotCache code line (I assume it doesn't like the Range object SourceData piece, nor does it like a fully qualified string).

And to make matters more confounding, as I said in my intro - the code actually will build a blank pivot table on a worksheet called "Paid Pivot" as I want it to - but it simply will not allow me to define the source data range. I attached a scrubbed sample of the spreadsheet to show that, you know, a part of it is working!


Sub BuildPaidPivot()

'Declare Variables
Dim PSheet As Worksheet
Dim DSheet As Worksheet
Dim PCache As PivotCache
Dim PTable As PivotTable
Dim PRange As Excel.Range
Dim LastRow As Long
Dim LastCol As Long
Dim XlfileName As String
Dim XlfldrPath As String
Dim XlfilePath As String
Dim xls As Excel.Application
Dim wkb As Excel.Workbook
Dim wks As Excel.Worksheet

XlfileName = Format(Now(), "mm_dd_yyyy") & "_IDC_Agents"
XlfldrPath = "C:\Users\auasm\Documents\Projects\Scheduled\IDC Agents\Reports"
XlfilePath = XlfldrPath & "" & XlfileName
XlfilePath = XlfilePath & ".xlsx"

'ASM - delete the extra sheet
Set xls = New Excel.Application
Set wkb = xls.Workbooks.Open(XlfilePath)
xls.Visible = True

With wkb
Set wks = wkb.Worksheets.Add(After:=.Sheets(.Sheets.Count))
wks.Name = "Paid Pivot"
End With

Set PSheet = wkb.Sheets("Paid Pivot")
Set DSheet = wkb.Sheets("Paid Production Detail")

'Define Data Range
LastRow = DSheet.Cells(DSheet.Rows.Count, 1).End(xlUp).Row
LastCol = DSheet.Cells(1, DSheet.Columns.Count).End(xlToLeft).Column
Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)

'Define Pivot Cache
Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange). _
CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _
TableName:="PaidPivotTable")

'Insert Blank Pivot Table
Set PTable = PCache.CreatePivotTable _
(TableDestination:=PSheet.Cells(1, 1), TableName:="PaidPivotTable")

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

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

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


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

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

End Sub

 

[theDBguy]

Hi. I hope someone picks it up and provide us a working solution. Cheers!

 

[Gasman]

You need to put code within code tags as this site has put spaces between the code keywords for some reason.
Also lost all indentation.

It fails with me at

Set PCache = ActiveWorkbook.PivotCaches.Create

with 'no such interface supported', so likely my version is too old for that.

I cannot help further. Sorry.

 

[Gasman]

I cannot get all of this to work in Excel

'Define Pivot Cache - type mismatch here
Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange). _
CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _
TableName:="PaidPivotTable")

but can get

'Define Pivot Cache - type mismatch here
Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange.Address) '. _
'CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _
'TableName:="PaidPivotTable")

to at least run, yet strangely the code below works without any issues?

Sub CreatePivotTable()
'PURPOSE: Creates a brand new Pivot table on a new worksheet from data in the ActiveSheet
'Source: www.TheSpreadsheetGuru.com

Dim sht As Worksheet
Dim pvtCache As PivotCache
Dim pvt As PivotTable
Dim StartPvt As String
Dim SrcData As String

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

'Create a new worksheet
  Set sht = Sheets.Add

'Where do you want Pivot Table to start?
  StartPvt = sht.Name & "!" & sht.Range("A3").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:="PivotTable1")

End Sub

So perhaps try the Address property.?

No idea as to why it would work in Excel and then npt Access.

 

[Uncle Gizmo]

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

 

[Gasman]

This works from Access. It has been modified from the code I found that would work on my Excel.
It creates the empty pivot table with data ready.

Hopefully you can adapt, if you think it is worth it. It will need tidying up, as it was only to see if it would work.

HTH

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:="PivotTable1")

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

End Sub

I got the code that worked for me from this link

https://www.thespreadsheetguru.com/blog/2014/9/27/vba-guide-excel-pivot-tables