用VB操作Excel（VB6.0）（整理）精品资料.doc

《用VB操作Excel（VB6.0）（整理）精品资料.doc》由会员分享，可在线阅读，更多相关《用VB操作Excel（VB6.0）（整理）精品资料.doc（42页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、用VB操作Excel(VB6.0)(整理)、一、 VB读写EXCEL表：VB本身提自动化功能可以读写EXCEL表，其方法如下：1、在工程中引用Microsoft Excel类型库：从工程菜单中选择引用栏；选择Microsoft Excel 9.0 Object Library（EXCEL2000），然后选择确定。表示在工程中要引用EXCEL类型库。2、在通用对象的声明过程中定义EXCEL对象：Dim xlApp As Excel.ApplicationDim xlBook As Excel.WorkBookDim xlSheet As Excel.Worksheet 3、在程序中操作EXCEL

2、表常用命令：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 结束EX

3、CEL对象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脱节。二、 E

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

5、ok.RunAutoMacros (xlAutoOpen)和xlBook.RunAutoMacros (xlAutoClose) 来运行启动宏和关闭宏。三、 VB与EXCEL的相互勾通：充分利用EXCEL的启动宏和关闭宏，可以实现VB与EXCEL的相互勾通，其方法如下：在EXCEL的启动宏中加入一段程序，其功能是在磁盘中写入一个标志文件，同时在关闭宏中加入一段删除此标志文件的程序。VB程序在执行时通过判断此标志文件存在与否来判断EXCEL是否打开，如果此标志文件存在，表明EXCEL对象正在运行，应该禁止其它程序的运行。如果此标志文件不存在，表明EXCEL对象已被用户关闭，此时如果要使用EXCE

6、L对象运行，必须重新创建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:tempexcel.bz) = Then 判断EX

7、CEL是否打开Set xlApp = CreateObject(Excel.Application) 创建EXCEL应用类xlApp.Visible = True 设置EXCEL可见Set xlBook = xlApp.Workbooks.Open(D:tempbb.xls) 打开EXCEL工作簿Set xlsheet = xlBook.Worksheets(1) 打开EXCEL工作表xlsheet.Activate 激活工作表xlsheet.Cells(1, 1) = abc 给单元格1行驶列赋值xlBook.RunAutoMacros (xlAutoOpen) 运行EXCEL中的启动宏El

8、seMsgBox (EXCEL已打开) End IfEnd SubPrivate Sub Command2_Click()If Dir(D:tempexcel.bz) Then 由VB关闭EXCEL xlBook.RunAutoMacros (xlAutoClose) 执行EXCEL关闭宏xlBook.Close (True) 关闭EXCEL工作簿xlApp.Quit 关闭EXCELEnd IfSet xlApp = Nothing 释放EXCEL对象EndEnd Sub2、在盘根目录上建立一个名为Temp的子目录，在Temp目录下建立一个名为bb.xls的EXCEL文件。3、在bb.xls中

9、打开Visual Basic编辑器，在工程窗口中点鼠标键选择插入模块，在模块中输入入下程序存盘：Sub auto_open()Open d:tempexcel.bz For Output As #1 写标志文件Close #1End SubSub auto_close()Kill d:tempexcel.bz 删除标志文件End Sub 4、运行VB程序，点击EXCEL按钮可以打开EXCEL系统，打开EXCEL系统后，VB程序和EXCEL分别属两个不同的应用系统，均可同时进行操作，由于系统加了判断，因此在VB程序中重复点击EXCEL按钮时会提示EXCEL已打开。如果在EXCEL中关闭EXCEL

10、后再点EXCEL按钮，则会重新打开EXCEL。而无论EXCEL打开与否，通过VB程序均可关闭EXCEL。这样就实现了VB与EXCEL的无缝连接。首先创建Excel对象，使用ComObj:Dim ExcelID as Excel.ApplicationSet ExcelID as new Excel.Application1)显示当前窗口：ExcelID.Visible:=True;2)更改Excel标题栏：ExcelID.Caption:=应用程序调用MicrosoftExcel;3)添加新工作簿：ExcelID.WorkBooks.Add;4)打开已存在的工作簿：ExcelID.WorkBo

11、oks.Open(C:ExcelDemo.xls);5)设置第2个工作表为活动工作表：ExcelID.WorkSheets2.Activate; 或ExcelID.WorkSheetsSheet2.Activate;6)给单元格赋值：ExcelID.Cells1,4.Value:=第一行第四列;7)设置指定列的宽度（单位：字符个数），以第一列为例：ExcelID.ActiveSheet.Columns1.ColumnsWidth:=5;8)设置指定行的高度（单位：磅）（1磅0.035厘米），以第二行为例：ExcelID.ActiveSheet.Rows2.RowHeight:=1/0.035;

