excel-vba - 麻烦更改与vba的odbc连接的commandText

Question

我花了两天时间寻找解决方案，这让我发疯了...

首先让我解释一下我在做什么。我们将带来50万条记录，这些记录将驱动12个数据透视表。为了使工作簿的文件大小保持可管理，我直接从外部数据连接构建了数据透视表。这是我手动配置的odbc连接。

一切正常，我可以在工作簿中单击“全部刷新”，并且所有数据透视表都会自动更新，妙极了。

但是现在我需要能够使用手动开始和结束日期来限制整个记录集...更改数据透视表上的日期过滤器并不理想，因为它不仅是受结束日期影响的单个字段，还存在一些字段需要在数据透视之前进行计算的值，这些值取决于涉及结束日期的公式。

反复使整个Excel崩溃一整个下午后，我发现了以下限制：如果您的连接直接连接到数据透视表，则不能使用？和指向单元格引用的参数对话框，一旦关闭本书，单元格引用就会丢失。

所以我的下一个方法是这样做：

Dim ReportStartDate, ReportEndDate

' Get parameters from Intro sheet
ReportStartDate = "'" & ActiveWorkbook.Worksheets("Intro").Range("$B$1").Value & "'"
ReportEndDate = "'" & ActiveWorkbook.Worksheets("Intro").Range("$B$2").Value & "'"

' There are 3 directpivot odbc connections/caches that need to be modified.
' In each query, the default report-end-date is specified by CURDATE().
' The default report-start-date is specified as '2010-01-01'
' Replace these defaults with the values retrieved above.

Dim cn As WorkbookConnection
Dim odbcCn As ODBCConnection
Dim originalsqltext, newsqltext

For Each cn In ThisWorkbook.Connections     ' loop through the connections
    If cn.Type = xlConnectionTypeODBC Then
        Set odbcCn = cn.ODBCConnection
        originalsqltext = odbcCn.CommandText
        If odbcCn.Parent = "Calls" Then
            newsqltext = Replace(originalsqltext, "CURDATE()", ReportEndDate)
            newsqltext = Replace(newsqltext, "'2010-01-01'", ReportStartDate)
        ElseIf odbcCn.Parent = "Suboutcomes" Then
            newsqltext = Replace(originalsqltext, "CURDATE()", ReportEndDate)
            newsqltext = Replace(newsqltext, "'2010-01-01'", ReportStartDate)
        ElseIf odbcCn.Parent = "QtyCallsPerDay1" Then
            newsqltext = Replace(originalsqltext, "CURDATE()", ReportEndDate)
        Else
            newsqltext = originalsqltext
        End If
        odbcCn.CommandText = newsqltext
        odbcCn.Refresh
        odbcCn.CommandText = originalsqltext
    End If
    cn.Refresh ' refresh the other connection without modification
Next
Set cn = Nothing
Set odbcCn = Nothing

但是当它到达odbcCn.CommandText = newsqltext时，这使我出错
运行时错误'1004：
应用程序定义或对象定义的错误。
太没用了...

我验证了newsqltext是否包含了我的意图，它只是不会分配回CommandText。

经过一天的谷歌搜索和一些简单的宏录制实验后，更改CommandText看起来需要以下语法：

