Excel作为一款非常实用的前端数据分析工具，往往需要使用到外部数据，访问后台数据库的操作，譬如：某个数据库的某张业务表。基本实现思路是1）建立外部数据源 2）设置外部数据源的属性，并将其绑定至某个数据表/透视表/图等。这里，我们设计一个示例，大致了解下这个实现过程以及VBA的简单应用。

示例：利用VBA代码实现销售数据透视表按某个地区的更新。譬如：选择上海，透视表显示上海地区最新的销售信息。

说明：

1）报表业务的数据量往往很巨大，如果直接向外部获取所有数据，刷新时间可能需要较长时间完成，这个示例主要考虑了如何按某些特定条件检索外部/后台数据。

2）示例环境：Excel 2010/SQL Server 2k8，事先建立Northwind数据库，并在该库中创建一张名为[Report].[Sales]的数据表。

http://s16/middle/62c0483cg95dc172a1e2f&690VBA实现自动刷新透视表" TITLE="【软件】Excel VBA实现自动刷新透视表" />

关键步骤：

1 使用下拉组合框（Combox）设计地区下拉菜单

两个比较重要的属性：

1）ListFillRange 下拉菜单中显示的地区列表

2）LinkedCell    将选中的地区显示在某个单元格中,这里是C8

http://s12/middle/62c0483cg95dc715ef58b&690VBA实现自动刷新透视表" TITLE="【软件】Excel VBA实现自动刷新透视表" />

2 在Excel中，建立名为Sales的连接，设置该连接获取[Report].[Sales]表所有数据的属性，再将这个数据源关联到一个数据透视表上。具体步骤（略）

3 由于数据的刷新是选了某个地区后发生的，编写ComboBox Change事件代码。

VBA代码：

Private Sub ComboBox1_Change()
'Author       : http://blog.sina.com.cn/lightonlife
'Macro purpose: automatic to refresh pivot table

Dim strSQL As String

Application.ScreenUpdating = False
Application.StatusBar = ""

Sheets("Sheet1").Select
strSQL = Sheets("Sheet1").Range("$J$8")

With ActiveWorkbook.Connections("Sales").OLEDBConnection
        .BackgroundQuery = False
        .CommandText = strSQL
        .CommandType = xlCmdSql
        .Connection = "OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=Northwind;Data Source=Servername;"
        .RefreshOnFileOpen = False
        .SavePassword = False
        .SourceConnectionFile = ""
        .ServerCredentialsMethod = xlCredentialsMethodIntegrated
        .AlwaysUseConnectionFile = False
End With

With ActiveWorkbook.Connections("Sales")
        .Name = "Sales"
        .Description = ""
End With
   
Sheets("Sheet1").PivotTables("PivotTable1").RefreshTable
Sheets("Sheet1").Select
ActiveWorkbook.RefreshAll
End Sub

1）运用了一个简化代码的技巧，获得检索后台数据的条件

代码strSQL = Sheets("Home").Range("$J$8")中J8单元格在Excel中设置的公式为：

="select * from Report.Sales where Area =  '" & C8 & "'"

C8是所选的地区。

2）上述代码大意是打开名为Sales的连接，对某服务器Northwind数据库执行诸如

select * from Report.Sales where Area = 'Shanghai'的查询语句，并刷新与其关联的数据透视表。

3）改变数据库访问引擎，还可访问其他外部/数据源，例如：Access数据库等。