 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Modules / DAO / VBA / February 2006Tough One - Update Pivot Table chart in Report using VBA?
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 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. 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 > Hi Jerry, > [quoted text clipped - 25 lines] > ===================================================== > This posting is provided "AS IS" with no warranties, and confers no rights. 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. 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 > Hi Jerry, > [quoted text clipped - 20 lines] > ===================================================== > This posting is provided "AS IS" with no warranties, and confers no rights. 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. Sure hate leaving my email here. But here goes - it's a temp one. gbg@rightchoice.biz Please send it to that email. Sure appreciate it. > Hi Jerry, > [quoted text clipped - 10 lines] > ===================================================== > This posting is provided "AS IS" with no warranties, and confers no rights. 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. 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. 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. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.