excel_vba_编程教程(完整版)-第97章
按键盘上方向键 ← 或 → 可快速上下翻页，按键盘上的 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提供�
