11

动态修改 Excel 数据表的数据来源

 3 years ago
source link: https://zhiqiang.org/coding/change-pivotcache-source.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

动态修改 Excel 数据表的数据来源

作者: 张志强

, 发表于 2010-01-13

, 共 2327 字 , 共阅读 151 次

系列：办公自动化

查看该系列所有文章

Excel 有一个很有用的功能是直接导入外部数据库或者使用外部数据源建立数据透视表和数据透视图。但比较可惜的是，这个数据源的查询语句是静态的，它无法根据日期自动修改（比如在应用中，我们希望每天获取的外部数据都是当天最新的数据），下面两个函数是修改外部数据源的 VBA 代码，调用它们就可以建立动态的数据源。

' 更改数据表的来源
' wb：工作表对象
' connectionName：数据来源连接名称
' strSQL：新查询语句（修改SQL的查询代码）
' strSQLConnection：新连接语句（修改来源数据库，在对DBF数据库操作时非常有用）
' author: [email protected], 2010
Public Sub ChangeODBCConnection(wb As Excel.Workbook, connectionName As String, _
        Optional strSQL As String = "", Optional strSQLConnection As String = "")

    With wb.Connections(connectionName).ODBCConnection
        If Len(strSQLConnection) Then .CommandText = SplitString(strSQLConnection)
        If Len(strSQL) Then .Connection = SplitString(strSQL)
    End With
    wb.Connections(connectionName).Refresh
End Sub

' 更改数据表的来源
' pc：数据透视表的PivotCache对象 （例如：ActiveSheet.PivotTables(1).PivotCache）
' connectionName：数据来源连接名称
' strSQL：新查询语句（修改SQL的查询代码）
' strSQLConnection：新连接语句（修改来源数据库，在对DBF数据库操作时非常有用）
' author: [email protected], 2010
Public Sub ChangePivotConnection(pc As Excel.PivotCache, Optional strSQLConnect, _
        Optional strSQL As String = "")
    Dim blnODBCConnect As Boolean

    With pc
        If .QueryType = xlODBCQuery Then
            blnODBCConnect = True
            If Len(strSQLConnect) = 0 Then strSQLConnect = .Connection
            strSQLConnect = Replace(strSQLConnect, "ODBC;DSN", "OLEDB;DSN", 1, 1, vbTextCompare)
        End If

        If StrComp(.Connection, strSQLConnect, vbTextCompare) <> 0 And Len(strSQLConnect) Then
            .Connection = strSQLConnect
        End If

        If StrComp(.CommandText, strSQL, vbTextCompare) <> 0 And Len(strSQL) Then
            .CommandText = (strSQL)
        End If

        If blnODBCConnect = True Then
            .Connection = Replace(.Connection, "OLEDB;DSN", "ODBC;DSN", 1, 1, vbTextCompare)
        End If

        .Refresh
    End With
End Sub

' 将字符串分割成短字符串的数组
Private Function SplitString(ByVal s As String) As Variant
    Dim ss() As Variant
    Dim i As Long

    ReDim ss(0 To Len(s) \ 200) ' note: it is not 256
    For i = 0 To UBound(ss)
        ss(i) = Array(Mid(s, i * 200 + 1, 200))
    Next i

    SplitString = ss
End Function

以上代码在 Office 2007 版通过测试，其余版本未知。

Q. E. D.


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK