VB控制Excel

VB控制Excel（共4篇）

VB控制Excel 篇1

在日常的工作中,人们可能会使用Excel进行数据统计。 例如,教师在每次测试结束后,需要统计班级的最高分、最低分、平均分及其每个分数段的人数。而这种类似于统计分数段人数的这种分段条件统计,对于一些Excel使用者而言,有一定的难度。为了解决这个分段统计的问题,文中设计了一个插件。

开发插件的主要好处在于插件结构具有良好的扩充和定制功能以及很容易将插件作为功能模块挂接到主程序上面[1]。 文中采用Visual Basic 6.0为开发平台,开发了一个适用于Ex-cel 2010版本的插件。该插件适用于对所选择的工作表单元格区域进行多段的条件统计,并且将统计结果显示在用户所指定的单元格区域中。这个插件的操作界面简单,操作过程容易掌握。本文主要介绍了这个插件的设计和实现。

1插件的设计

文中所设计的插件是适用于Excel 2010版本的,因此,该插件在Excel 2010功能区中创建一个名为“统计”的选项卡,在该选项卡的名为“自定义组”选项组中,设置一个名为“分段统计”的命令按钮。当用户单击该命令按钮时,出现图1所示的窗口。

在该窗口中用户单击“计算区域”文本框时,可以在工作表中选择需要统计的单元格区域;单击“结果显示区域”文本框时,可以在工作表中选择存放统计结果的单元格区域。用户在该窗口中设置统计条件,至多可以设置10个条件,单击“确定” 按钮后,就可以依据条件分段统计计算区域中符合条件的单元格个数,并将结果显示到指定的单元格区域中。

分段统计功能在实现时,考虑到用户所选择的需统计的单元格区域后,可能会在统计完成后有时会修改它们的值,那么相应的统计结果也会发生变化。因此,为了实现实时更新,统计方法是使用Excel中的函数来完成。

2插件的实现

文中使用Visual Basic 6.0通过创建外接程序项目完成插件的设计和实现。

2.1创建外接程序项目

使用VB6.0企业版创建新的“外接程序”项目。在“引用” 对话框中添加如下的两个引用:“Microsoft Office 14.0 Object Li-brary”和“Microsoft Excel 14.0 Object Library”,再按表1设置 “Connect”设计器的属性。

2.2声明全局变量、添加引用和实现接口

1) 声明全局变量

在模块中声明全局变量Excel_app,代码为:Public Ex-cel_app As Excel.Application。

2) 添加引用

在“Connect”设计器的代码窗口中,添加应用程序接口的引用:IDTExtensibility2和IRibbon Extensibility,以便于VB调用Excel的资源,识别Excel VBA中的资源和对象。具体语句如下:

Implements IDTExtensibility2

Implements IRibbon Extensibility

3) 实现IDTExtensibility2接口

IDTExtensibility2接口中定义了5个成员方法:On Add In-s Update方法,On Connection方法,On Disconnection方法,On-Startup Complete方法,On Begin Shutdown方法。这些方法分别发生在COM加载项更改、加载、卸载,或者是Excel程序启动时, 或者是Excel程序关闭而COM加载项仍运行时。文中这些方法在实现时因为没有特殊的要求,所以大部分方法是空方法。 但是在On Connection方法中需要完成全局变量的赋值,该变量在程序中表示Excel应用程序,其具体的代码如下:

4) 实现IRibbon Extensibility接口

因为该插件是面向Excel 2010版本的,因此,需要在Excel的功能区中添加选项组与命令按钮。而IRibbon Extensibility接口中所定义的方法Get Custom UI,就是依据XML代码创建功能区用户界面。所以,在“Connect”设计器必须实现IRibbon Exten-sibility接口的方法Get Custom UI,其具体代码如下:

上述代码中所调用的Get Ribbon XML是用户自定义的函数,该函数要求返回一个字符串,这个字符串是使用XML代码在Excel的功能区中创建一个“统计”选项卡,并在该选项卡中创建名为“自定义组”的选项组,再在这个选项组中创建一个 “分段统计”命令按钮。函数Get Ribbon XML的具体代码如下:

在函数Get Ribbon XML中指明,用户单击“分段统计”命令按钮时,则执行名为“submain”的过程,该过程主要是显示图1所示的“分段统计”窗口,其代码如下:

