vb操作excel及宏操作

vB是常用的应用软件开发工具之一，由于VB的报表功能有限，而且一但报表格式发生变化，就得相应修改程序，给应用软件的维护工作带来极大的不便。因此有很多程序员现在已经充分利用EXECL的强大报表功来实现报表功能。但由于VB与EXCEL由于分别属于不同的应用系统，如何把它们有机地结合在一起，是一个值得我们研究的课题。

一、VB读写EXCEL表：

能获取到表的行数的代码：https://www.360docs.net/doc/8516707996.html,edRange.Rows.Count

VB本身提自动化功能可以读写EXCEL表，其方法如下：

1、在工程中引用Microsoft Excel类型库：

从"工程"菜单中选择"引用"栏；选择Microsoft Excel 9.0 Object Library（EXCEL2000），然后选择"确定"。表示在工程中要引用EXCEL类型库。

2、在通用对象的声明过程中定义EXCEL对象：

Dim xlApp As Excel.Application

Dim xlBook As Excel.WorkBook

Dim xlSheet As Excel.Worksheet

3、在程序中操作EXCEL表常用命令：

Set xlApp = CreateObject("Excel.Application") '创建EXCEL对象

Set xlBook = xlApp.Workbooks.Open("文件名") '打开已经存在的EXCEL工件簿文件xlApp.Visible = True '设置EXCEL对象可见（或不可见）

Set xlSheet = xlBook.Worksheets("表名") '设置活动工作表

xlSheet.Cells(row, col) =值'给单元格（row,col）赋值

xlSheet.PrintOut '打印工作表

xlBook.Close (True) '关闭工作簿

xlApp.Quit '结束EXCEL对象

Set xlApp = Nothing '释放xlApp对象

xlBook.RunAutoMacros (xlAutoOpen) '运行EXCEL启动宏

xlBook.RunAutoMacros (xlAutoClose) '运行EXCEL关闭宏

4、在运用以上VB命令操作EXCEL表时，除非设置EXCEL对象不可见，否则VB程序可继续执行其它操作，也能够关闭EXCEL，同时也可对EXCEL进行操作。但在EXCEL 操作过程中关闭EXCEL对象时，VB程序无法知道，如果此时使用EXCEL对象，则VB程序会产生自动化错误。形成VB程序无法完全控制EXCEL的状况，使得VB与EXCEL脱节。

二、EXCEL的宏功能：

EXCEL提供一个Visual Basic编辑器，打开Visual Basic编辑器，其中有一工程属性窗口，点击右键菜单的"插入模块"，则增加一个"模块1"，在此模块中可以运用Visual Basic语言编写函数和过程并称之为宏。其中，EXCEL有两个自动宏：一个是启动宏（Sub Auto_Open()），另一个是关闭宏（Sub Auto_Close()）。它们的特性是：当用EXCEL打含有启动宏的工簿时，就会自动运行启动宏，同理，当关闭含有关闭宏的工作簿时就会自动运行关闭宏。但是通过VB的自动化功能来调用EXCEL工作表时，启动宏和关闭宏不会自动运行，而需要在VB中通过命令xlBook.RunAutoMacros (xlAutoOpen)和xlBook.RunAutoMacros (xlAutoClose)来运行启动宏和关闭宏。

Set xlApp = CreateObject("Excel.Application") '创建EXCEL对象

Set xlBook = xlApp.Workbooks.Open(App.Path & "\文件名.xls") '打开已经存在的EXCEL工件簿文件

xlApp.Visible = True '设置EXCEL对象可见（或不可见）

Set xlSheet = xlBook.Worksheets("sheel1") '设置活动工作表，sheet1表示表名，可以使用字符型变量代替。

xlSheet.Activate '激活工作表，让它处于前台活动中。

xlBook.RunAutoMacros (xlAutoOpen) '运行自动开启宏Macro1()，在EXCEL中加入以下宏'Sub auto_open() '这是VBA中的自动打开时运行的宏

'您要宏自动运行的程序，比如可以call其它的宏，这样VB在打开EXCEL时，使用上面的语句就可以先运行这个宏

'End Sub

xlSheet.Cells.Item(行数,列数) =值'给EXCEL对象中的表的单元格附值。

xlSheet.Rows("7:7").Select '选定EXCEL表的第7行

xlApp.Selection.Delete shift:=xlUp '删除选定的行

xlSheet.Rows(7).Insert '在第7行处插入一行

ActiveSheet.PageSetup.CenterHeader ="页眉内容" '给页眉附值

xlSheet.Range("f7:h7").Select '选定f7:h7之间的单元格，下面的程序是把它们合并。Application.CutCopyMode = False

With xlApp.Selection

.HorizontalAlignment = xlGeneral

.VerticalAlignment = xlCenter

.WrapText = False

.Orientation = 0

.AddIndent = False

.IndentLevel = 0

.ShrinkToFit = False

.ReadingOrder = xlContext

.MergeCells = True

End With

如果有自己不会的可以用录制宏的办法，用手功操作一次你要的功能，然后看录下的宏程序，VBA中的宏程序几乎所有的宏在VB中都可以直接使用，关键是对象一定要搞清楚。

VB操作EXCEL的介绍

全面控制Excel

首先创建Excel对象，使用ComObj:

Dim ExcelID as Excel.Application

Set ExcelID as new Excel.Application

1)显示当前窗口：

ExcelID.Visible := True;

2)更改Excel标题栏：

ExcelID.Caption := '应用程序调用Microsoft Excel';

3)添加新工作簿：

ExcelID.WorkBooks.Add;

4)打开已存在的工作簿：

ExcelID.WorkBooks.Open( 'C:ExcelDemo.xls' );

5)设置第2个工作表为活动工作表：

ExcelID.WorkSheets[2].Activate;

或ExcelID.WorkSheets[ 'Sheet2' ].Activate;

6)给单元格赋值：

ExcelID.Cells[1,4].Value := '第一行第四列';

7)设置指定列的宽度（单位：字符个数），以第一列为例：ExcelID.ActiveSheet.Columns[1].ColumnsWidth := 5;

8)设置指定行的高度（单位：磅）（1磅＝0.035厘米），以第二行为例：ExcelID.ActiveSheet.Rows[2].RowHeight := 1/0.035; // 1厘米

9)在第8行之前插入分页符：

ExcelID.WorkSheets[1].Rows[8].PageBreak := 1;

参考代码：ActiveSheet.HPageBreaks(1).Location = Range("A22")

10)在第8列之前删除分页符：

ExcelID.ActiveSheet.Columns[4].PageBreak := 0;

11)指定边框线宽度：

ExcelID.ActiveSheet.Range[ 'B3:D4' ].Borders[2].Weight := 3;

1-左2-右3-顶4-底5-斜( ) 6-斜( / )

12)清除第一行第四列单元格公式：

ExcelID.ActiveSheet.Cells[1,4].ClearContents;

13)设置第一行字体属性：

ExcelID.ActiveSheet.Rows[1]https://www.360docs.net/doc/8516707996.html, := '隶书';

ExcelID.ActiveSheet.Rows[1].Font.Color := clBlue;

ExcelID.ActiveSheet.Rows[1].Font.Bold := True;

ExcelID.ActiveSheet.Rows[1].Font.UnderLine := True;

14)进行页面设置：

a.页眉：

ExcelID.ActiveSheet.PageSetup.CenterHeader := '报表演示';

b.页脚：

ExcelID.ActiveSheet.PageSetup.CenterFooter := '第&P页';

c.页眉到顶端边距2cm：

ExcelID.ActiveSheet.PageSetup.HeaderMargin := 2/0.035;

d.页脚到底端边距3cm：

ExcelID.ActiveSheet.PageSetup.HeaderMargin := 3/0.035;

e.顶边距2cm：

ExcelID.ActiveSheet.PageSetup.TopMargin := 2/0.035;

f.底边距2cm：

ExcelID.ActiveSheet.PageSetup.BottomMargin := 2/0.035;

g.左边距2cm：

ExcelID.ActiveSheet.PageSetup.LeftMargin := 2/0.035;

h.右边距2cm：

ExcelID.ActiveSheet.PageSetup.RightMargin := 2/0.035;

i.页面水平居中：

ExcelID.ActiveSheet.PageSetup.CenterHorizontally := 2/0.035;

j.页面垂直居中：

ExcelID.ActiveSheet.PageSetup.CenterVertically := 2/0.035;

k.打印单元格网线：

ExcelID.ActiveSheet.PageSetup.PrintGridLines := True;

15)拷贝操作：

a.拷贝整个工作表：

https://www.360docs.net/doc/8516707996.html,ed.Range.Copy;

b.拷贝指定区域：

ExcelID.ActiveSheet.Range[ 'A1:E2' ].Copy;

c.从A1位置开始粘贴：

ExcelID.ActiveSheet.Range.[ 'A1' ].PasteSpecial;

d.从文件尾部开始粘贴：

ExcelID.ActiveSheet.Range.PasteSpecial;

16)插入一行或一列：

a. ExcelID.ActiveSheet.Rows[2].Insert;

b. ExcelID.ActiveSheet.Columns[1].Insert;

17)删除一行或一列：

a. ExcelID.ActiveSheet.Rows[2].Delete;

b. ExcelID.ActiveSheet.Columns[1].Delete;

18)打印预览工作表：

ExcelID.ActiveSheet.PrintPreview;

19)打印输出工作表：

ExcelID.ActiveSheet.PrintOut;

20)工作表保存：

If not ExcelID.ActiveWorkBook.Saved then

ExcelID.ActiveSheet.PrintPreview