.CommandText = Array( _
"SELECT C1.CALLID, C1.TAKENON, C1A.TAKENAT, CAST(CONCAT(DATE_FORMAT(TAKENON,'%c/%e/%y'),' ',TIME_FORMAT(TAKENAT,'%H:" _
    , _
    "%i:%s')) AS CHAR) AS CallDateTime, YEAR(C1.TAKENON) AS Year, CEILING(MONTH(C1.TAKENON)/3) AS Quarter, MONTH(C1.TAKE" _
    , _

（我遗漏了其余部分，因为它很大）...起初我以为是我的问题，因为当我最初尝试记录宏时，遇到了“太多行继续”错误，因此我缩短了查询时间尽可能在替换之前将其降低到1428个字符。替换后，它最终为1448个字符...但是如何将其解析为代码所需的数组格式？还是有一些更好的方法来做到这一点？

我真的不想这样处理我的查询，只是为了能够使用vba编辑它们，而且我觉得我只是缺少一些有关如何更改CommandText的东西。

我的搜索出现了一些令人烦恼的事情，例如这个问题，除非您先将其更改为oledb，否则无法更改odbc连接上的CommandText，然后可以更改CommandText，然后将连接更改回odbc ...但是早于Excel 2010，该版本不再使用这些... http://p2p.wrox.com/excel-vba/29037-cant-set-commandtext-property-if-cache-has-1-rpt.html

链接到其中的Knowledgebase文章 http://support.microsoft.com/kb/816562更加令人震惊……当我看到StringToArray函数时，我以为自己正在寻找解决方案，但随后我进一步阅读并看到


注意如果您使用共享的数据透视缓存，基于OLAP的数据透视表或基于多个合并范围的数据透视表连接到数据库，则先前的代码可能无法按预期工作。


然后


如果工作表上的多个数据透视表是从同一个数据透视表派生的，则子例程在处理第一个数据透视表后将无法工作。截至2003年3月，没有已知的解决方法。


尽管它指出该文章仅适用于Excel 2000至2003。

我尝试了另一件事，我想也许可以使用？参数，然后使用vba进行设置...但是当我创建带有参数的简单查询时，当我将参数指向新的单元格引用时记录了宏，该宏仅包含以下内容：
子PARAMEDIT5（）
'
'PARAMEDIT5宏
'

'
    With ActiveWorkbook.Connections("PARAMEDIT").ODBCConnection
        .BackgroundQuery = False
        .CommandText = Array("SELECT * FROM Calls1 where TAKENON > ?" _
        )
        .CommandType = xlCmdSql
        .Connection = _
        "ODBC;DRIVER={MySQL ODBC 5.1 Driver};UID=xxxxxxx;PWD=xxxxxxxx;SERVER=xxxxxx;PORT=3306;BIG_PACKETS=1;"
        .RefreshOnFileOpen = False
        .SavePassword = True
        .SourceConnectionFile = ""
        .SourceDataFile = ""
        .ServerCredentialsMethod = xlCredentialsMethodIntegrated
        .AlwaysUseConnectionFile = False
    End With
    With ActiveWorkbook.Connections("PARAMEDIT")
        .Name = "PARAMEDIT"
        .Description = ""
    End With
    ActiveWorkbook.Connections("PARAMEDIT").Refresh
End Sub

我尝试使用直接输入数据类型的连接以及连接到外部数据源的常规表来进行此操作，我知道它确实支持参数。

那么...有谁知道为共享数据透视缓存odbc连接参数化查询的正确方法是什么？

更新：
我尝试了这个：

Dim cn, originalCn, newCn As WorkbookConnection
Dim odbcCn As ODBCConnection
Dim originalsqltext, newsqltext
Dim connStr As String

For Each cn In ThisWorkbook.Connections     ' loop through the connections
    If cn.Type = xlConnectionTypeODBC Then
        Set odbcCn = cn.ODBCConnection
        originalsqltext = odbcCn.CommandText
        Set originalCn = cn
        connStr = odbcCn.Connection
        Select Case odbcCn.Parent
            Case "Calls", "Suboutcomes"
                newsqltext = Replace(originalsqltext, "CURDATE()", ReportEndDate)
                newsqltext = Replace(newsqltext, "'2010-01-01'", ReportStartDate)
            Case "QtyCallsPerDay1"
                newsqltext = Replace(originalsqltext, "CURDATE()", ReportEndDate)
            Case Else
                newsqltext = originalsqltext
        End Select
        Set newCn = ActiveWorkbook.Connections.Add(odbcCn.Parent & "New", "WhoCares", connStr, newsqltext)
        Set cn = newCn
        cn.Refresh
        Set cn = originalCn
        newCn.Delete
    Else
        cn.Refresh ' refresh any other connections without modification
    End If
Next

Set cn = Nothing
Set odbcCn = Nothing
Set newCn = Nothing
Set originalCn = Nothing

尽管它似乎可以使命令文本达到我想要的目的，但是cn.Refresh在我逐步执行时什么也不做。如果它正在刷新，但我的数据透视表没有更新，则可以看到他们在哪里寻找Calls1，并在刷新发生的那一刻将其命名为Calls1New，但连接只是不执行任何操作（查询通常需要几分钟即可完成）。或者，也许我无法将其分配给具有相同名称的现有连接？在设置cn = newCn之后，它们看起来都完全相同，并且名称相同。

我会再介绍一下，但是如果其他人做了类似的事情，我将不胜感激。非常感谢您到目前为止所提供的！

编辑：所以我回到原来的

odbcCn.CommandText = newsqltext
cn.Refresh
odbcCn.CommandText = originalsqltext

我也试过
odbcCn.CommandText = StringToArray（newsqltext）
cn。刷新
odbcCn.CommandText = StringToArray（原始sqltext）
我在 http://support.microsoft.com/kb/816562上找到的。没人工作。

我将张贴原始sqltext和newsql文本，因为它们恰好在错误之前。请注意，如果我手动将originalsqltext粘贴到查询对话框中，则newsqltext也可以正常工作

**由于新信息，删除了先前的编辑**

注意-我发现一个线程 Excel VBA: Update Pivot Sourcedata似乎是一个类似的问题-因为我已经测试过尝试分配odbcCn.CommandText = originalsqltext（未作任何更改），并且它也失败了。但是，该线程来自2009年，因此很可能不使用excel 2010，因为我尝试编写

For Each pvtC In ThisWorkbook.PivotCaches
    name = pvtC.WorkbookConnection.name
    originalsqltext = pvtC.CommandText
    pvtC.CommandText = originalsqltext
Next

而且它也会在pvtC.CommandText = originalsqltext上失败

更新：
我现在确定这与查询本身无关，而是具有多个枢纽分析表指向同一个枢轴缓存的条件。
我用简单的查询创建了一个新的外部数据源

SELECT * FROM clientdashboard1.Calls1 WHERE TAKENON BETWEEN '2010-01-01' AND CURDATE()

作为其查询。我将连接命名为AlphaTest，并从中创建了一个数据透视表，然后将该数据透视表复制到另一个工作表并使用了不同的字段。我修改了代码以首先运行此代码：

For Each pvtC In ThisWorkbook.PivotCaches
    name = pvtC.WorkbookConnection.name
    If name = "AlphaTest" Then
        originalsqltext = pvtC.CommandText
        pvtC.CommandText = originalsqltext
    End If
Next

它在完全相同的位置失败，pvtC.CommandText = originalsqltext

然后我移开了第二个数据透视表，然后再次走过去，它成功了。

然后只是为了踢球，我输入了我最初的庞大查询，然后再次进行。有效。但是，它发现了另一个难题……通过代码更改CommandText会使它刷新。因此，我原来的计划，即进行替换，刷新，然后重新设置为原始计划，将无法正常工作，因为表将在第二次分配时再次刷新（如果可行的话）。

更新这只会越来越好。我虽然要制作我的数据透视表缓存的虚拟副本，也许只有一条单条记录，将每个数据透视表指向其自己的缓存，然后让vba更改“真实”查询的查询，然后遍历并将每个数据透视表指向之一。执行报告任务（复制工作表，断开链接，另存为，关闭是我们通常的工作方式）。然后回到原始书中，将所有数据透视表指向各自的虚拟缓存。好吧，您知道我拥有的AlphaTest数据源吗？我以为pvtC.CommandText = originalsqltext实际上更改了查询并导致AlphaTest刷新...哦，没有我的朋友。它创建了一个名为Connection的新连接，它是AlphaTest的副本。
好的。我该如何使用呢？ .....我有一些想法要尝试，但是如果其他人已经解决了，请....我要尝试类似Tim所建议的方法...就是这样我将无法直接更改与表连接的数据透视表，我必须将它们钩接到默认的共享连接，具有可编辑的连接（未连接数据透视表），并使用我的参数对其进行编辑，将每个数据透视表指向该表，执行报告任务，然后将数据透视表指向默认的共享连接...如果可以，我将非常高兴。

Answer 1

在进行了大量在线研究之后……我发现这是更新ODBC连接的CommandText属性时的错误。如果您暂时切换到OLEDB连接，请更新您的CommandText属性，然后再切换回ODBC，它不会创建新的连接。不要问我为什么...这对我有用。

创建一个新模块并插入以下代码：

Option Explicit

Sub UpdateWorkbookConnection(WorkbookConnectionObject As WorkbookConnection, Optional ByVal CommandText As String = "", Optional ByVal ConnectionString As String = "")

With WorkbookConnectionObject
    If .Type = xlConnectionTypeODBC Then
        If CommandText = "" Then CommandText = .ODBCConnection.CommandText
        If ConnectionString = "" Then ConnectionString = .ODBCConnection.Connection
        .ODBCConnection.Connection = Replace(.ODBCConnection.Connection, "ODBC;", "OLEDB;", 1, 1, vbTextCompare)
    ElseIf .Type = xlConnectionTypeOLEDB Then
        If CommandText = "" Then CommandText = .OLEDBConnection.CommandText
        If ConnectionString = "" Then ConnectionString = .OLEDBConnection.Connection
    Else
        MsgBox "Invalid connection object sent to UpdateWorkbookConnection function!", vbCritical, "Update Error"
        Exit Sub
    End If
    If StrComp(.OLEDBConnection.CommandText, CommandText, vbTextCompare) <> 0 Then
        .OLEDBConnection.CommandText = CommandText
    End If
    If StrComp(.OLEDBConnection.Connection, ConnectionString, vbTextCompare) <> 0 Then
        .OLEDBConnection.Connection = ConnectionString
    End If
    .Refresh
End With

End Sub

此 UpdateWorkbookConnection子例程仅适用于更新OLEDB或ODBC连接。该连接不一定必须链接到数据透视表。它还解决了另一个问题，即使有多个基于同一连接的数据透视表，也可以更新连接。

要启动更新，只需使用连接对象和命令文本参数调用函数，如下所示：

UpdateWorkbookConnection ActiveWorkbook.Connections("Connection"), "exec sp_MyAwesomeProcedure"

您也可以选择更新连接字符串。

我从不使用 ?参数，因此我无法真正确定这是否可以解决您的问题，但我怀疑会解决。我总是只使用字符串串联将参数直接插入CommandText字符串中。