Excel VBA:“应用程序定义或对象定义的错误” [英] Excel VBA: "application-defined or object-defined error"

查看:695
本文介绍了Excel VBA:“应用程序定义或对象定义的错误”的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

在Excel 2003中,我遇到运行时错误1004:应用程序定义或对象定义错误在此代码的最后一行(commandtext = abc)

In Excel 2003 I'm getting a Runtime error 1004: "application-defined or object-defined error" on 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


$ b b

这不是我想要做的,但不知道是什么原因导致一个错误在一个简单的,因为这推动我一堵墙。手头的数据透视表是一个ODBC连接。以下代码在此代码之前运行并且工作正常。所有我真正想要做的是根据变化的范围WhereFilters动态地更改查询。下面的查询工作正常,但我不想取消隐藏和选择工作表,并通过pivotwizard如果我可以直接更改commandText(虽然基于错误,我可能不会...虽然其他似乎认为以上是可能的,所以我不知道为什么它不为我工作):

This isn't really what I'm trying to do, but not knowing what is causing an error in something as simple as this is driving me up a wall. The Pivot table at hand is an ODBC connection. The following code was run before this code and works fine. All I really want to do is change the query dynamically based on the changing range "WhereFilters". The below query works alright, but I'd prefer not to have to unhide and select the sheet and go through the pivotwizard if I can just change the commandText directly (though based on the errors I'm getting maybe not... Though others seem to think the above is possible, so I don't know why it isn't working 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

感谢

推荐答案

您的问题似乎是这里描述的:

Your problem appears to be the exact one described here:

限制PivotCache.CommandText属性

您尝试设置为CommandText的字符串有多长?

How long is the string you're trying to set as CommandText?

这篇关于Excel VBA:“应用程序定义或对象定义的错误”的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
相关文章
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