End if

21)工作表另存为：

ExcelID.SaveAs( 'C:ExcelDemo1.xls' );

22)放弃存盘：

ExcelID.ActiveWorkBook.Saved := True;

23)关闭工作簿：

ExcelID.WorkBooks.Close;

24)退出Excel：

ExcelID.Quit;

25)设置工作表密码:

ExcelID.ActiveSheet.Protect "123", DrawingObjects:=True, Contents:=True, Scenarios:=True 26) EXCEL的显示方式为最大化

ExcelID.Application.WindowState = xlMaximized

27)工作薄显示方式为最大化

ExcelID.ActiveWindow.WindowState = xlMaximized

28)设置打开默认工作薄数量

ExcelID.SheetsInNewWorkbook = 3

29) '关闭时是否提示保存(true保存；false不保存)

ExcelID.DisplayAlerts = False

30)设置拆分窗口,及固定行位置

ExcelID.ActiveWindow.SplitRow = 1

ExcelID.ActiveWindow.FreezePanes = True

31)设置打印时固定打印内容

ExcelID.ActiveSheet.PageSetup.PrintTitleRows = "$1:$1"

32)设置打印标题

ExcelID.ActiveSheet.PageSetup.PrintTitleColumns = ""

33)设置显示方式(分页方式显示)

ExcelID.ActiveWindow.View = xlPageBreakPreview

34)设置显示比例

ExcelID.ActiveWindow.Zoom = 100

35)让Excel响应DDE请求

Ex.Application.IgnoreRemoteRequests = False

用VB操作EXCEL

以下是代码片段：

Private Sub Command3_Click()

On Error GoTo err1

Dim i As Long

Dim j As Long

Dim objExl As Excel.Application ’声明对象变量

Me.MousePointer = 11 ’改变鼠标样式

Set objExl = New Excel.Application ’初始化对象变量

objExl.SheetsInNewWorkbook = 1 ’将新建的工作薄数量设为1

objExl.Workbooks.Add ’增加一个工作薄

objExl.Sheets(objExl.Sheets.Count).Name = "book1" ’修改工作薄名称objExl.Sheets.Add , objExl.Sheets("book1") ‘增加第二个工作薄在第一个之后objExl.Sheets(objExl.Sheets.Count).Name = "book2"

objExl.Sheets.Add , objExl.Sheets("book2") ‘增加第三个工作薄在第二个之后objExl.Sheets(objExl.Sheets.Count).Name = "book3"

objExl.Sheets("book1").Select ’选中工作薄

For i = 1 To 50 ’循环写入数据

For j = 1 To 5

If i = 1 Then

objEx l.Selection.NumberFormatLocal = "@" ’设置格式为文本

objExl.Cells(i, j) = " E " & i & j

Else

objExl.Cells(i, j) = i & j

End If

Next

Next

objExl.Rows("1:1").Select ’选中第一行

objExl.Selection.Font.Bold = True ’设为粗体

objExl.Selection.Font.Size = 24 ’设置字体大小

objExl.Cells.E ntireColumn.AutoFit ’自动调整列宽

objExl.ActiveWindow.SplitRow = 1 ’拆分第一行

objExl.ActiveWindow. SplitColumn = 0 ’拆分列

objExl.ActiveWindow.FreezePanes = True ’固定拆分

objExl.ActiveSheet.PageSetup.PrintTitleRows = "$1:$1" ’设置打印固定行objExl.ActiveSheet.PageSetup.PrintTitl eColumns = "" ’打印标题

objExl.ActiveSheet.PageSetup.RightFooter = "打印时间: " & _

Format(Now, "yyyy年mm月dd日hh:MM:ss")

objExl.ActiveWindow.View = xlPageBreakPreview ’设置显示方式

objExl.ActiveWindow.Zoom = 100 ’设置显示大小

objExl.ActiveSheet.PageSetup.Orientation = xlLandsca pe ‘设置打印方向(横向) ’给工作表加密码

objExl.ActiveSheet.Protect "123", DrawingObjects:=True, _

Contents:=True, Scenarios:=True

objExl.Application.IgnoreRemoteRequests = False

objExl.Visible = True ’使EXCEL可见

objExl.Application.WindowState = xlMaximized ’EXCEL的显示方式为最大化o bjExl.ActiveWindow.WindowState = xlMaximized ’工作薄显示方式为最大化objExl.SheetsInNewWorkbook = 3 ’将默认新工作薄数量改回3个

Set objExl = Nothing ’清除对象

Me.MousePointer = 0 ’修改鼠标

Exit Sub

err1:

objExl.SheetsInNewWorkbook = 3

objExl.DisplayAlerts = False ’关闭时不提示保存

objExl.Quit ’关闭EXCEL

objExl.DisplayAlerts = True ’关闭时提示保存

Set objExl = Nothing

Me.MousePointer = 0

End Sub