插件成功安装后Excel功能区中会添加图2所示的选项卡、选项组等。

2.3窗体的设计

1) 外观设计

“分段统计”窗体的名称为User Form1,在该窗体中添加了标签控件、文本框控件、选择框控件、组合框控件和按钮控件等,如图1所示。其中“计算区域”的文本框控件的名字为Ref Edit1,“结果显示区域”的文本框控件的名字为Ref Edit2。 这两个文本框分别用于显示用户在Excel工作表中选定的数据源区域和结果区域的地址,该地址是绝对地址。

窗体中允许用户至多设置10个分段条件,每个分段条件是由一个选择框控件、两个组合框控件和两个文本框控件构成。选择框控件的名称是Check Boxi,其中i=1,2,…,10;名称为Combo Boxi_1(i=1,2,…,10)的组合框只允许设置“>”或“>=” 的关系运算,名称为Combo Boxi_2(i=1,2,…,10)的组合框只允许设置“<”或“<=”的关系运算,这两个组合框的内容只允许从下拉列表中选择,因此,它们的Style属性设置为Dropdown List; 两个文本框分别用于输入对应关系运算的操作数值,该数值要求用户从键盘输入,它们的名称分别是Text Boxi_1和Text Boxi_2(i=1,2,…,10)。

“确定”按钮控件的名称是Command1,“取消”按钮控件的名称是Command2。

2) 窗口激活时设置默认值的功能实现

当“分段统计”窗口被激活时,所有Combo Boxi_1(i=1, 2,…,10)的组合框的默认值为“>”,所有Combo Boxi_2(i=1, 2,…,10)的组合框的默认值为“<”,同时,数据源的文本框Ref Edit1获得焦点。因此,User Form1的Activate事件代码如下:

3) Ref Edit1和Ref Edit2单元格区域选择功能的实现

文本框Ref Edit1和Ref Edit2在窗体运行时,要求能显示用户在Excel工作表中所选择的单元格区域,因此,在用户单击文本框时,要求将当前的“分段统计”窗体隐藏,出现图3所示的输入框,允许用户在Excel工作表中用鼠标拖选单元格区域,选择完成后单击图3所示的“确定”按钮,隐藏的“分段统计”窗口显示到前台。

Ref Edit1文本框的单击事件和Ref Edit2的单击事件处理方法非常类似,Ref Edit1的Click事件代码如下:

4) 设置分段条件的功能实现

在设置分段条件时,一但文本框Text Boxi_1或Text Boxi_2(i=1,2,…10)的内容发生改变时,则只要这两个文本框中的一个有内容,那么对应的选择框Check Boxi(i=1,2,…10)的Value值设置为1,否则为0。因此,需要对这些文本框的文本改变事件进行对应的编程设计,它们的实现代码极为类似。以下是文本框Text Box1_1的Change事件的代码:

5)“确定”按钮Command1的功能实现

单击“确定”按钮时,要求能够按照窗口中所设定的条件, 对指定的数据源区域进行条件统计,并将结果显示到指定的单元格区域中。

其算法基本思想是:首先获取数据源的非空的单元格区域和非空的结果区域,并分别保存为字符串形式,如果这两个区域有一个是空的,则出现出错提示对话框,再关闭窗口;如果区域选择正确,则使用循环语句对10个选择框进行依次检查,如果第i个选择框的Value值为1,即被选中,则读取它对应的两个组合框、两个文本框中的内容,并结合数据源区域的地址字符串,使用Excel中countif函数完成公式的编写,并将该公式保存为字符串,最后在指定的单元格内分别填写条件和计算的公式;最后,关闭窗口。

由于统计的方法使用的是公式,因此,如果数据源区域的值发生了改变,那么相应的运算结果就会自动调整。Com-mand1按钮的Click事件的代码如下:

6) “取消”按钮Command2的功能实现

单击“取消”按钮时,关闭窗口。其Click事件的代码如下:

2.4生成dll文件并安装插件

在VB6.0企业版中生成扩展名为dll的动态链接库文件 “fdtj.dll”。打开Excel 2010,在“开发工具”选项卡的“加载项” 选项组中单击“COM加载项”命令按钮,在出现的“COM加载项”对话框中正确的添加文件“fdtj.dll”,即可成功的安装该插件。这时,Excel 2010中就会出现图2所示的选项卡,则用户就可以很方便地使用分段统计的功能。

