In Excel 2003, I get Runtime error 1004: "application-specific or object error" in the last line of this code (commandtext = abc)
Sub SCommandTxt() Dim abc as string abc = Sheets("Totals").PivotTables("PivotTable2").PivotCache.CommandText Sheets("Totals").PivotTables("PivotTable2").PivotCache.CommandText = abc End Sub
Actually, this is not what I am trying to do, but not knowing what causes the error into something simple, because it pushes me against the wall. The Pivot table is an ODBC join. The following code has been run before this code and works fine. All I really want to do is dynamically change the query based on the "WhereFilters" changing range. The following query works well, but I would prefer not to display and select the sheet and go through the pivotwizard if I can just change the commandText directly (although based on the errors I get, maybe not ... Although others seem to think this is maybe that's why I don’t know why it doesn’t work for me):
Sub UpdatePvt() Dim DBDir As String, DBName As String, SortType As String, Size As String Dim QueryArry1(0 To 100) As String, rng As Range, x As Integer DBDir = "C:\Documents and Settings\jt\" DBName = "DatabaseExample.mdb" If Range("ComboResult1") = 1 Then SortType = "TDollars" Sheets("Totals").PivotTables("PivotTable1").PivotFields("DIV_ID").AutoSort _ xlDescending, "Sum of Dollars" Sheets("Totals").PivotTables("PivotTable2").PivotFields("DIV_ID").AutoSort _ xlDescending, "Sum of Dollars" Else SortType = "TCounts" Sheets("Totals").PivotTables("PivotTable1").PivotFields("DIV_ID").AutoSort _ xlDescending, "Sum of Counts" Sheets("Totals").PivotTables("PivotTable2").PivotFields("DIV_ID").AutoSort _ xlDescending, "Sum of Counts" End If If Range("ComboResult2") = 1 Then Size = "Total" ElseIf Range("ComboParOUT") = 2 Then Size = "Small" Else Size = "Large" End If QueryArry1(0) = "SELECT Top 500 C.* " QueryArry1(1) = "FROM Final03 C " x = 2 If Not (Range("NoFilters")) Then QueryArry1(x) = "INNER JOIN (Select DIV_ID FROM FullLookup WHERE " x = x + 1 For Each rng In Range("WhereFilters") QueryArry1(x) = rng.Value x = x + 1 Next rng QueryArry1(x) = "GROUP BY DIV_ID) E ON C.DIV_ID = E.DIV_ID " x = x + 1 End If QueryArry1(x) = "WHERE C.EntitySize = '" & Size & "' " QueryArry1(x + 1) = "ORDER BY C." & SortType & " DESC " 'Example Query Results: 'SELECT Top 500 C.* FROM Final03 C INNER JOIN (Select DIV_ID FROM FullLookup WHERE Year = 2008 and State = 'MN' and Type = 'RST44' GROUP BY DIV_ID) E ON C.DIV_ID = E.DIV_ID WHERE C.EntitySize = 'Large' ORDER BY C.TCounts DESC Sheets("Totals").Visible = xlSheetVisible Sheets("Totals").Select Sheets("Totals").PivotTables("PivotTable1").DataBodyRange.Select Sheets("Totals").PivotTableWizard SourceType:=xlExternal, _ SourceData:=QueryArry1, _ Connection:=Array( _ Array("ODBC;DSN=MS Access Database;DBQ=" & DBDir & "\" & DBName & ";"), _ Array("DefaultDir=" & DBDir & ";DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;") _ ) Sheets("Totals").PivotTables("PivotTable2").DataBodyRange.Select Sheets("Totals").PivotTableWizard _ SourceType:=xlPivotTable, _ SourceData:="PivotTable1" Sheets("Totals").Visible = xlSheetHidden End Sub
thanks
source share