VB与Excel的数据交换

1、第11章 VB与Excel的数据交换Excel是目前非常流行的电子表格软件，很多人都习惯于在EXCEL中处理数据，并在Excel中完成报表输出等功能，但Excel的数据处理功能相对较弱，而VB具有强大的数据处理功能，但报表输出功能相对较弱。本章以实例的形式介绍VB如何从Excel中获得数据，再将处理后的数据保存到Excel工作表中，并调用Excel中的VBA指令对排版，生成数据报表。11.1 VB中Excel的启动与关闭 Excel对象库引用在VB中调用Excel，首先需要打开VB编程环境“工程”菜单中的“引用”项目，并选取项目中的“Microsoft Excel 11.0 object li

2、brary”（Excel版本不同，这个选项中的的版本号可能不一样）。引用Excel对象库后，对编写代码会带来很多便利。 Excel对象声明 EXCEL是以层次结构组织对象的，其对象模型中含有许多不同的对象元素。编程过程中主要用到以下4个层次的对象。 1Application对象，即Excel程序本身； 2WorkBook对象，即Excel的工作簿文件对象； 3WorkSheets对象，表示的是Excel的工作表对象集；例如：worksheets(1)表示第一个工作表。4Cells、Range、Rows、Columns对象，分别表示Excel工作表中的单元格对象集、区域对象、行对象集、列对象集。

3、例如：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列 VB中Excel的启动与关闭 例11-1 新建立一个VB的工程，在窗体上添加2个命令按钮（Comm

4、and1和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.Workbooks.Add '启动Excel，并将自动创建的工作薄赋给x

5、book Set xsheet = xbook.Worksheets(1) '将第一个工作表赋给xsheet xls.Visible = True '显示Excel窗口，程序调试阶段显示该窗口非常重要End SubPrivate Sub Command2_Click() xls.Quit Set xls = Nothing '释放对象变量 Set xbook = Nothing Set xsheet = NothingEnd Sub这里将有关对象声明放在通用声明段是为了在两个命令按钮中均可以调用对象xls。11.2 VB与Excel的数据交换当VB程序启动Excel后，

6、就可以对其中的单元格进行任意处理了。例11-2 随机生成的一组学生成绩数据保存到一个Excel工作表中。新建一个VB工程，引用“Microsoft Excel 11.0 object library”对象库后。在窗体上添加2个按钮（Command1、Command2），Caption属性分别为“生成数据存入Excel”和“保存及并闭Excel”。代码如下：Dim xls As New Excel.ApplicationDim xbook As New Excel.WorkbookDim xsheet As New Excel.WorksheetPrivate Sub Command1_Clic

7、k() 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) = "大学计算机基础" xsheet.Cells(1, 5) = "平均成

8、绩" For 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 iEnd SubPrivate Sub Command2_Click() xbook.SaveAs("c:te

9、mp.xls") '以指定文件名存盘 xls.Quit Set xls = Nothing '释放对象变量 Set xbook = Nothing Set xsheet = Nothing MsgBox "请通过资源管理器查询C盘根文件夹下生成的temp.xls文件"End Sub11.3 VB对Excel的全面控制VB不仅可以与Excel实现数据交换，还可以对Excel进行删除或插入表行、列以及完成各种排版操作。以下程序代码可以实现将例11-1生成的文件打开并执行各种排版操作，同例11-1一样，在窗体上添加2个命令按钮，并通过“工程”菜单“引用”

10、Excel对象库后，录入以下代码：Dim xls As New Excel.ApplicationDim xbook As New Excel.WorkbookDim xsheet As New Excel.WorksheetPrivate Sub Command1_Click() Set xbook = xls.Workbooks.Open("c:temp.xls") '打开Excel文件 Set xsheet = xbook.Worksheets(1) xls.Visible = True xls.WindowState = xlMinimized '让

11、Excel窗口最小化 For i = 1 To 5 With xsheet.Columns(i) '对各列样式进行设置 .AutoFit '最适合列宽 .HorizontalAlignment = xlCenter '水平方向居中 .VerticalAlignment = xlCenter '垂直方向居中 End With Next i xsheet.Rows(1).Insert '在原表第1行前插入一行 xsheet.Cells(1, 1) = "XX班级学生成绩表" '写入表标题 xsheet.Range("a1

12、:e1").Merge '合并单元格区域 xsheet.Range("1:1").RowHeight = 40 '设置第1行行高为40磅 xsheet.Range("2:11").RowHeight = 24 '设置第2到11行行高为24磅 With xsheet.Cells(1, 1) '设置表标题字体及字号 .Font.Name = "隶书" .Font.Size = 24 .HorizontalAlignment = xlCenter .VerticalAlignment = xlCen

13、ter End With With Range("A2:E11") '对A2到E11区域设置表格线 .Borders(xlEdgeLeft).LineStyle = xlContinuous '左边线 .Borders(xlEdgeTop).LineStyle = xlContinuous '顶边线 .Borders(xlEdgeBottom).LineStyle = xlContinuous '底边线 .Borders(xlEdgeRight).LineStyle = xlContinuous '右边线 .Borders(xlIns

14、ideVertical).LineStyle = xlContinuous '内部垂直线 .Borders(xlInsideHorizontal).LineStyle = xlContinuous '内部水平线 End With MsgBox "排版结束!"End SubPrivate Sub Command2_Click() xbook.Save xls.Quit Set xls = Nothing '释放对象变量 Set xbook = Nothing Set xsheet = NothingEnd Sub11.4 Excel中的VBA实际上，要

