資源描述:
《用vb操作excel方法匯總》由會(huì)員上傳分享,免費(fèi)在線閱讀,更多相關(guān)內(nèi)容在工程資料-天天文庫(kù)。
1、VB控制EXCEL用VB操作excel方法匯總PrivateSubCommand3_Click()????DimiAsLong??DimjAsLong??DimobjExlAsExcel.Application??'聲明對(duì)象變量??Me.MousePointer=11????????'改變鼠標(biāo)樣式??SetobjExl=NewExcel.Application??'初始化對(duì)象變量??objExl.SheetsInNewWorkbook=1??'將新建的工作薄數(shù)量設(shè)為1??objExl.Workbooks.Add??????'增加一個(gè)工作薄??objExl.Sheets(objExl.S
2、heets.Count).Name="book1"??'修改工作薄名稱??objExl.Sheets.Add,objExl.Sheets("book1")'增加第二個(gè)工作薄在第一個(gè)之后??objExl.Sheets(objExl.Sheets.Count).Name="book2"??objExl.Sheets.Add,objExl.Sheets("book2")'增加第三個(gè)工作薄在第二個(gè)之后??objExl.Sheets(objExl.Sheets.Count).Name="book3"??objExl.Sheets("book1").Select????'選中工作薄?
3、?Fori=1To50????????????'循環(huán)寫入數(shù)據(jù)??????Forj=1To5????????Ifi=1Then??????????objExl.Selection.NumberFormatLocal="@"??'設(shè)置格式為文本??????????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=T
4、rue??'設(shè)為粗體??objExl.Selection.Font.Size=24????'設(shè)置字體大小??objExl.Cells.EntireColumn.AutoFit??'自動(dòng)調(diào)整列寬??objExl.ActiveWindow.SplitRow=1??'拆分第一行??objExl.ActiveWindow.SplitColumn=0??'拆分列??objExl.ActiveWindow.FreezePanes=True??'固定拆分??objExl.ActiveSheet.PageSetup.PrintTitleRows="$1:$1"??'設(shè)置打印固定行??objExl.Ac
5、tiveSheet.PageSetup.PrintTitleColumns=""??'打印標(biāo)題??objExl.ActiveSheet.PageSetup.RightFooter="打印時(shí)間:"&_????????????????????????????????Format(Now,"yyyy年mm月dd日hh:MM:ss")??objExl.ActiveWindow.View=xlPageBreakPreview??'設(shè)置顯示方式??objExl.ActiveWindow.Zoom=100????????????'設(shè)置顯示大小??'給工作表加密碼??objExl.ActiveShee
6、t.Protect"123",DrawingObjects:=True,_????????????????????Contents:=True,Scenarios:=True??objExl.Application.IgnoreRemoteRequests=False??objExl.Visible=True?????????????第20頁(yè)共20頁(yè)VB控制EXCEL???'使EXCEL可見(jiàn)??objExl.Application.WindowState=xlMaximized??'EXCEL的顯示方式為最大化??objExl.ActiveWindow.WindowState=xlMa
7、ximized??'工作薄顯示方式為最大化??objExl.SheetsInNewWorkbook=3????????'將默認(rèn)新工作薄數(shù)量改回3個(gè)??SetobjExl=Nothing??'清除對(duì)象??Me.MousePointer=0??'修改鼠標(biāo)??ExitSubEndSub全面控制Excel首先創(chuàng)建Excel對(duì)象,使用ComObj:DimExcelIDasExcel.ApplicationSetExcelIDasnewExcel.Appl