Tough One - Update Pivot Table chart in Report using VBA?

[jer99]

I have posted this on other forums and can't seem to find an answer,

I have a report that is based on a Oracle view.
The report contains a chart/Pivot table. I would like to have the chart
reflect the new data in the Oracle view. The refresh option in the object
doesnt do it.

Typically the steps I manually go through to update the chart are this:
Open report
Click on chart object
Click Worksheet Object --> Edit
Two toolbars appear - Chart & Pivot Table
I click on pivot table bar
Click the exclamation mark Update
Then an ODBC box appears asking for my username/password
Then I close the report saving changes when asked.

There has to be a way to do this from VBA.
The Chart itself says it's an Unbound Object Frame. (Someone else made it)
The Data Tab shows it's an
OLE Class - Microsoft Office Excel 2003
display type - content
update -automatic
OLE TYpe - Embedded
OLE Te allowed - Either
Class - Excell.Sheet.8
-----
Name - OLEUnbound338

=======================================

Anyone have any ideas?

Jerry

 

[Michael Cheng [MSFT]]

Hi Jerry,

Thanks for your post.

Would you please clarify "There has to be a way to do this from VBA", does
it mean someone had built it and you could use that chart to update the raw
in Pivot Table?

You may refer the Knowledge Base articles below for more information

How to programmatically build a pivotTable view in an Access 2002 form
http://support.microsoft.com/kb/298764

ACC2000: How to Use Code to Change Column Headings in a Crosstab Query
http://support.microsoft.com/kb/210004

Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!


Sincerely yours,

Michael Cheng
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[jer99]

Hi Michael,
Yes - someone put together the report with the cahrt (based on a pivot
table) on the report. I'm 99% sure they used the wizard.
The report is one of many that need to be compiled into a PDF.
I have automated the PDF compilation process using VBA kicked off by a macro
- so others can use it easily.
However, I have to manually do the steps I outlined in order to "refresh"
the chart before I do the PDF.
I sometimes forget (I'm getting older).
I would like to add steps in the module so that I do not have to mannually
do these - but I do not know how to do any of these steps. I've been doing
VB(A) for about 3-5 years and have never run into this. It seems not a lot of
people know how.

The steps are:
Click on chart object
Click Worksheet Object --> Edit
Two toolbars appear - Chart & Pivot Table
I click on pivot table bar
Click the exclamation mark Update
Then an ODBC box appears asking for my username/password
Then I close the report saving changes when asked.

For example, I dont know how to find the chart object. I don't know where to
find the Update (from the pivot table bar) method. And...I don't know how to
connect to the ODBC connection - should I do it before everything or in the
order that I do it manually?

I would just like to do the manual steps programatically inside a module so
I dont forget to do them.

Any help would be greatly appreciated. It's been 3 months and I cant seem to
find an answer.

jerry

 

[Michael Cheng [MSFT]]

Hi Jerry,

I understood that you are using the Unbound Object Frame in an access
report to show up a pivot table, and you want to auto refresh it when you
open this report. If I misunderstood your concern, please feel free to let
me know.

You can use the VBA to control the Unbound Object Frame to refresh.
Assuming the Unbound Object Frame named 'OLE1' and the pivot table named
'PivotTable1', here is the simple sample code:

OLE1.Object.ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh

Thank you for your patient and hope this will be helpful!


Sincerely yours,

Michael Cheng
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[jer99]

Michael,
Your understanding is correct.
I am a lot closer than I was before.

However, I do not understand where to use the line you provided.
The report name is "Claim_Audit_Report_Page_13"
The object is OLEUnbound33.

So I tried :

Dim rpt As Access.Report
DoCmd.OpenReport "Claim_Audit_Report_Page_13", acViewDesign
Set rpt = Reports("Claim_Audit_Report_Page_13")
rpt!OLEUnbound334.Object.ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh

But it said the object wasn't supported.

The other thing I didn't like about the above was that it opened the report
in a design view. I didn't want users to see the report when this was
happening.

Sorry to be so slow here, but how do I utilize the line of code you sent in
my "Claim_Audit_Report_Page_13" report?


Thanks so much,
Jerry

 

[Michael Cheng [MSFT]]

Hi Jerry,

Could you leave your email to me and I could generate a sample Access
application file for you using AdventureWorks as datasource.


Sincerely yours,

Michael Cheng
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[jer99]

Sure hate leaving my email here.
But here goes - it's a temp one.
(e-mail address removed)

Please send it to that email.
Sure appreciate it.

 

[Michael Cheng [MSFT]]

Hi Jerry,

I have sent you the sample


Sincerely yours,

Michael Cheng
Microsoft Online Partner Support
======================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[jer99]

Thanks Michael.
I took a look at it and while it is similar, it is not quite there.

it is my fault for not clarifying the issue at this point.
I now understand how to get to the chart part and refresh it.
But my situation is that it is in a report - the pivot chart was
automatically generated.
And there's a connection to an external oracle database involved.

What I can not figure out, is how to address the report object and the
specific unbound object within it from within VBA and also how to make sure I
connect with the data before addressing it .

Problem 1:
-------------------------------------------------
For example,
'Connect to data
' If the database is called 'MyData' and 'My Table' what do I do?

?????

Problem 2:
----------------------------------------------------
'Then I need to get at my report in the current application

Dim rpt As Access.Report
DoCmd.OpenReport "Report_Page_13", acViewDesign
Set rpt = Reports("Report_Page_13"
rpt!OLEUnbound334.Object.ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh

That generates an error: object does not support this property.


Problem #3:
--------------------------------------------------------
Since the Chart is contained within a report only (autogenerated) and really
doesn't reside anywhere that I know of, how do I find the name of the chart?


Thanks for hanging in here with me Michael - this has been 3 months in the
works, and it's apparently not too easy.

 

[Michael Cheng [MSFT]]

Hi,

Thanks for the udpate.

Please understand that we will provide publicly available code samples and
links where available,but will not assist with custom coding (including
macros, event sinks, queries, etc.).

For now, since it is too complex in the newsgroup, I recommend that you
open a Support incident with Microsoft Customer Service and Support (CSS)
so that a dedicated Support Professional can work with you in a more timely
and efficient manner. If you need any help in this regard, please let me
know.

For a complete list of Microsoft Customer Service and Support phone
numbers, please go to the following address on the World Wide Web:
<http://support.microsoft.com/directory/overview.asp>

If you are outside the US please see http://support.microsoft.com for
regional support phone numbers.


Sincerely yours,

Michael Cheng
Microsoft Online Partner Support
======================================================
PLEASE NOTE the newsgroup SECURE CODE and PASSWORD will be updated at 9:00
AM PST, February 14, 2006. Please complete a re-registration process by
entering the secure code mmpng2006 when prompted. Once you have entered the
secure code mmpng2006, you will be able to update your profile and access
the partner newsgroups.
======================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.