excel_vba_编程教程(完整版)-第87部分
按键盘上方向键 ← 或 → 可快速上下翻页，按键盘上的 Enter 键可回到本书目录页，按键盘上方向键 ↑ 可回到本页顶部！
————未阅读完？加入书签已便下次继续阅读！
& 〃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。Characters。Text = 〃〃
。Axes（xlSeries）。HasTitle = False
。Axes（xlValue）。HasTitle = True
。Axes（xlValue）。AxisTitle。Characters。Text = mySheet。Range（〃B1〃） _
& 〃（）〃
。Axes（xlValue）。AxisTitle。Orientation = xlUpward
End With
db。Close
End Sub
过程ChartData的运行结果显示在图15…18上。
312
… 页面 329…
图15…18 你可以使用VBA编程基于从Access表，查询或者SQL语句获取的数据创建内嵌图表
24。传输 Excel 电子表格到 Access 数据库
世界上许多大的数据库都是从电子表格开始的。当你需要从你的电子表格创建一个数据库应
用软件时，你可以求助于缓慢呆滞的 手动方法来传输数据，你也可以使用你新学的VBA编程技巧
自动将你的电子表格变为数据库表。一旦在数据库格式了，你的Excel 数据就可以使用在高级的
公司范围的报告上使用，或者作为一个独立的应用软件使用（不必多言，后面的要求就是你要拥
有数据 库应用软件的设计技巧）。本章剩余的部分将示范如何将Excel电子表格链接和导入到
Access数据库。在你移到Excel数据到 Access之前，你应该尽可能地整理好数据，这样，传输
操作就会顺利。请牢记，电子表格里你要传输的每一行都会变为表中的 一个记录，而每一列
都会作为表的字段。因为这个原因，你计划传输到Access的电子表格的第一行应该包含字段名
称。你要传 输的数据列中间应该没有空白。换句话说，你的数据应该是连续的。如果你要传输
的数据有大量的列，那么应该先打印出你的数据并检查确保以后没有意外。如果你的数据的第
一列为字段名称，那么建议你使用内置的Transpose函数先将数据转置，以确保 数据是从上到
下，而不是从左到右。让你数据顺利导入的关键是使你的电子表格尽可能象数据库表。
25。将 Excel 电子表格链接到 Access 数据库
你可以使用TransferSpreadsheet方法将Excel电子表格链接到Access数据库（参见本章“使
用TransferSpreadsheet方法获取数 据”部分有关该方法的使用详情）。下面的示例过程将图
15…19显示的电子表格链接到Northwind数据库。在使用 OpenCurrentDatabase方法打开Access
数据库之后，该过程从Chap15。xls电子表格文件的mySheet工作表中位于单元格（A1：D7） 区域
里，使用Access DoCmd对象的TransferSpreadsheet方法创建了一个名为ExcelSheet的链接表。
注意，DoCmd语句中的参 数…1表明电子表格的第一行包含列标题。接着，该过程打开该链接的
表为编辑模式，因此用户可以添加或者修改数据。如果在添 加一条或者多条记录后，你要更
改回到Excel，那么你将注意到在链接的Access表里作的改变将在Excel里立即可用。
图15…19 VBA过程LinkExcel_ToAccess将电子表格链接到Access的Northwind数据库
Sub LinkExcel_ToAccess（）
Dim objAccess As Access。Application
Dim strName As String
strName = 〃Linked_ExcelSheet〃
Set objAccess = New Access。Application
With objAccess
。OpenCurrentDatabase 〃C：Program FilesMicrosoft Office” _
& 〃OfficeSamplesNorthwind。mdb〃
。DoCmd。TransferSpreadsheet acLink； acSpreadsheetTypeExcel9； _
strName； _
〃C：Chap15。xls〃； _
…1； 〃mySheet！A1：D7〃
。DoCmd。OpenTable strName； acViewNorm
