《第11章VB与Excel的数据交换》由会员分享，可在线阅读，更多相关《第11章VB与Excel的数据交换（9页珍藏版）》请在金锄头文库上搜索。
1、-111- 第 11 章VB 与 Excel 的数据交换Excel 是目前非常流行的电子表格软件，很多人都习惯于在EXCEL 中处理数据， 并在 Excel中完成报表输出等功能，但 Excel 的数据处理功能相对较弱，而 VB具有强大的数据处理功能，但报表输出功能相对较弱。本章以实例的形式介绍VB如何从 Excel 中获得数据， 再将处理后的数据保存到Excel 工作表中，并调用Excel 中的 VBA指令对排版，生成数据报表。11.1 VB 中 Excel的启动与关闭11.1.1 Excel对象库引用在 VB中调用 Excel ，首先需要打开VB编程环境“工程”菜单中的“引用”项目，并选取项
2、目中的“Microsoft Excel 11.0 object library” （Excel版本不同，这个选项中的的版本号可能不一样） 。引用 Excel 对象库后，对编写代码会带来很多便利。11.1.2 Excel对象声明EXCEL是以层次结构组织对象的，其对象模型中含有许多不同的对象元素。编程过程中主要用到以下4 个层次的对象。1Application对象，即Excel 程序本身；2WorkBook 对象，即Excel 的工作簿文件对象；3WorkSheets 对象，表示的是Excel 的工作表对象集；例如：worksheets(1)表示第一个工作表。4Cells 、Range、Rows
3、、Columns 对象，分别表示Excel 工作表中的单元格对象集、区域对象、行对象集、列对象集。例如：Cells(3，5) 表示第 3 行第 5 列的那个单元格Range(“C5“) 表示第 3 行第 5 列的那个单元格Range(“A1:C5“) 表示从 A1单元格到C5单元格的矩形区域Rows(1) 表示第 1 行Range(“1:1“) 表示第 1 行Range(“1:10“) 表示第 1 到 10 行的区域Columns(1) 表示第 1 列Range(“A:A“) 表示第 1 列Range(“A:D“) 表示从第A到 D列-112- 11.1.3 VB中 Excel 的启动与关闭例
4、 11-1 新建立一个VB的工程，在窗体上添加2 个命令按钮（ Command1 和 Command2 ） ，2 个按钮的Caption分别为“启动Excel ”和“关闭Excel ” ，输入以下代码即可。Dim xls As New Excel.Application 声明一个Excel 应用程序对象Dim xbook As New Excel.Workbook 声明一个Excel 工作薄对象Dim xsheet As New Excel.Worksheet 声明一个Excel 工作表象Private Sub Command1_Click() Set xbook = xls.Workbook
5、s.Add 启动 Excel ，并将自动创建的工作薄赋给xbook Set xsheet = xbook.Worksheets(1) 将第一个工作表赋给xsheet xls.Visible = True 显示 Excel 窗口，程序调试阶段显示该窗口非常重要End Sub Private Sub Command2_Click() xls.Quit Set xls = Nothing 释放对象变量Set xbook = Nothing Set xsheet = Nothing End Sub 这里将有关对象声明放在通用声明段是为了在两个命令按钮中均可以调用对象xls 。11.2 VB 与 Exc
6、el 的数据交换当 VB程序启动 Excel 后，就可以对其中的单元格进行任意处理了。例 11-2 随机生成的一组学生成绩数据保存到一个Excel 工作表中。新建一个VB工程，引用“Microsoft Excel 11.0 object library”对象库后。在窗体上添加 2 个按钮 （Command1 、Command2 ） ，Caption属性分别为 “生成数据存入Excel ”和“保存及并闭Excel ” 。代码如下：Dim xls As New Excel.Application Dim xbook As New Excel.Workbook Dim xsheet As New E
7、xcel.Worksheet Private Sub Command1_Click() Set xbook = xls.Workbooks.Add Set xsheet = xbook.Worksheets(1) xls.Visible = True 当程序调试成功以后就可以删除此操作xsheet.Cells(1, 1) = “学号 “ 填写表头xsheet.Cells(1, 2) = “高等数学 “ xsheet.Cells(1, 3) = “英语 “ xsheet.Cells(1, 4) = “大学计算机基础“ -113- xsheet.Cells(1, 5) = “平均成绩 “ For
8、i = 2 To 10 xsheet.Cells(i, 1) = “09108“ & 1000 + I 生成学号Sum = 0 For j = 2 To 4 xsheet.Cells(i, j) = Int(Rnd() * 51) + 50 Sum = Sum + xsheet.Cells(i, j) Next j xsheet.Cells(i, 5) = Round(Sum / 3, 2) Next i End Sub Private Sub Command2_Click() xbook.SaveAs(“c:temp.xls“) 以指定文件名存盘xls.Quit Set xls = Noth
9、ing 释放对象变量Set xbook = Nothing Set xsheet = Nothing MsgBox “请通过资源管理器查询C盘根文件夹下生成的temp.xls文件 “ End Sub 11.3 VB 对Excel的全面控制VB不仅可以与Excel 实现数据交换，还可以对Excel 进行删除或插入表行、列以及完成各种排版操作。以下程序代码可以实现将例11-1 生成的文件打开并执行各种排版操作，同例 11-1 一样，在窗体上添加2 个命令按钮，并通过“工程”菜单“引用”Excel 对象库后，录入以下代码：Dim xls As New Excel.Application Dim xb
10、ook As New Excel.Workbook Dim xsheet As New Excel.Worksheet Private Sub Command1_Click() Set xbook = xls.Workbooks.Open(“c:temp.xls“) 打开 Excel 文件Set xsheet = xbook.Worksheets(1) xls.Visible = True xls.WindowState = xlMinimized 让 Excel 窗口最小化For i = 1 To 5 With xsheet.Columns(i) 对各列样式进行设置.AutoFit 最适合列
11、宽.HorizontalAlignment = xlCenter 水平方向居中-114- .VerticalAlignment = xlCenter 垂直方向居中End With Next i xsheet.Rows(1).Insert 在原表第1 行前插入一行xsheet.Cells(1, 1) = “XX班级学生成绩表“ 写入表标题xsheet.Range(“a1:e1“).Merge 合并单元格区域xsheet.Range(“1:1“).RowHeight = 40 设置第 1 行行高为40 磅xsheet.Range(“2:11“).RowHeight = 24 设置第 2 到 11
12、行行高为24 磅With xsheet.Cells(1, 1) 设置表标题字体及字号.Font.Name = “隶书 “ .Font.Size = 24 .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter End With With Range(“A2:E11“) 对 A2 到 E11 区域设置表格线.Borders(xlEdgeLeft).LineStyle = xlContinuous 左边线.Borders(xlEdgeTop).LineStyle = xlContinuous 顶边线.Borders(xlEdgeB
13、ottom).LineStyle = xlContinuous 底边线.Borders(xlEdgeRight).LineStyle = xlContinuous 右边线.Borders(xlInsideVertical).LineStyle = xlContinuous 内部垂直线.Borders(xlInsideHorizontal).LineStyle = xlContinuous 内部水平线End With MsgBox “排版结束 !“ End Sub Private Sub Command2_Click() xbook.Save xls.Quit Set xls = Nothing
14、 释放对象变量Set xbook = Nothing Set xsheet = Nothing End Sub 11.4 Excel中的 VBA 实际上，要全面掌握EXCEL中的 VBA语言是非常困难的，因为涉及到太多的对象、属性及其方法，但利用微软公司提供的宏录制功能，学习就变得易如反掌。宏就是一段程序， 存在于 Office系列应用软件中，如 Word、 Excel 、 PowerPoint 、 Outlook等。对这些应用软件的所有操作步骤都可以录制成宏代码，然后再对宏代码进行分析，是学习 VBA最好的方法。下面以Excel 为例，介绍宏的录制及学习方法。-115- 如果想学习Excel
15、 中对单元格的字体、字号、边框线设置的VBA代码，操作步骤如下：（1）启动Excel ，在任何一个单元格中录入一些内容，选择“工具| 宏| 录制新宏”，界面如图 11-1 所示。所有内容均使用默认，单击“确定”按钮，Excel界面上会多一个宏录制工具栏，如图11-2 所示。（2）选中单元格“C4” ，设置字体为“黑体”，字号为24 磅，在左右加边框，完成这些操作后单击宏录制工具栏中的停止录制按钮，结束宏录制。（3）按 ALT+F11键进制 Excel 中的 VBA集成开发环境，这个界面和VB的集成开发环境非常相似，如图11-3 所示。图 11-1 录制新宏对话框图 11-2 处于宏录制状态的E
16、xcel 界面宏 录 制 工 具-116- （4）展开右侧的“模块” ，可以看到其中的“模块1” ，这就是刚才录制的宏，双击“模块 1” ，可以在右侧看到录制的宏代码。如图11-4 所示。由于宏会对每一个步骤分别进行录制，所以代码看起来特别烦琐，可以将其中没有进行图 11-3 VBA集成开发环境图 11-4 宏代码-117- 特别处理的代码全部删除，这样就可以得到想要的VBA指令了。如图11-5 所示。以上代码其实还可以再简化如下：Sub Macro1() Selection.Font.Name = “黑体 “ Selection.Font.Size = 24 Selection.Borders(
