Alex Dybenko: Create Pivot Table in Excel using automation?

[Guest]

Im using the DoCmd.TransferSpreadsheet method to create a worksheet in excel
then I use the CreateObject("Excel.Application") and xlApp.Workbooks.Open to
establish the environment . At this point I use
ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal) to create a pivot
table on the Access query.

..CreatePivotTable is working and the table does get created, but when I go to
ActiveSheet.PivotTables("PivotTable").AddFields, I get "add fields method of
Pivot Table Class failed with a run code error 1004."

I would have thought that when the .createPivotTable method worked, that
would have establish the environment for the .addfields methods, as well.

I need a nice pivot table which I can produce in Excel, but the data and all
supporting logic is in Access.

Do you have any thoughts on this other than doing it in Excel by using a MS
Query against the DB?

I appreciate any comments.

Lee

 

[Alex Dybenko]

Hi Lee,
i think that instead of using MS Query better is to export data to excel
sheet. then you can start macro recorder there, format data in a way you
like and then make similar code in access
i use the same approach in several applications and it works fine

 

[Guest]

Hey Thanks for the reply Alex!

I understand what you mean, but I have a user that wants to just "click a
button" and have the daily input captured from Excel, appended to a table,
have the queries run and the graphs produced.

These are daily statistics that are captured in a table and then reported on
as daily, weekly, monthly and yearly stats.

The data's way too big to try to hold it all in excel, so I'm stuck.

Can you offer any further ideas on a way too "automate" all of that?

Thanks,
Lee

 

[Guest]

Here's an article that I just found that addresses the problem exactly!

http://msdn.microsoft.com/library/d...y/en-us/dnoffpro01/html/OfftheChartAccess.asp

Looks like I'm going to be busy for quite a while learning this.