3结束语

本文详细介绍了使用VB开发Excel 2010插件的方法,以及具体的插件功能实现,文中所设计的插件可以很方便地实现分段的条件统计,具有一定的应用价值,并且也可以将之作为Excel VBA教学的一个良好用例。

摘要：文中使用Visual Basic 6.0设计了一个Excel 2010的插件,该插件是依据用户所设定的分段条件,对用户在电子工作表中所选定的数据源区域进行条件统计,并将结果填写到用户所指定的单元格区域中。文中对插件的设计和实现进行了较为详细的描述。该插件具有一定的实用价值。

关键词：分段统计,Excel 2010,插件,VBA

VB控制Excel 篇2

机械设计过程是很繁琐的工作, 有的机械设计企业会花“重金”购买相关的计算软件, 这样做当然会给机械设计人员减轻很多工作量。不过, 如果设计人员所设计的结构超出软件的涉及范围, 那么设计工作就可能陷入困境, 机械设计企业可能还得另外购买相应结构的软件, 这样会给企业带来较大的经济负担。笔者利用EXCEL与VB各自的优点, 简单地介绍一下开发机械设计软件的方法。

1 方法简介

机械设计过程中要涉及到很多设备, 有些设备又有许多相似的部分, 比如传动部分。我们仅仅用传动部分中的链传动部分来举例说明用EXCEL与VB开发机械设计软件。由于EXCEL在解决计算问题上十分方便, 不像VB那样在计算之前得定义每个变量;同时, EXCEL的数据输入很方便, 自动查表功能也很强大。VB是面向对象编程工具, 交互能力很强, 我们可以将某些复杂的逻辑判断工作交给VB来完成, 同时, VB可以生成很友好的界面, 比如各种按钮、图片、菜单、对话框、具有多个界面的窗体等等, 因此我们设计软件的中心思想是将大部分的计算工作交给EXCEL来完成, 用VB作为EXCEL的载体。

在本例中, 我们将大部分的计算工作交给EXCEL来完成, 一般通过采用相对引用, 就可以轻松地建立起各个单元格直接的函数关系, 而且这种函数关系不会因为插入行或列而改变。对于绝对引用, 只有在特殊的情况下才使用, 当在各个单元格建立起函数关系之后, 最好不要再插入行或列, 否则会引起错误。这项工作完成后, 复杂的数据计算EXCEL可以很轻松地完成, 如图1所示。

图1中, 阴影是需要我们输入的项目, 其他单元格则可以自动得出计算结果。在完成上述EXCEL的编程后, 就可以用VB来为EXCEL制作载体了。

具体方法如下:首先将VB软件打开, 建立一个窗体form1, 修改窗体form1的属性选项caption, 默认是“form1”, 将其改为“计算软件”;修改窗体form1的属性选项backcolor, 选择图中的颜色;同时可以在窗体form1上加上Lable控件, 更改其属性加上个人的信息。要想制作我们非常熟悉的菜单, 可以在窗体form1中打开其属性对话框, 选择“菜单编辑器”, 做出如图2所示的最终软件界面。

有了上述的界面, 接下来我们就可以给每个图标或菜单选项编写原代码了, 这些工作其实是最关键的。由于篇幅所限, 我们只简单地介绍“链传动”这个菜单选项。我们希望当点击“链传动”这个菜单选项时, 能进入另外一个界面。我们先把要进入的界面预先设计好, 具体操作是:做一个窗体form2, 在窗体form2上加上Command1和Command2两个按钮, 将其更名为“开始计算”和“结束计算”。这里需要强调的是我们利用OLE这个VB具有的强大数据连接工具, 将事先编好的EXCEL计算程序嵌入到窗体form2当中来。单击“链传动”进入编程, 原代码如下 (如图3) :

由于在关闭“链传动”界面后, EXCEL将恢复原始的数据 (新的计算结果将不被保留) , 这时可以让软件弹出一个提示对话框, 如图3所示。原代码修改如下:

如果希望保留数据, 则可以启用Common Dialog控件编写代码让软件保存EXCEL数据, 如果不想写代码, 也可以在关闭“燃煤机械设计”界面前将数据复制出来, 生成新的EXCEL表格, 其中的公式也都复制出来, 非常方便。还可以在软件中加上其他自己认为必要的功能, 如果自己对开发的软件经过调试以后认为可以的话, 就可以将其转化为可执行程序 (*.EXE文件) , 这样就能在任何一台电脑中运行本软件了 (没有安装VB软件也能用) 。

2 结语

用EXCEL与VB开发机械设计软件非常简单, 充分利用EXCEL强大的计算功能与VB能开发软件的优点, 根据个人的喜好, 随心所欲地开发出很方便、实用的机械设计软件, 有效地减少设计人员的工作量, 值得借鉴。

摘要：EXCEL具有强大的计算功能, 利用EXCEL可以很方便、快捷地实现机械设计过程中繁琐的计算;VB是一种很简便的软件开发工具, 开发出的软件很有档次, 若将二者的优点结合起来, 则可以开发出很方便、实用的机械设计软件。

VB控制Excel 篇3

1 程序设计

1.1 需求分析

Excel打印插件开发的直接目的,是使用户能方便地实现数据表当前页、奇数页、偶数页、全部页、选定区或任意指定多页的打印。插件在Excel主程序中的放置位置、各功能模块布局方式、与用户交互的复杂度等,均直接影响用户使用的方便程度与效率。因此,本插件采用Excel菜单方式,组织各功能命令。插件安装后,在Excel菜单栏“帮助”菜单后添加一“打印”菜单,用户只需单击“打印”菜单中相应打印命令,基本无需其它交互,即能实现相应打印任务。

1.2 功能设计

基于Excel打印插件需求分析,确定插件含如下七项功能:

1)打印全部页:打印活动工作表中有用户内容的全部页面。

2)打印当前页:打印活动工作表用户数据区中活动单元格所在的页面。

3)打印奇偶页:先按正常页序打印活动工作表中全部奇数页,提示用户翻面纸张后逆正常页序打印活动工作表中全部偶数页。

4)打印奇数页:按正常页序打印活动工作表中全部奇数页。

5)打印偶数页:逆正常页序打印活动工作表中全部偶数页。

6)打印选择区:打印活动工作表中用户选择的单元格或单元格区域。

7)打印指定页:打印活动工作表中用户指定的任意单页、多页、连续页、不连续页。

1.3 详细设计

Excel打印插件七项功能实现,在详细设计时,要注意以下问题:

1)启动打印机前,检查活动工作表中有无打印内容;若无打印内容,应不发送打印命令到打印机,并提示用户。

2)打印奇偶页模块设计,应充分考虑活动工作表总页数等于1、为奇数、为偶数及双面打印方式纸张用量情况。打印指定页模块,要充分考虑提示用户页号输入规则及用户输入页号非法问题。

3)打印奇数页、偶数页、当前页、指定页模块程序流程相对复杂,应注意认真绘制和分析程序流程图,尽量设计较优算法。以逆向打印偶数页模块为例,设计程序流程图如图1所示。

2 程序实现

2.1 VB 6.0外接程序实现

1)创建VB外接程序工程

启动Visual Basic 6.0,选择“新建工程”对话框“新建”选项卡中“外接程序”打开;移除“frm Add In”窗体,保存工程及工程中“connect”设计器到新建文件夹“Excel打印插件”,工程名为“Excel Print Add In”,设计器名可用默认名称“connect”。

2)设置设计器连接属性

在工程资源管理器窗口,双击“connect”设计器,在“My Add In-Connect”对话框中,按表1设置设计器相应属性后,关闭对话框。

3)添加库文件引用

单击“工程”菜单中“引用”命令,在“引用”对话框中增加勾选下列选项,单击“确定”完成库文件引用。

(1)Microsoft Office 11.0 Object Library;

(2)Microsoft Execl 11.0 Object Library。

4)编写设计器代码

(1)代码规划

“connect”设计器代码,可分为设计器公共变量及事件声明、Addin Instance对象事件过程、插件中各打印命令单击事件过程、打印命令单击事件过程调用过程或函数四个部分。设计器公共变量与事件声明部分,用于声明Excel程序、下拉式命令栏、及命令栏中各打印按钮对象及事件。Addin Instance对象主要有On Connection、On Disconnection两个事件过程,On Connection事件完成添加“打印”菜单及“打印”菜单中各打印菜单项,On Disconnection事件完成Excel关闭时删除“打印”菜单及释放其占用内存。各打印菜单项单击事件过程调用过程或函数,包括奇数页打印过程、偶数页打印过程及活动工作表有无打印内容检查函数。

