用vb操作excel方法匯總

1、VB控制EXCEL第1頁共20頁用VB操作excel方法匯總PrivateSubComm3_Click()DimiAsLongDimjAsLongDimobjExlAsExcel.Application聲明對(duì)象變量Me.MousePointer=11改變鼠標(biāo)樣式SetobjExl=NewExcel.Application初始化對(duì)象變量objExl.SheetsInNewWkbook=1將新建的工作薄數(shù)量設(shè)為1objExl.Wkbooks

2、.Add增加一個(gè)工作薄objExl.Sheets(objExl.Sheets.Count).Name=“book1“修改工作薄名稱objExl.Sheets.AddobjExl.Sheets(“book1“)增加第二個(gè)工作薄在第一個(gè)之后objExl.Sheets(objExl.Sheets.Count).Name=“book2“objExl.Sheets.AddobjExl.Sheets(“book2“)增加第三個(gè)工作薄在第二個(gè)之后ob

3、jExl.Sheets(objExl.Sheets.Count).Name=“book3“objExl.Sheets(“book1“).選中工作薄Fi=1To50循環(huán)寫入數(shù)據(jù)Fj=1To5Ifi=1ThenobjExl.ion.NumberFmatLocal=“@“設(shè)置格式為文本objExl.Cells(ij)=“E“ExcelID.ActiveSheet.Rows[1].Font.UnderLine:=True14)進(jìn)行頁面設(shè)置：a.

4、頁眉：ExcelID.ActiveSheet.PageSetup.CenterHeader:=報(bào)表演示b.頁腳：ExcelID.ActiveSheet.PageSetup.CenterFooter:=第c.頁眉到頂端邊距2cm：ExcelID.ActiveSheet.PageSetup.HeaderMargin:=20.035d.頁腳到底端邊距3cm：ExcelID.ActiveSheet.PageSetup.HeaderMargin:

5、=30.035e.頂邊距2cm：ExcelID.ActiveSheet.PageSetup.TopMargin:=20.035f.底邊距2cm：ExcelID.ActiveSheet.PageSetup.BottomMargin:=20.035g.左邊距2cm：ExcelID.ActiveSheet.PageSetup.LeftMargin:=20.035h.右邊距2cm：ExcelID.ActiveSheet.PageSetup.Ri

6、ghtMargin:=20.035i.頁面水平居中：ExcelID.ActiveSheet.PageSetup.CenterHizontally:=20.035j.頁面垂直居中：ExcelID.ActiveSheet.PageSetup.CenterVertically:=20.035k.打印單元格網(wǎng)線：ExcelID.ActiveSheet.PageSetup.PrintGridLines:=True15)拷貝操作：a.拷貝整個(gè)工作表

7、：ExcelID.ActiveSheet.Used.Range.Copyb.拷貝指定區(qū)域：ExcelID.ActiveSheet.Range[A1:E2].Copyc.從A1位置開始粘貼：ExcelID.ActiveSheet.Range.[A1].PasteSpeciald.從文件尾部開始粘貼：ExcelID.ActiveSheet.Range.PasteSpecial16)插入一行或一列：a.ExcelID.ActiveSheet.

8、Rows[2].b.ExcelID.ActiveSheet.Columns[1].17)刪除一行或一列：a.ExcelID.ActiveSheet.Rows[2].b.ExcelID.ActiveSheet.Columns[1].18)打印預(yù)覽工作表：ExcelID.ActiveSheet.PrintPreview19)打印輸出工作表：ExcelID.ActiveSheet.PrintOut20)工作表保存：IfnotExcelID.A