Excel VBA：“应用程序定义或对象定义的错误”

Question

在Excel 2003中，此代码的最后一行（commandtext = abc）中出现运行时错误1004：“应用程序定义或对象定义的错误”

Sub SCommandTxt() 
Dim abc as string 
abc = Sheets("Totals").PivotTables("PivotTable2").PivotCache.CommandText 
Sheets("Totals").PivotTables("PivotTable2").PivotCache.CommandText = abc 
End Sub 

这不是我真正想做的事情，但不知道是什么原因导致了一个错误，因为这会让我变成一堵墙。手头的Pivot表是一个ODBC连接。以下代码在此代码之前运行并且正常工作。我真正想要做的是基于变化范围“WhereFilters”动态地改变查询。下面的查询工作正常，但我宁愿不必取消隐藏和选择工作表，并通过pivotwizard，如果我可以直接更改commandText（虽然基于我得到的错误可能不是......虽然其他人似乎认为以上是可能的，所以我不知道为什么它不工作对我来说）：

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 

感谢

Answer 1

你的问题似乎是这里所描述的确切一个：

Limitation of PivotCache.CommandText property

字符串有多长时间你试图设置为CommandText？