Pivotcache.refresh Application-defined Or Object-defined Error
here for a quick overview of the site Help Center Detailed answers to any questions application defined or object defined error pivot table filter you might have Meta Discuss the workings and policies of this vba pivot table filter site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Run Time Error 1004: Application-Defined or Object Defined Error up vote -1 down vote favorite I am trying to record a macro which will create pivot chart out of excel data and here is the code that has been recorded: Sub chart1() ' ' chart1 Macro ' ' Range("E1:F11").Select Sheets.Add In Debugger, code within the **** **** is shown in Yellow color ***** ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _ "data!R1C5:R11C6", Version:=xlPivotTableVersion12).CreatePivotTable _ TableDestination:="Sheet1!R3C1", TableName:="PivotTable1", DefaultVersion _ :=xlPivotTableVersion12 ******** Sheets("Sheet1").Select Cells(3, 1).Select With ActiveSheet.PivotTables("PivotTable1").PivotFields("question1") .Orientation = xlRowField .Position = 1 End With With ActiveSheet.PivotTables("PivotTable1").PivotFields("answer1") .Orientation = xlColumnField .Position = 1 End With ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _ "PivotTable1").PivotFields("answer1"), "Count of answer1", xlCount With ActiveSheet.PivotTables("PivotTable1").PivotFields("answer1") .Orientation = xlPageField .Position = 1 End With ActiveSheet.PivotTables("PivotTable1").PivotFields("answer1").Orientation = _ xlHidden With ActiveSheet.PivotTables("PivotTable1").PivotFields("answer1") .Orientation = xlColumnField .Position = 1 End With ActiveSheet.Shapes.AddChart.Select ActiveChart.SetSourceData Source:=Range("Sheet1!$A$3:$D$6") ActiveSheet.Shapes.AddChart.Select ActiveChart.SetSourceData Source:=Range("Sheet1!$A$3:$D$6") ActiveChart.ChartType = xlColumnClustered End Sub Why am I getting the Run Time Error 1004: Application-Defined or Object Defined Error when i try to run this macro ? Thanks in advance.. excel vba excel-vba share|improve this question edited Oct 25 '12 at 8:22 asked Oct 25 '12 at 7:12 Impasse 1,72111729 1 Is there a que
Forums Excel Questions PivotCache.Refresh not working Excel 2007 Page 1 of 2 12 Last Jump to page: Results 1 to 10 of 16 PivotCache.Refresh not working Excel 2007This is a discussion on PivotCache.Refresh not working Excel 2007 within the Excel Questions forums, part of the Question Forums category; Background on the issue: I have a pivot table with data coming from an external source. The external source actually ... LinkBack LinkBack URL About LinkBacks Bookmark & Share Digg this Thread!Add Thread to del.icio.usBookmark in TechnoratiTweet this thread Thread Tools Show Printable Version Display Linear Mode Switch to Hybrid Mode Switch http://stackoverflow.com/questions/13063418/run-time-error-1004-application-defined-or-object-defined-error to Threaded Mode Apr 7th, 2011,10:57 AM #1 Straha New Member Join Date Sep 2004 Posts 18 PivotCache.Refresh not working Excel 2007 Background on the issue: I have a pivot table with data coming from an external source. The external source actually references the same workbook that the pivot table is in. The source data is comprised of three dynamic named ranges on three worksheets. The tables are http://www.mrexcel.com/forum/excel-questions/541911-pivotcache-refresh-not-working-excel-2007-a.html joined via a SQL statement and then used by the pivot table. The external source/SQL join method is being used because the multiple consolidation ranges option of building the pivot table is limited and does not provide the level of detail necessary. The three tables can not be combined into one worksheet. Now the problem: The pivot table works correctly. Updates to the source data are reflected in the pivot upon manual refresh. However, I am trying to automatically refresh the pivot using the Worksheet.Activate method with this line of code: ActiveSheet.PivotTables("ptName").PivotCache.Refresh (This is the line of code provide by VBA when I recorded the macro using the manual refresh.) I receive an "Application-Defined or Object-Defined Error" when the sheet is activated. The code also fails when used as a stand-alone macro and not in the Worksheet.Activate method. I can manually refresh and update the pivot but not refresh with VBA. I think the issue centers around the fact that the external data reference is the same workbook. When the external source is another workbook the automatic refresh works and when the source is a table within the workbook (not external) the automatic refresh works. Has anyone experienced this problem before and/or have an
If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you http://www.vbforums.com/showthread.php?371428-1004-Application-defined-or-object-defined-error can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. Results 1 to 9 of 9 Thread: 1004: Application-defined or object-defined error Tweet Thread Tools Show Printable Version Subscribe to this Thread… Display Linear Mode Switch to Hybrid Mode Switch to Threaded Mode defined or Nov 16th, 2005,10:41 AM #1 Valleysboy1978 View Profile View Forum Posts Thread Starter Fanatic Member Join Date Nov 2004 Location Planet Xeoroaniar CC Posts:1,928,453,459,361 Posts 770 1004: Application-defined or object-defined error Code: Dim Sql As String Sql = "SELECT ICMAST.ICOMP, ICMAST.ICAT, ICMAST.ISCAT, SUM(IAC#1) AS A1,SUM(IAC#2) AS A2,SUM(IAC#3) AS A3,SUM(IAC#4) AS A4,SUM(IAC#5) AS A5,SUM(IAC#6) AS A6,SUM(IAC#7) AS A7,SUM(IAC#8) AS A8,SUM(IAC#9) defined or object AS A9,SUM(IAC#10) AS A10,SUM(IAC#11) AS A11,SUM(IAC#12) AS A12,SUM(IAC#13) AS A13,SUM(IAC#14) AS A14,SUM(IAC#15) AS A15,SUM(IAC#16) AS A16,SUM(IAC#17) AS A17,SUM(IAC#18) AS A18,SUM(IAC#19) AS A19,SUM(IAC#20) AS A20,SUM(IAC#21) AS A21,SUM(IAC#22) AS A22,SUM(IAC#23) AS A23,SUM(IAC#24) AS A24,SUM(IAC#25) AS A25,SUM(IAC#26) AS A26,SUM(IAC#27) AS A27,SUM(IAC#28) AS A28,SUM(IAC#29) AS A29,SUM(IAC#30) AS A30,SUM(IAC#31) AS A31,SUM(IAC#32) AS A32,SUM(IAC#33) AS A33,SUM(IAC#34) AS A34,SUM(IAC#35) AS A35,SUM(IAC#36) AS A36,SUM(IAC#37) AS A37,SUM(IAC#38) AS A38,SUM(IAC#39) AS A39,SUM(IAC#40) AS A40,SUM(IAC#41) AS A41,SUM(IAC#42) AS A42,SUM(IAC#43) AS A43,SUM(IAC#44) AS A44,SUM(IAC#45) AS A45,SUM(IAC#46) AS A46,SUM(IAC#47) AS A47,SUM(IAC#48) AS A48,SUM(IAC#49) AS A49,SUM(IAC#50) AS A50,SUM(IAC#51) AS A51," Sql = Sql & "SUM(IAC#52) AS A52,SUM(IAC#53) AS A53,SUM(IAC#54) AS A54,SUM(IAC#55) AS A55,SUM(IAC#56) AS A56,SUM(IAC#57) AS A57,SUM(IAC#58) AS A58,SUM(IAC#59) AS A59,SUM(IAC#60) AS A60,SUM(IAC#61) AS A61,SUM(IAC#62) AS A62,SUM(IAC#63) AS A63,SUM(IAC#64) AS A64,SUM(IAC#65) AS A65,SUM(IAC#66) AS A66,SUM(IAC#67) AS A67,SUM(IAC#68) AS A68,SUM(IAC#69) AS A69,SUM(IAC#70) AS A70,SUM(IAC#71) AS A71,SUM(IAC#72) AS A72,SUM(IAC#73) AS A73,SUM(IAC#74) AS A74,SUM(IAC#75) AS A75" Sql = Sql & " FROM " & Range("Library").Value & ".ICMNOM ICMNOM JOIN " & Range("Library").Value & ".ICMAST ICMAST ON ICMAST.ISERL = ICMNOM.ISERL GROUP BY ICMAST.ICOMP,ICAT, ISCAT" ActiveSheet.PivotTables("table1").PivotCache.Connection = "ODBC;DRIVER={iSeries Access ODBC Driver};SYSTEM=" & Range("Mac