vb操作excel.docx
《vb操作excel.docx》由会员分享，可在线阅读，更多相关《vb操作excel.docx（20页珍藏版）》请在冰点文库上搜索。
vb操作excel
用VB操作Excel详解
一、 VB读写EXCEL表：
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） 来运行启动宏和关闭宏。
三、 VB与EXCEL的相互勾通：
充分利用EXCEL的启动宏和关闭宏，可以实现VB与EXCEL的相互勾通，其方法如下：
在EXCEL的启动宏中加入一段程序，其功能是在磁盘中写入一个标志文件，同时在关闭宏中加入一段删除此标志文件的程序。
VB程序在
执行时通过判断此标志文件存在与否来判断EXCEL是否打开，如果此标志文件存在，表明EXCEL对象正在运行，应该禁止其它程序的运行。
如果此标志文件不存在，表明EXCEL对象已被用户关闭，此时如果要使用EXCEL对象运行，必须重新创建EXCEL对象。
四、举例：
1、在VB中，建立一个FORM，在其上放置两个命令按钮，将Ｃommand1的Caption属性改为EXCEL，Command2的Caption属性改为End。
然后 在其中输入如下程序：
Dim xlApp As Excel.Application '定义EXCEL类
Dim xlBook As Excel.Workbook '定义工件簿类
Dim xlsheet As Excel.Worksheet '定义工作表类
Private Sub Command1_Click（） '打开EXCEL过程
If Dir（"D:
\temp\excel.bz"） = "" Then '判断EXCEL是否打开
Set xlApp = CreateObject（"Excel.Application"） '创建EXCEL应用类
xlApp.Visible = True '设置EXCEL可见
Set xlBook = xlApp.Workbooks.Open（"D:
\temp\bb.xls"） '打开EXCEL工作簿
Set xlsheet = xlBook.Worksheets
（1） '打开EXCEL工作表
xlsheet.Activate '激活工作表
xlsheet.Cells（1, 1） = "abc" '给单元格1行驶列赋值
xlBook.RunAutoMacros （xlAutoOpen） '运行EXCEL中的启动宏
Else
MsgBox （"EXCEL已打开"）
End If
End Sub
Private Sub Command2_Click（）
If Dir（"D:
\temp\excel.bz"） <> "" Then '由VB关闭EXCEL
xlBook.RunAutoMacros （xlAutoClose） '执行EXCEL关闭宏
xlBook.Close （True） '关闭EXCEL工作簿
xlApp.Quit '关闭EXCEL End If
Set xlApp = Nothing '释放EXCEL对象
End If
End Sub
2、在Ｄ盘根目录上建立一个名为Temp的子目录，在Temp目录下建立一个名为"bb.xls"的EXCEL文件。
3、在"bb.xls"中打开Visual Basic编辑器，在工程窗口中点鼠标键选择插入模块，在模块中输入入下程序存盘：
Sub auto_open（）
Open "d:
\temp\excel.bz" For Output As #1 '写标志文件 Close #1 End Sub Sub auto_close（）
Kill "d:
\temp\excel.bz" '删除标志文件 End Sub
4、运行VB程序，点击EXCEL按钮可以打开EXCEL系统，打开EXCEL系统后，VB程序和EXCEL分别属两个不同的应用系统，均可同时进行操作，
由于系统加了判断，因此在VB程序中重复点击EXCEL按钮时会提示EXCEL已打开。
如果在EXCEL中关闭EXCEL后再点EXCEL按钮，则会重新打开
EXCEL。
而无论EXCEL打开与否，通过VB程序均可关闭EXCEL。
这样就实现了VB与EXCEL的无缝连接。
首先创建Excel对象，使用
ComObj:
Dim ExcelID as Excel.Application
Set ExcelID as new Excel.Application
1）显示当前窗口：
ExcelID.Visible:
=True;
2）更改Excel标题栏：
ExcelID.Caption:
='应用程序调用MicrosoftExcel';
3）添加新工作簿：
ExcelID.WorkBooks.Add;
4）打开已存在的工作簿：
ExcelID.WorkBooks.Open（'C:
\Excel\Demo.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;
10）在第8列之前删除分页符：
ExcelID.ActiveSheet.Columns[4].PageBreak:
=0;
11）指定边框线宽度：
ExcelID.ActiveSheet.Range['B3:
D4'].Borders[2].Weight:
=3;
With xlBook.ActiveSheet.Range（"B2:
L" + Trim（Str（i + 1）））.Borders 1-左 2-右 3-顶4-底 5-斜（\） 6-斜（/）
12）清除第一行第四列单元格公式：
ExcelID.ActiveSheet.Cells[1,4].ClearContents;
13）设置第一行字体属性：
ExcelID.ActiveSheet.Rows[1].Font.Name:
='隶书'; 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.拷贝整个工作表：
ExcelID.ActiveSheet.Used.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）工作表保存：
IfnotExcelID.ActiveWorkBook.Savedthen
ExcelID.ActiveSheet.PrintPreview Endif
21）工作表另存为：
ExcelID.SaveAs（'C:
\Excel\Demo1.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
objExl.Selection.NumberFormatLocal="@" '设置格式为文本
objExl.Cells（i,j）="E"&i&j
Else
objExl.Cells（i,j）=i&j
EndIf
Next
Next
objExl.Rows（"1:
1"）.Select '选中第一行
objExl.Selection.Font.Bold=True '设为粗体
objExl.Selection.Font.Size=24 '设置字体大小
objExl.Cells.EntireColumn.AutoFit '自动调整列宽
objExl.ActiveWindow.SplitRow=1 '拆分第一行
objExl.ActiveWindow.SplitColumn=0 '拆分列
objExl.ActiveWindow.FreezePanes=True '固定拆分
objExl.ActiveSheet.PageSetup.PrintTitleRows="$1:
$1" '设置打印固定行
objExl.ActiveSheet.PageSetup.PrintTitleColumns=""'打印标题objExl.ActiveSheet.PageSetup.RightFooter="打印时间:
"&_ Format（Now,"yyyy年mm月dd日hh:
MM:
ss"）
objExl.ActiveWindow.View=xlPageBreakPreview'设置显示方式
objExl.ActiveWindow.Zoom=100 '设置显示大小 '给工作表加密码
objExl.ActiveSheet.Protect"123",
DrawingObjects:
=True,
_ Contents:
=True,
Scenarios:
=True
objExl.Application.IgnoreRemoteRequests=False
objExl.Visible=True '使EXCEL可见
objExl.Application.WindowState=xlMaximized'EXCEL的显示方式为最大化
objExl.ActiveWindow.WindowState=xlMaximized'工作薄显示方式为最大化
objExl.SheetsInNewWorkbook=3 '将默认新工作薄数量改回3个
Set objExl=Nothing'清除对象
Me.MousePointer=0 '修改鼠标
ExitSub
err1:
objExl.SheetsInNewWorkbook=3
objExl.DisplayAlerts=False '关闭时不提示保存 objExl.Quit'关闭EXCEL
objExl.DisplayAlerts=True '关闭时提示保存
Set objExl=Nothing
Me.MousePointer=0
End Sub
本人最近在做个程序，由于以前没涉及过此类编程，且手头没有相关技术资料，于是遂在网上淘金。
可不幸的是网上搜到的此方面的代码大都是抄袭而来，错误太多，几乎无法使用和参考。
为此，本人结合网上的资料和自己的研究分析，做了很多测试。
以下代码为本人初次用VB6控制并读写EXCEL时，测试的部分代码，基本都通过了测试。
在这里特意把更正且通过测试的代码做了笔记，便于自己需要时直接参考。
测试环境：
VB6.0sp6企业版、Office2003、WinXP
真确的引用：
“工程（P）”—“引用（N）”—引用项目 Excel 2000中：
Microsoft Excel 9.0 Object Library Microsoft Office 9.0 Object Library Excel 2003中：
Microsoft Excel 11.0 Object Library Microsoft Office 11.0 Object Library
Private Sub Test_Click（） '--------------------------------
' 功能：
读RESULT.TXT到EXCEL中去 ' 用法：
主程序自动调用处理 '-------------------------------- '打开最终结果文件resulr.txt
Dim i, j, iLen As Integer
Dim s（） As String
Dim sStr As String
If iOutNum <> 0 Then
Close iOutNum '先关闭
End If
fOutFile = Trim（App.Path） & "\Result.txt" '最终格式化结果
iOutNum = FreeFile（）
Open fOutFile For Input As #iOutNum '控制EXCEL代码
Dim xlApp As Object 'Excel.Application
Dim xlBook As Object 'Excel.Workbook
Dim xlSheet As Object 'Excel.Worksheet
Screen.MousePointer = vbHourglass '改变鼠标样式 '
On Error GoTo Err_Proc
Set xlApp = CreateObject（"Excel.Application"） '创建新的EXCEL文件
Set xlBook = xlApp.WorkBooks.Add Set xlSheet = xlBook.WorkSheets
（1）
'Begin to fill data to sheet
'Set xlBook = xlApp.Workbooks.Open（"c:
\1.xls"） '打开已存在的某EXCEL文件
Set xlSheet = xlBook.Sheets
（1） '设置活动工作表
（1） '*********************************************************** '试验代码部分
xlSheet.Cells（1, 1） = "
升级会员 