15、全面掌握EXCEL中的VBA语言是非常困难的，因为涉及到太多的对象、属性及其方法，但利用微软公司提供的宏录制功能，学习就变得易如反掌。宏就是一段程序，存在于Office系列应用软件中，如Word、Excel、PowerPoint、Outlook等。对这些应用软件的所有操作步骤都可以录制成宏代码，然后再对宏代码进行分析，是学习VBA最好的方法。下面以Excel为例，介绍宏的录制及学习方法。如果想学习Excel中对单元格的字体、字号、边框线设置的VBA代码，操作步骤如下：图 11-1 录制新宏对话框（1）启动Excel，在任何一个单元格中录入一些内容，选择“工具|宏|录制新宏”，界面如图11-1所

16、示。图 11-2 处于宏录制状态的Excel界面宏录制工具栏所有内容均使用默认，单击“确定”按钮，Excel界面上会多一个宏录制工具栏，如图11-2所示。（2）选中单元格“C4”，设置字体为“黑体”，字号为24磅，在左右加边框，完成这些操作后单击宏录制工具栏中的停止录制按钮，结束宏录制。（3）按ALT+F11键进制Excel中的VBA集成开发环境，这个界面和VB的集成开发环境非常相似，如图11-3所示。图 11-4 宏代码图 11-3 VBA集成开发环境（4）展开右侧的“模块”，可以看到其中的“模块1”，这就是刚才录制的宏，双击“模块1”，可以在右侧看到录制的宏代码。如图11-4所示。由于宏会

17、对每一个步骤分别进行录制，所以代码看起来特别烦琐，可以将其中没有进行图 11-5 简化后的VBA代码特别处理的代码全部删除，这样就可以得到想要的VBA指令了。如图11-5所示。以上代码其实还可以再简化如下：Sub Macro1() Selection.Font.Name = "黑体" Selection.Font.Size = 24 Selection.Borders(xlEdgeLeft).LineStyle = xlContinuous Selection.Borders(xlEdgeRight).LineStyle = xlContinuousEnd Sub这里的Se

18、lection就是指被选中的单元格或单元格区域，这些代码移植到VB中时，只需要在前面加上Selection所属的父对象名序列即可。通过以上步骤，就可以学习Excel中对字体、字号、边框线的设置方法，也就可以直接用于VB中对Excel的控制了。假设VB中Excel应用程序的对象名为xls，以下是部分操作的代码。（1）显示当前窗口：xls.Visible = True（2）更改 Excel 标题栏：xls.Caption := "应用程序调用 Microsoft Excel"（3）添加新工作簿：xls.WorkBooks.Add（4）打开已存在的工作簿：xls.WorkBook

19、s.Open("C:temp.xls")（5）设置第2个工作表为活动工作表：xls.WorkSheets(2).Activate或xls.WorkSheets("Sheet2").Activate（6）给单元格赋值：xls.Cells(1,4).Value="第一行第四列" （7）设置指定列的宽度（单位：字符个数），以第一列为例：xls.ActiveSheet.Columns(1).ColumnsWidth=5（8）设置指定行的高度（单位：磅）（1磅0.035厘米），以第二行为例：xls.ActiveSheet.Rows(2).Row

20、Height= 1/0.035 '1厘米（9）在第8行之前插入分页符：xls.WorkSheets(1).Rows(8).PageBreak=1（10）指定边框线宽度：xls.ActiveSheet.Range("B3:D4").Borders(2).Weight=3（11）设置第一行字体属性： Xls.ActiveSheet.Rows(1).Font.Name = '隶书'Xls.ActiveSheet.Rows(1).Font.Color = clBlueXls.ActiveSheet.Rows(1).Font.Bold = True Xls.A

21、ctiveSheet.Rows(1).Font.UnderLine = True（12）进行页面设置：a.页眉：Xls.ActiveSheet.PageSetup.CenterHeader = "报表演示"b.页脚：Xls.ActiveSheet.PageSetup.CenterFooter = "第&P页"c.页眉到顶端边距2cm：Xls.ActiveSheet.PageSetup.HeaderMargin = 2/0.035d.页脚到底端边距3cm：Xls.ActiveSheet.PageSetup.HeaderMargin = 3/0.03

22、5 e.顶边距2cm：Xls.ActiveSheet.PageSetup.TopMargin = 2/0.035f.底边距2cm：Xls.ActiveSheet.PageSetup.BottomMargin = 2/0.035g.左边距2cm：Xls.ActiveSheet.PageSetup.LeftMargin = 2/0.035h.右边距2cm：Xls.ActiveSheet.PageSetup.RightMargin = 2/0.035i.页面水平居中：Xls.ActiveSheet.PageSetup.CenterHorizontally = Truej.页面垂直居中：Xls.ActiveSheet.PageSetup.CenterVertically =Truek.打印单元格网线：Xls.ActiveSheet.PageSetup.PrintGridLines = True（13）拷贝操作： a.拷贝整个工作表：Xlsb.拷贝指定区域：Xls.ActiveSheet.Range("A1:E2").Copyc.从A1位置开始粘贴：X