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

作者:
系列:办公自动化

查看该系列所有文章

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

' 更改数据表的来源
' wb:工作表对象
' connectionName:数据来源连接名称
' strSQL:新查询语句(修改SQL的查询代码)
' strSQLConnection:新连接语句(修改来源数据库,在对DBF数据库操作时非常有用)
' author: zhang@zhiqiang.org, 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: zhang@zhiqiang.org, 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.

系列: 办公自动化 »
在工作中定期或不定期会收到一些数据文件,然后要将它们的附件保存到自己的电脑上,下面演示如何让 Outlook 自动做这件事情。
编程 » vbs, 办公自动化
工作中或多或少有些琐碎的事情,比如每天要发送和接受数据,很多是通过邮件的方式传递的。之前写过 如何在 Outlook 里自动保存附件 ,这里再发一个如何更方便地发送带附件的邮件。
数学 » 数学家, 陶哲轩
下面是中国天津南开大学数学博士生 Xiaochuan Liu 翻译的陶哲轩的文章《做数学一定要是天才吗?》。陶老大自然是给出了否认的回答(否则会被骂死),但是这个问题呢,陶哲轩眼里的天才的定义可能不一样,他写这种文章是站着说话不腰疼。
最近关于 Google 的事情大家应该都知道了吧,让我们重温一下 CLIVE THOMPSON 在 Google 刚进入中国时对「信息审查」 的深度报道。此文于 2006 年 4 月份发表于纽约时报。