(2)代码编写

在工程资源管理器窗口,右击“connect”设计器,单击“查看代码”命令,进入设计器代码窗口,按前述规划编写代码。核心代码如下:

(3)生成dll动态链接库文件

检查并确保Excel程序未运行,单击“文件”菜单中“生成Excel Print Add In.dll”菜单项,在“生成工程”对话框,设置好“选项”参数后,单击“确定”,即生成“Excel Print Add In.dll”动态链接库文件。

2.2 插件注册/卸载程序实现

Excel中注册与卸载外部插件,可通过在Excel窗口执行COM加载项操作或使用regsvr32.exe注册/卸载程序两种方式中任一种方式实现。为便于插件程序分发与用户使用方便,可用Visual Basic 6.0开发“Excel打印插件安装”EXE程序,共用户注册与卸载插件。具体实现过程如下:

启动VB6.0,新建标准EXE工程,向窗体中至少添加“安装”、“卸载”、“取消”三个命令按钮对象,设置好窗体及各控件属性,保存工程到新建文件夹“Excel打印插件安装”中,工程名为“Excel2003打印插件”,其余可用默认。复制“Excel Print Add In.dll”文件到“Excel打印插件安装”文件夹中。进入窗体代码窗口,编写各按钮单击事件代码。调试运行程序正常后,生成应用程序“Excel2003打印插件.exe”。分发时,注意将“Excel Print Add In.dll”文件与“Excel2003打印插件.exe”两文件放到同一文件夹。程序关键代码如下:

3 结束语

基于VB的Excel打印插件程序的开发,不仅能弥补Excel打印功能的不足,提高Excel数据表打印工作效率与效益;更可作为VB外接程序设计教学与Excel VBA学习的良好教学项目。

摘要：基于VB的Excel打印插件程序,以VB6.0作开发工具,针对Excel 2003开发,具有打印当前页、奇数页、偶数页、全部页、选定区、任意指定多页等方便的电子表格打印功能。文中对插件从设计到实现的核心技术进行了论述,为VB6.0外接程序设计与Excel VBA开发学习提供了较好范例。

关键词：插件,Excel打印,VB,外接程序

参考文献

[1]Bullen S.Excel专业开发[M].杜茂康刘友军,译.北京:电子工业出版社,2007.

[2]陈文宇,胡迎春,侯军燕.Word2010插件的开发与实现[J].广西工学院学报,2010(6).

VB控制Excel 篇4

关键词：VB,EXCEL,报表

在数据库应用系统的设计过程中,报表的设计是很重要的一个步骤。如果系统缺乏灵活适用的报表,那无疑会给用户带来不少的麻烦。Visual Basic6.0是一门可视化语言,它是集应用程序开发、测试和查错功能于一体的集成式开发环境[1]。它虽然不是专门的数据库语言,但它与其它的数据库语言有良好的兼容性和应用接口,可以方便地与Oracle,Access,SQL Server等多种数据库相连接,并存取其数据生成各种报表。

在VB中有Data Report设计器,可以直接在VB IDE中创建报表,没有单独的报表文件,报表定义保存在VB项目的一个Active X设计器窗体中。这意味着当编译一个项目时,报表定义被编译进可执行文件中。因而在VB中制作报表是很容易的,它只需一段很短的代码,但是对报表的排版却要花去大量的时间。因而,利用VB操作EXCEL来生成复杂的报表,即在VB中引用EXCEL对象来制作报表,这样能够大大提高工作的效率。

1 EXCEL对象

在VB中能处理的Excel对象有:Application、Work Books、Work Sheets、Range及Cells,利用这些对象所提供的属性、方法和文件,就可以很容易的在VB应用程序中生成EXCEL报表,但要注意使用它们之前要先声明[2]。

1)Application对象是对象模型的顶层,表示整个Excel应用程序;

2)Workbook对象表示Excel应用程序中当前打开的一个工作簿;

3)Worksheet对象表示工作表对象(一个工作簿可以包含多个工作表);

