按键盘上方向键 ← 或 → 可快速上下翻页，按键盘上的 Enter 键可回到本书目录页，按键盘上方向键 ↑ 可回到本页顶部！
————未阅读完？加入书签已便下次继续阅读！
Sub ExportData（）
Set objAccess = CreateObject（〃Access。Application〃）
objAccess。OpenCurrentDatabase filepath：= _
〃C：Program FilesMicrosoft OfficeOffice” _
& 〃SamplesNorthwind。mdb〃
objAccess。DoCmd。TransferSpreadsheet _
TransferType：=acExport； _
SpreadsheetType：=acSpreadsheetTypeExcel9； _
TableName：=〃Shippers〃； _
Filename：=〃C：Shippers。xls〃； _
HasFieldNames：=True； _
Range：=〃Sheet1〃
objAccess。Quit
Set objAccess = Nothing
End Sub
（译者：原文为acSpreadsheetTypeExcel10运行失败）
图15…12 使用TransferSpreadsheet方法可以将Access表里的数据导出到Excel电子表格里
19。使用 OpenDatabase 方法
Excel 2002提供了一个操纵数据库的新方法，OpenDatabase方法，应用于Workbooks集合，是将
数据库数据导入Excel电子表
格最容易的方法。该方法要求你明确你想要打开的数据库文件名称。下面的示例过程打开位于
C：Program FilesMicrosoft
OfficeOffice10Samples文件夹里的Northwind数据库。当你运行该过程，Excel显示一个对
话框，列出了该数据库里的所有表和 查询（参见图15…13）。从列表里选择后，就会打开一
个全新的工作簿，显示被选上的表或者查询里的数据。
Sub OpenAccessDatabase（）
Workbooks。OpenDatabase _
Filename：=〃C：Program FilesMicrosoft Office” _
& 〃Office10SamplesNorthwind。mdb〃
End Sub
307
… 页面 324…
图15…13 使用带一个参数（数据库文件名称）的OpenDatabase方法允许从一个列表框里选择一
个表或者查询
图15…14 使用Excel 2002里新增的OpenDatabase方法可以轻易地将储存在表或者查询里的数据
库数据导入Excel工作簿
OpenDatabase方法有四个可选参数，可供你进一步限定你要获取的数据：
OpenDatabase方法的可选参数 数据类型 描述
mandText Variant SQL查询字符串。参见使用该参数的
示例
mandType Variant 查询的命令类型。可
用的命令类型有：Deault，SQL和表 BackgroundQuery
Variant 查询的背景。可以是以下常数之
一：PivotCache或者QueryTable ImportDataAs
Variant 明确查询的格式。使用
xlQueryTable报告创建一个查询表，或者
xlPivotTableReport来创建一个数据透视表
接下来的示例过程示范了如何使用带可选参数的OpenDatabase方法。该过程从获取的客户记录
创建了一个数据透视表。当你运 行该过程时，Excel就会基于提供的查询的字符串显示一个可
用字段的列表。你可以拖曳一个或者多个字段到该透视表中，以创 建数据透视报告。图15…15
显示了按国家分类的CustomerId字段。
Sub CountCustomersByCountry（）
Workbooks。OpenDatabase _
Filename：=〃C：Program FilesMicrosoft Office” _
& 〃Office10SamplesNorthwind。mdb〃； _
mandText：=〃Select * from Customers〃； _
BackgroundQuery：=PivotTable； _
ImportDataAs：=xlPivotTableReport
End Sub
308
… 页面 325…
（译者：Excel 2003＋Access 2002运行该过程有问题：BackgroundQuery： = PivotTable。 此
处有矛盾，上面的参数解释说该参 数为PivotCache或者QueryTable，而这里却是PivotTable。
这三个参数均导致错误。搜索对象浏览器说该参数为布尔类型。译 者将该参数改为…1，1，2，
10，True，False等运行，结果没有 区别。）
图15…15 使用OpenDatabase方法的可选参数，你可以明确获取数据库数据到一个特定的格式，
例如数据透视报告或者查询表报告
20。从 Access 数据创建文本文件
你可以使用Excel的VBA过程从Access数据创建一个以逗号或者tab分开的文本文件。文本文件
对于传输大量数据到电子表格特 别有用。下面的示例程序示范了如何从一个ADO recordset创
建一个tab分开的文本文件。为了确保该过程运行正确，你必须创建
对Microsoft ActiveX Data Objects 2。6 Library的引用。参考第八章中操作文本文件的详细
信息。运行该过程后，请在Excel
里打开C：ProductsOver50。txt
Sub CreateTextFile（）
Dim strPath As String
Dim conn As New ADODBnnection
Dim rst As ADODB。Recordset
Dim strData As String
Dim strHeader As String
Dim strSQL As String
strPath = 〃C：Program FilesMicrosoft Office” _
& 〃OfficeSamplesNorthwind。mdb〃
conn。Open 〃Provider=Microsoft。Jet。OLEDB。4。0；〃 _
& 〃Data Source=〃 & strPath & 〃；〃
conn。CursorLocation = adUseClient
strSQL = 〃SELECT * FROM Products WHERE UnitPrice 》 50〃
Set rst = conn。Execute（mandText：=strSQL； Options：=adCmdText）
'save the recordset as a tab…delimited file
strData = rst。GetString（StringFormat：=adClipString； _
ColumnDelimeter：=vbTab； _
RowDelimeter：=vbCr； _
nullExpr：=vbNullString）
Open 〃C：ProductsOver50。txt〃 For Output As #1
For Each f In rst。Fields
strHeader = strHeader ＋ f。Name & vbTab
Next
Print #1； strHeader
Print #1； strData
Close #1
End Sub
（译者：如果为强制要求声明对象则还需声明变量f。） 在第八章中，你学习了如何使用
FileSystemObject操作文本文件。下面的过程演示了如何使用该对象来创建一个名为
309
… 页面 326…
ProductsOver100。txt的文本文件：
Sub CreateTextFile2（）
Dim strPath As String
Dim conn As New ADODBnnection
Dim rst As ADODB。Recordset
Dim strData As String
Dim strHeader As String
Dim strSQL As String
Dim fso As Object
Dim myFile As Object
Set fso = CreateObject（〃Scripting。FileSystemObject〃）
Set myFile = fso。CreateTextFile（〃C：ProductsOver100。txt〃； True）
strPath = 〃C：Program FilesMicrosoft Office” _
& 〃OfficeSamplesNorthwind。mdb〃
conn。Open 〃Provider=Microsoft。Jet。OLEDB。4。0；〃 _
& 〃Data Source=〃 & strPath & 〃；〃
conn。CursorLocation = adUseClient
strSQL = 〃SELECT * FROM Products WHERE UnitPrice 》 100〃
Set rst = conn。Execute（mandText：=strSQL； Options：=adCmdText）
'save the recordset as a tab…delimited file
strData = rst。GetString（StringFormat：=adClipString； _
ColumnDelimeter：=vbTab； _
RowDelimeter：=vbCr； _
nullExpr：=vbNullString）
For Each f In rst。Fields
strHeader = strHeader ＋ f。Name & vbTab
Next
With myFile
。WriteLine strHeader
。WriteLine strData
。Close
End With
End Sub
（译者：如果为强制要求声明对象则还需声明变量f。）
图15…16 因为文本文件可以轻易地在Excel打开，所以你可以使用它在Access和Excel之间传输
数据
21。从 Access 数据创建查询表
如果你想要在Excel使用来自外部的数据源，而且你知道你将使用的数据会经常改变，那么你需
要创建一个查询表。查询表是Excel 工作表里的特殊表，它链接到外部数据源，例如Access数
据库，SQL服务器，网页或者文本文件。用户可以轻易地刷新查询表 来获取最更新的信息。
Excel提供了专门的菜单选项来获取外部数据：只要选择“数据”｜“导入外部数据”，并选
择“新数据库 查询”。通过查询外部数据库，你可以带来一些正好适合你要求得数据。例如，
不必将所有的产品信息都带入你的电子表格来回 顾，你只要在获取数据之前明确数据必须达
到的条件就行。因此，你可以只获取单价大于20美金的产品，而不是从Access导入 所有的产
品。 在VBA里，你可以使用QueryTable对象访问外部数据。每个QueryTable代表从外部数据
源例如SQL服务器或者Access数据库创 建的工作表表格。要编程创建一个查询的话，你可以
使用QueryTabes集合对象的Add方法。该方法要求三个参数。本章结尾处 的示例过程使用下
述语句在活动工作表上创建一个查询表：
Set myQryTable = ActiveSheet。QueryTables。Add（strConn； Dest； strSQL） strConn是为第
一个参数——Connection提供数值的变量。它是必须的参数，为Variant数据类型，明确查询
表数据源。 Dest是为第二个参数——Destination提供数值的变量。这是个必须的参数，为
Range数据类型，明确在哪个单元格放置查询表。 strSQL是为第三个参数——SQL提供数值的
变量。这是个必须的参数，为字符串数据类型，定义要从查询返回的数据。 当你使用Add方法
创建查询时，该查询不会运行，直到你调用Refresh方法。该方法接受一个参数——
BackgroundQuery。这是 一个Variant数据类型的可选参数，允许你决定是否在建立了对数据
库的链接以及查询被提交（True）后将控制返回给过程，或 者在查询已经运行并且所有数据
已经获取到工作表里了（False）才将控制返回给过程。 接下来的过程CreateQueryTable仅仅
从Northwind数据库获取产品单价大于20的Products表中的产品。注意，该过程仅在所有相 关
记录都被获取之后，控制才交回给过程。方法RefreshStyle决定数据如何插入工作表。下述常
310
… 页面 327…
数可供使用：
* xlOverwriteCells – 现存的单元格会被新数据覆盖
* xlInsertDeleteCells – 插入或者删除单元格以容纳新数据
* xlInsertEntireRows – 插入整行以容纳新数据
Sub CreateQueryTable（）
Dim myQryTable As Object
Dim myDb As String
Dim strConn As String
Dim Dest As Range
Dim strSQL As String
myDb = 〃C：Program FilesMicrosoft OfficeOffice” _
& 〃SamplesNorthwind。mdb〃
strConn = 〃OLEDB；Provider=Microsoft。Jet。OLEDB。4。0；〃 _
& 〃Data Source=〃 & myDb & 〃；〃
Set Dest = Worksheets（1）。Range（〃A1〃）
strSQL = 〃SELECT * FROM Products WHERE UnitPrice》20〃
Set myQryTable = ActiveSheet。QueryTables。Add（strConn； _
Dest； strSQL）
With myQryTable
。RefreshStyle = xlInsertEntireRows
。Refresh False
End With
End Sub
图15…17 使用QueryTable对象可以在Excel里分析来自外部数据源例如Access数据库的数据
22。在 Excel 里使用 Access 数据
使用上面讨论过的方法之一从Access数据库获取数据之后，你可以使用许多Excel内置的工具
来分析该数据。基于获取的信息来 创建一些图表经常是很有用的。
23。用 Access 数据创建内嵌图表
使用VBA，你可以轻松的基于从Access数据库获取的数据创建图表。下面显示的ChartData过程
使用从Access Northwind数据里 获取的数据创建了一个内嵌图表。该图表由Charts集合的Add
方法创建。图表的数据源由Range对象提供。CurrentRegion方法 返回单元格A1周围的所有非
空单元格。过程的剩余部分则通过设置图表的各种属性来设置图表格式。图表代码部分录制在
一个 分开的宏里，然后移到该VBA过程里并作一些修改以设置一些图表属性。
Sub ChartData（）
Dim db As DAO。database
Dim qd As DAO。QueryDef
Dim rs As DAO。Recordset
Dim mySheet As Worksheet
311
… 页面 328…
Dim recArray As Variant
Dim i As Integer
Dim j As Integer
Dim pathDb As String
Dim qdName As String
pathDb = 〃C：Program FilesMicrosoft Office” _
& 〃OfficeSamplesnorthwind。mdb〃
qdName = 〃Category Sales for 1997〃
Set db = OpenDatabase（pathDb）
Set qd = db。QueryDefs（qdName）
Set rs = qd。OpenRecordset
Set mySheet = Worksheets（〃Sheet2〃）
With mySheet。Range（〃A1〃）
。CurrentRegion。Clear
recArray = rs。GetRows（rs。RecordCount）
For i = 0 To UBound（recArray； 2）
For j = 0 To UBound（recArray； 1）
。Offset（i ＋ 1； j） = recArray（j； i）
Next j
Next i
For j = 0 To rs。Fieldsunt … 1
。Offset（0； j） = rs。Fields（j）。Name
。Offset（0； j）。EntireColumn。AutoFit
Next j
End With
mySheet。Activate
Charts。Add
ActiveChart。ChartType = xl3DColumnClustered
ActiveChart。SetSourceData _
Source：=mySheet。Cells（1； 1）。CurrentRegion； PlotBy：=xlRows
ActiveChart。Location Where：=xlLocationAsObject； Name：=mySheet。Name
With ActiveChart
。HasTitle = True
。ChartTitle。Characters。Text = qdName
。Axes（xlCategory）。HasTitle = True
。Axes（xlCategory）。AxisTitle。Cha