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...
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
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.
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...
----
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...
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...