4)Range对象代表工作表的某一单元格或多个单元格、某一选定区域等;

5)Cell对象表示特定工作表的一个单元格对象。

2 注意事项

2.1 VB与EXCEL的连接

由于VB的报表功能有限,而且一旦报表格式发生变化,就得相应修改程序,给应用软件的维护工作带来极大的不便。因此该设计充分利用EXECL的强大报表功能来实现。但由于VB与EXCEL由于分别属于不同的应用系统,如何把它们有机地结合在一起,就是该设计所要解决的一个重要的问题。

2.2 报表中需要合计和总计的内容的填入

在报表中往往有很多的需要合计的一行或是某一列的值,那么如何控制这些行列的值的填入,也是该设计需要解决的问题。

3 从VB输出报表到EXCEL中的具体过程

3.1 用VB创建外部EXCEL对象

大多数大型Active X-enabled应用程序和其它Active X部件,在它们的对象层次中都提供了一个顶层外部可创建对象。该对象提供了对该层次中其它对象的访问,并且还提供对整个应用程序起作用的方法和属性。每个Microsoft Office应用程序提供一个顶层Application对象。下面语句显示了对Microsoft Excel的Application对象的引用:

Dim vb Excel As New Excel.Application'引用Excel对象

Dim book As Excel.Workbook'引用工作薄

Dim my Sheet As Excel.Worksheet'引用工作表

然后,可以用这些变量来访问在EXCEL应用程序中的从属对象、以及这些对象的属性和方法。

Set book=vb Excel.Workbooks.Open(Temp File Name)'打开报表模版

'Temp File Name为一个EXCEL报表文件

Set my Sheet=book.Worksheets(1)'设定工作表

3.2 用EXCEL设计报表的模版文件

根据用户提供的报表,可以很快在EXCEL里生成模板文件。所谓生成模板文件只是为了满足用户多方面的需求而设计的。也是为了适合报表以后的更改而做的一点预备工作。在程序里当然不要对模板文件进行操作了,只需要对模板文件的一个拷贝进行操作就行,这也是设计模板文件的一个目的和好处。

Dim Temp File Name As String

Temp File Name=App.Path+"temp.xls"'临时文件所在位置

If Dir(Temp File Name)<>""Then Kill(Temp File Name)'清空临时文件

File Copy Excel File Name,Temp File Name'拷贝报表模板

‘Excel File Name为传入的参数报表模板所在的位置

3.3 生成工作表内容

有了上述两步工作的铺垫,下面接着就可以给各单元格赋值了。由于报表中有一些值是需要合计或是总计的,还需要判断那些单元格是需要计算,哪些是不需要计算的。那么实现这一功能的实现采用了循环语句,而且用变量的值记下了哪些是该计算的,哪些是报表的内容。具体如下:

3.4 打印报表

生成了工作表后,就可以对EXCEL发出打印指令了。注意在执行打印操作之前应该对EXCEL临时文件执行一次保存操作,以免在退出应用程序后EXCEL还提示用户是否保存已修改的文件。而打印报表就可以交给EXCEL去处理。当然打印的过程会根据程序给出的页数进行处理。

4 具体实现过程

为所有的报表的制作了一个通用的模块,只要调用这一公用模块,就能生成所需的报表。调用时,应该输入报表所对应的记录集、报表的行数和列数、报表第一行所显示的位置、一个表示要合计数据列数的字符串、合计标志、总计标志、报表模板所在路径、表示报表所要显示的页头的字符串、表示报表所要显示的制表人和制表日期的字符串。

事先做好了所要报表的模板,用一个临时文件拷贝报表的模板,而后,将引入的记录集一行一行地添入EXCEL临时表中,添入记录的同时页检测是否进行合计运算,再将合计的值添入到应在的位置(一般在表尾)。运行之前必须判断临时表是否为空,如果不为空,则要清空临时表。

具体的代码以及代码注释(’后为代码注释)如下:

5 结束语

针对数据库应用系统中,报表处理的复杂性,提出的基于VB和EXCEL的报表设计及打印的解决方法,该方法不仅可以减轻报表设计的工作量,也提高报表设计的灵活性,是生成报表的一种非常有效的方法。

参考文献

[1]Eric A.Visual Basic 6.0宝典[M].蒋洪军,译.北京:电子工业出版社,1999.