12、/1厘米9)在第8行之前插入分页符：ExcelID.WorkSheets1.Rows8.PageBreak:=1;10)在第8列之前删除分页符：ExcelID.ActiveSheet.Columns4.PageBreak:=0;11)指定边框线宽度：ExcelID.ActiveSheet.RangeB3:D4.Borders2.Weight:=3;With xlBook.ActiveSheet.Range(B2:L + Trim(Str(i + 1).Borders1-左 2-右 3-顶4-底 5-斜() 6-斜(/)12)清除第一行第四列单元格公式：ExcelID.ActiveSheet.C

13、ells1,4.ClearContents;13)设置第一行字体属性：ExcelID.ActiveSheet.Rows1.Font.Name:=隶书;ExcelID.ActiveSheet.Rows1.Font.Color :=clBlue;ExcelID.ActiveSheet.Rows1.Font.Bold :=True;ExcelID.ActiveSheet.Rows1.Font.UnderLine:=True;14)进行页面设置：a.页眉：ExcelID.ActiveSheet.PageSetup.CenterHeader:=报表演示;b.页脚：ExcelID.ActiveSheet.

14、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.Activ

15、eSheet.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.

16、拷贝整个工作表：ExcelID.ActiveSheet.Used.Range.Copy;b.拷贝指定区域：ExcelID.ActiveSheet.RangeA1:E2.Copy;c.从A1位置开始粘贴：ExcelID.ActiveSheet.Range.A1.PasteSpecial;d.从文件尾部开始粘贴：ExcelID.ActiveSheet.Range.PasteSpecial;16)插入一行或一列：a.ExcelID.ActiveSheet.Rows2.Insert;b.ExcelID.ActiveSheet.Columns1.Insert;17)删除一行或一列：a.ExcelID.A

17、ctiveSheet.Rows2.Delete;b.ExcelID.ActiveSheet.Columns1.Delete;18)打印预览工作表：ExcelID.ActiveSheet.PrintPreview;19)打印输出工作表：ExcelID.ActiveSheet.PrintOut;20)工作表保存：IfnotExcelID.ActiveWorkBook.Savedthen ExcelID.ActiveSheet.PrintPreviewEndif21)工作表另存为：ExcelID.SaveAs(C:ExcelDemo1.xls);22)放弃存盘：ExcelID.ActiveWorkB

18、ook.Saved:=True;23)关闭工作簿：ExcelID.WorkBooks.Close;24)退出Excel：ExcelID.Quit;25)设置工作表密码:ExcelID.ActiveSheet.Protect123,DrawingObjects:=True,Contents:=True,Scenarios:=True26)EXCEL的显示方式为最大化ExcelID.Application.WindowState=xlMaximized 27)工作薄显示方式为最大化ExcelID.ActiveWindow.WindowState=xlMaximized 28)设置打开默认工作薄数量

19、ExcelID.SheetsInNewWorkbook=329)关闭时是否提示保存(true保存；false不保存)ExcelID.DisplayAlerts=False 30)设置拆分窗口,及固定行位置ExcelID.ActiveWindow.SplitRow=1ExcelID.ActiveWindow.FreezePanes=True31)设置打印时固定打印内容ExcelID.ActiveSheet.PageSetup.PrintTitleRows=$1:$1 32)设置打印标题ExcelID.ActiveSheet.PageSetup.PrintTitleColumns=33)设置显示方

20、式(分页方式显示)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 err1Dim i As LongDim j As LongDim objExl As Excel.Application 声明对象变量Me.MousePointer=11 改变鼠标样式S

21、et objExl=New Excel.Application初始化对象变量objExl.SheetsInNewWorkbook=1 将新建的工作薄数量设为1objExl.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)增加第三

22、个工作薄在第二个之后objExl.Sheets(objExl.Sheets.Count).Name=book3 objExl.Sheets(book1).Select 选中工作薄For i=1 To 50循环写入数据For j=1 To 5If i=1 Then objExl.Selection.NumberFormatLocal= 设置格式为文本 objExl.Cells(i,j)=E&i&jElse objExl.Cells(i,j)=i&jEndIfNextNextobjExl.Rows(1:1).Select 选中第一行objExl.Selection.Font.Bold=True 设

23、为粗体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=打印

24、标题objExl.ActiveSheet.PageSetup.RightFooter=打印时间:&_Format(Now,yyyy年mm月dd日hh:MM:ss)objExl.ActiveWindow.View=xlPageBreakPreview设置显示方式objExl.ActiveWindow.Zoom=100 设置显示大小给工作表加密码objExl.ActiveSheet.Protect123,DrawingObjects:=True, _Contents:=True,Scenarios:=TrueobjExl.Application.IgnoreRemoteRequests=False

25、objExl.Visible=True 使EXCEL可见objExl.Application.WindowState=xlMaximizedEXCEL的显示方式为最大化objExl.ActiveWindow.WindowState=xlMaximized工作薄显示方式为最大化objExl.SheetsInNewWorkbook=3 将默认新工作薄数量改回3个 Set objExl=Nothing清除对象Me.MousePointer=0 修改鼠标ExitSuberr1: objExl.SheetsInNewWorkbook=3objExl.DisplayAlerts=False 关闭时不提示保

26、存objExl.Quit关闭EXCELobjExl.DisplayAlerts=True 关闭时提示保存Set objExl=NothingMe.MousePointer=0End Sub本人最近在做个程序，由于以前没涉及过此类编程，且手头没有相关技术资料，于是遂在网上淘金。可不幸的是网上搜到的此方面的代码大都是抄袭而来，错误太多，几乎无法使用和参考。为此，本人结合网上的资料和自己的研究分析，做了很多测试。以下代码为本人初次用VB6控制并读写EXCEL时，测试的部分代码，基本都通过了测试。在这里特意把更正且通过测试的代码做了笔记，便于自己需要时直接参考。 测试环境：VB6.0sp6企业版、Of

27、fice2003、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,

28、 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

29、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文件

30、Set xlSheet = xlBook.Sheets(1) 设置活动工作表(1) * 试验代码部分 xlSheet.Cells(1, 1) = A 把第一行第一列改成A xlSheet.Cells(1, 2) = B 把第一行第二列改成B xlSheet.Cells(2, 1) = C 把第二行第一列改成C xlSheet.Cells(3, 2) = D 把第3行第二列改成D * 显示当前窗口 xlApp.Visible = True 设置EXCEL对象可见（或不可见） 更改Excel标题栏 xlApp.Caption = CDMA基站告警后台分析系统报表 修改单元格颜色 With xlBo

31、ok.Sheets(1) 第1张工作表 或者With xlBook.ActiveSheet .Range(A1, F10).Font.Color = RGB(112, 220, 21) End With 修改单元格边框 With xlBook.ActiveSheet.Range(A2:K9).Borders 边框设置 .LineStyle = 1 xlBorderLineStyleContinuous .Weight = 1 xlThin .ColorIndex = 1 End With 单元格字体设置 With xlBook.ActiveSheet.Range(A3:K9).Font 字体设

32、置 .Size = 9 .Bold = True .Italic = True .ColorIndex = 1 End With 设置单元格格式为文本格式 xlBook.Sheets(1).Selection.NumberFormatLocal = 设置格式为文本,没有通过 准备修改EXCEL单元格内容 i = 0 While Not EOF(iOutNum) Line Input #iOutNum, sStr 顺序按行读取 sStr = Trim(sStr) 压缩空格 If Len(Trim(sStr) 0 Then s() = Split(sStr, ,) 空格分隔符 iLen = UBo

33、und(s() 写EXCEL单元格 i = i + 1 记录号+1 For j = 0 To iLen xlSheet.Cells(i + 1, j + 2) = Trim(s(j) Next j End If Wend 其它设置 xlSheet.Rows(2:2).Select 选中第一行 xlApp.Columns(A:E).Select 选中AE列 xlApp.Selection.Font.Bold = True 设为粗体 xlApp.Selection.Font.Size = 10 设置字体大小 xlApp.Cells.EntireColumn.AutoFit 自动调整列宽 xlBoo

34、k.Sheets(1).Cells.EntireColumn.AutoFit 自动调整列宽 拆分 xlApp.ActiveWindow.SplitRow = 1 拆分第一行 xlApp.ActiveWindow.SplitColumn = 2 拆分第2列 设置指定列宽、或行高:（单位：字符个数） xlBook.Sheets(1).Columns(1).ColumnsWidth = 5 xlBook.Sheets(1).Rows(2).RowHeight = 5 / 0.035 /1厘米 激活某个工作表为活动工作表 xlApp.WorkSheets(2).Activate Set xlSheet

35、 = xlBook.Sheets(3) 边框设置,测试通过 With xlBook.ActiveSheet.Range(A2:K9).Borders 边框设置 .LineStyle = 1 xlBorderLineStyleContinuous细黑实线 .Weight = 2 xlThin .ColorIndex = 1 End With 进行页面设置:代码真TMD灵活 页眉: xlBook.ActiveSheet.PageSetup.CenterHeader = XXXXC网基站掉站后台分析报表 页脚: xlApp.WorkSheets(2).PageSetup.CenterFooter =

36、 第&P页 页眉到顶端边距2cm: xlApp.WorkSheets(2).PageSetup.HeaderMargin = 2 / 0.035 页脚到底端边距3cm: xlBook.Sheets(1).PageSetup.HeaderMargin = 3 / 0.035 顶边距2cm: xlApp.WorkSheets(2).PageSetup.TopMargin = 2 / 0.035 底边距2cm: xlApp.WorkSheets(2).PageSetup.BottomMargin = 2 / 0.035 左边距2cm: xlApp.WorkSheets(2).PageSetup.Le

37、ftMargin = 2 / 0.035 右边距2cm: xlApp.WorkSheets(2).PageSetup.RightMargin = 2 / 0.035 页面水平居中: xlApp.WorkSheets(2).PageSetup.CenterHorizontally = 2 / 0.035 页面垂直居中: xlApp.WorkSheets(2).PageSetup.CenterVertically = 2 / 0.035 打印单元格网线: xlApp.WorkSheets(2).PageSetup.PrintGridLines = True 拷贝操作： 拷贝整个工作表: xlApp

38、.ActiveSheet.Used.Range.Copy有问题！ 拷贝指定区域: xlApp.WorkSheets(1).Range(A1:E2).Copy 从A1位置开始粘贴: xlApp.WorkSheets(2).Range(A1:E2).PasteSpecial ExcelID.ActiveSheet.Range.A1.PasteSpecial 从文件尾部开始粘贴: ExcelID.ActiveSheet.Range.PasteSpecial 打印预览工作表： xlApp.ActiveSheet.PrintPreview 打印输出工作表 xlApp.ActiveSheet.PrintO

39、ut 工作表保存,预览 If Not xlApp.ActiveWorkBook.Saved Then xlApp.ActiveSheet.PrintPreview End If 增加新工作簿、表 xlApp.WorkBooks.Add 增加一个工作薄 xlApp.WorkSheets.Add 增加一个工作表 工作表另存为： xlBook.SaveAs (C:Demo1.xls) 测试通过 设置工作表的名称 xlApp.Worksheets(2).Name = 测试工作表 放弃存盘： xlApp.ActiveWorkBook.Saved = True 测试通过 关闭工作簿： xlApp.Work

40、Books.Close 退出Excel： xlApp.Quit 保存设置 xlApp.DisplayAlerts = False 关闭时不提示保存 xlApp.DisplayAlerts = True 关闭时提示保存 xlApp.Quit 关闭EXCEL 释放资源 Set xlApp = Nothing 释放xlApp对象 Set xlBook = Nothing Set xlSheet = Nothing Screen.MousePointer = vbDefault Close iOutNum End Sub 唯一不足的是，像.LineStyle = xlBorderLineStyleCo

41、ntinuous这类代码，=右边的常量xlBorderLineStyleContinuous，类似的在网上几乎搜不到完整的定义，相当郁闷 最终成品(关键)代码，用于自动生成EXCEL报表： Private Sub 生成报表() 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 fOutFil

42、e 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) Set xlBook = xlApp.WorkBooks.Add 指定第1个工作表sheets(

43、1) Set xlSheet = xlBook.Sheets(1) 激活某个工作表为活动工作表 xlApp.Worksheets(1).Activate 显示当前窗口 xlApp.Visible = True 更改Excel标题栏 xlApp.Caption = 淮北(Cdma)基站告警报表 设置工作表的名称 xlApp.WorkSheets(1).Name = CDate(Date) 单元格颜色设置 With xlBook.Sheets(1) .Range(C2:C3).Font.Color = vbRed .Range(I2:L3).Font.Color = vbRed .Range(xl

44、Sheet.Cells(2, 9), xlSheet.Cells(2, 12).Font.Color = vbRed效率低下 End With 单元格合并 With xlSheet .Range(B2:B3).MergeCells = True 合并 .Range(C2:C3).MergeCells = True .Range(D2:D3).MergeCells = True .Range(E2:E3).MergeCells = True .Range(G2:G3).MergeCells = True .Range(H2:H3).MergeCells = True .Range(I2:I3).MergeCells = True .Range(J2:J3).MergeCells =