第11章 VB与Excel的数据交换

本文格式为Word版，下载可任意编辑——第11章VB与Excel的数据交换

第11章VB与Excel的数据交换

Excel是目前十分流行的电子表格软件，好多人都习惯于在EXCEL中处理数据，并在Excel中完成报表输出等功能，但Excel的数据处理功能相对较弱，而VB具有强大的数据处理功能，但报表输出功能相对较弱。本章以实例的形式介绍VB如何从Excel中获得数据，再将处理后的数据保存到Excel工作表中，并调用Excel中的VBA指令对排版，生成数据报表。

11.1VB中Excel的启动与关闭

11.1.1Excel对象库引用

在VB中调用Excel，首先需要开启VB编程环境“工程〞菜单中的“引用〞项目，并选取项目中的“MicrosoftExcel11.0objectlibrary〞（Excel版本不同，这个选项中的的版本号可能不一样）。引用Excel对象库后，对编写代码会带来好多便利。

11.1.2Excel对象声明

EXCEL是以层次结构组织对象的，其对象模型中含有大量不同的对象元素。编程过程中主要用到以下4个层次的对象。

1．Application对象，即Excel程序本身；

2．WorkBook对象，即Excel的工作簿文件对象；

3．WorkSheets对象，表示的是Excel的工作表对象集；例如：worksheets(1)表示第一个工作表。

4．Cells、Range、Rows、Columns对象，分别表示Excel工作表中的单元格对象集、区域对象、行对象集、列对象集。例如：

Cells(3，5)表示第3行第5列的那个单元格Range(\表示第3行第5列的那个单元格Range(\表示从A1单元格到C5单元格的矩形区域Rows(1)表示第1行Range(\表示第1行Range(\表示第1到10行的区域Columns(1)表示第1列Range(\表示第1列Range(\表示从第A到D列

-111-

11.1.3VB中Excel的启动与关闭

例11-1新建立一个VB的工程，在窗体上添加2个命令按钮（Command1和Command2），2个按钮的Caption分别为“启动Excel〞和“关闭Excel〞，输入以下代码即可。

DimxlsAsNewExcel.Application'声明一个Excel应用程序对象DimxbookAsNewExcel.Workbook'声明一个Excel工作薄对象DimxsheetAsNewExcel.Worksheet'声明一个Excel工作表象PrivateSubCommand1_Click()

Setxbook=xls.Workbooks.Add'启动Excel，并将自动创立的工作薄赋给xbookSetxsheet=xbook.Worksheets(1)'将第一个工作表赋给xsheet

xls.Visible=True'显示Excel窗口，程序调试阶段显示该窗口十分重要

EndSub

PrivateSubCommand2_Click()xls.Quit

Setxls=Nothing'释放对象变量Setxbook=NothingSetxsheet=Nothing

EndSub

这里将有关对象声明放在通用声明段是为了在两个命令按钮中均可以调用对象xls。

11.2VB与Excel的数据交换

当VB程序启动Excel后，就可以对其中的单元格进行任意处理了。例11-2随机生成的一组学生成绩数据保存到一个Excel工作表中。

新建一个VB工程，引用“MicrosoftExcel11.0objectlibrary〞对象库后。在窗体上添加2个按钮（Command1、Command2），Caption属性分别为“生成数据存入Excel〞和“保存及并闭Excel〞。代码如下：

DimxlsAsNewExcel.ApplicationDimxbookAsNewExcel.WorkbookDimxsheetAsNewExcel.WorksheetPrivateSubCommand1_Click()

Setxbook=xls.Workbooks.AddSetxsheet=xbook.Worksheets(1)

xls.Visible=True'当程序调试成功以后就可以删除此操作xsheet.Cells(1,1)=\学号\填写表头xsheet.Cells(1,2)=\高等数学\xsheet.Cells(1,3)=\英语\

xsheet.Cells(1,4)=\大学计算机基础\-112-

xsheet.Cells(1,5)=\平均成绩\Fori=2To10

xsheet.Cells(i,1)=\生成学号Sum=0

Forj=2To4

xsheet.Cells(i,j)=Int(Rnd()*51)+50Sum=Sum+xsheet.Cells(i,j)Nextj

xsheet.Cells(i,5)=Round(Sum/3,2)NextiEndSub

PrivateSubCommand2_Click()

xbook.SaveAs(\以指定文件名存盘xls.Quit

Setxls=Nothing'释放对象变量Setxbook=NothingSetxsheet=Nothing

MsgBox\请通过资源管理器查询C盘根文件夹下生成的temp.xls文件\EndSub

11.3VB对Excel的全面控制

VB不仅可以与Excel实现数据交换，还可以对Excel进行删除或插入表行、列以及完成各种排版操作。

以下程序代码可以实现将例11-1生成的文件开启并执行各种排版操作，同例11-1一样，在窗体上添加2个命令按钮，并通过“工程〞菜单“引用〞Excel对象库后，录入以下代码：

DimxlsAsNewExcel.ApplicationDimxbookAsNewExcel.WorkbookDimxsheetAsNewExcel.WorksheetPrivateSubCommand1_Click()

Setxbook=xls.Workbooks.Open(\开启Excel文件Setxsheet=xbook.Worksheets(1)xls.Visible=True

xls.WindowState=xlMinimized'让Excel窗口最小化Fori=1To5

Withxsheet.Columns(i)'对各列样式进行设置.AutoFit'最适合列宽

.HorizontalAlignment=xlCenter'水平方向居中

-113-

.VerticalAlignment=xlCenter'垂直方向居中EndWithNexti

xsheet.Rows(1).Insert'在原表第1行前插入一行

xsheet.Cells(1,1)=\班级学生成绩表\写入表标题xsheet.Range(\合并单元格区域

xsheet.Range(\设置第1行行高为40磅

xsheet.Range(\设置第2到11行行高为24磅Withxsheet.Cells(1,1)'设置表标题字体及字号.Font.Name=\隶书\.Font.Size=24

.HorizontalAlignment=xlCenter.VerticalAlignment=xlCenterEndWith

WithRange(\对A2到E11区域设置表格线.Borders(xlEdgeLeft).LineStyle=xlContinuous'左边线.Borders(xlEdgeTop).LineStyle=xlContinuous'顶边线.Borders(xlEdgeBottom).LineStyle=xlContinuous'底边线.Borders(xlEdgeRight).LineStyle=xlContinuous'右边线

.Borders(xlInsideVertical).LineStyle=xlContinuous'内部垂直线.Borders(xlInsideHorizontal).LineStyle=xlContinuous'内部水平线EndWith

MsgBox\排版终止!\EndSub

PrivateSubCommand2_Click()xbook.Savexls.Quit

Setxls=Nothing'释放对象变量Setxbook=NothingSetxsheet=NothingEndSub

11.4Excel中的VBA

实际上，要全面把握EXCEL中的VBA语言是十分困难的，由于涉及到太多的对象、属性及其方法，但利用微软公司提供的宏录制功能，学习就变得易如反掌。

宏就是一段程序，存在于Office系列应用软件中，如Word、Excel、PowerPoint、Outlook等。对这些应用软件的所有操作步骤都可以录制成宏代码，然后再对宏代码进行分析，是学习VBA最好的方法。下面以Excel为例，介绍宏的录制及学习方法。-114-

假使想学习Excel中对单元格的字体、字号、边框线设置的VBA代码，操作步骤如下：（1）启动Excel，在任何一个单元格中录入一些内容，选择“工具|宏|录制新宏〞，界面如图11-1所示。

图11-1录制新宏对