VB中调用EXCEL

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:\\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;

参考代码：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/8d10115265.html, := \'隶书\';

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

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

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

14) 进行页面设置：

I.VB创建Excel表:

1.'定义Excel操作变量

2.Dim xlApp As New Excel.Application

3.Dim xlBook As Excel.Workbook

4.Dim xlSheet As Excel.Worksheet

5.

6.'创建Excel进程，并打开目标Excel文件

7.Set xlApp = CreateObject("Excel.Application")

8.xlApp.Visible = True

9.xlApp.DisplayAlerts = False

10.Set xlBook = xlApp.Workbooks.Add

11.Set xlSheet = xlBook.Worksheets(1)

12.xlBook.SaveAs "路径/文件名"

II.VB打开Excel表:

[vb]view plaincopyprint?

1.'定义Excel操作变量

2.Dim xlApp As New Excel.Application

3.Dim xlBook As Excel.Workbook

4.Dim xlSheet As Excel.Worksheet

5.

6.'打开目标Excel文件

7.Set xlApp = New Excel.Application

8.xlApp.Visible = True

9.xlApp.DisplayAlerts = False

10.Set xlBook = xlApp.Workbooks.Open("路径/文件名")

11.Set xlSheet = xlBook.Sheets(1)

12.xlBook.Save

III.VB关闭Excel表:

1.'定义Excel操作变量

2.Dim xlApp As New Excel.Application

3.Dim xlBook As Excel.Workbook

4.Dim xlSheet As Excel.Worksheet

5.

6.'已经打开目标Excel文件

7.Set xlApp = New Excel.Application

8.xlApp.Visible = True

9.xlApp.DisplayAlerts = False

10.Set xlBook = xlApp.Workbooks.Open("路径/文件名")

11.Set xlSheet = xlBook.Sheets(1)

12.xlBook.Save

13.

14.'关闭目标Excel文件

15.xlApp.Quit

16.Set xlBook = Nothing

17.Set xlSheet = Nothing

18.Set xlApp = Nothing

IV.VB写入Excel表:

[vb]view plaincopyprint?

1.'定义Excel操作变量

2.Dim xlApp As New Excel.Application

3.Dim xlBook As Excel.Workbook

4.Dim xlSheet As Excel.Worksheet

5.

6.'打开目标Excel文件并且写入

7.Set xlApp = New Excel.Application

8.xlApp.Visible = True

9.xlApp.DisplayAlerts = False

10.Set xlBook = xlApp.Workbooks.Open(“路径/文件名")

11.Set xlSheet = xlBook.Sheets(1)

12.With xlSheet

13. .Cells(1, 1) = "A"

14. .Cells(1, 2) = "B"

15. .Cells(1, 3) = "C"

16.End With

17.xlBook.Save

V.VB读取Excel表:

[vb]view plaincopyprint?

1.'定义Excel操作变量

2.Dim xlApp As New Excel.Application

3.Dim xlBook As Excel.Workbook

4.Dim xlSheet As Excel.Worksheet

5.'定义用于保存Excel单元格内容的字符串

6.Dim xlString As String

7.'定义整形变量用于保存Excel表的行数与列数

8.Dim xlNum, xlCol As Integer

9.

10.'打开目标Excel文件并且读取保存Excel内容

11.Set xlApp = New Excel.Application

12.xlApp.Visible = True

13.xlApp.DisplayAlerts = False

14.Set xlBook = xlApp.Workbooks.Open("路径/文件名")

15.Set xlSheet = xlBook.Sheets(1)

16.'读取Excel表中记录行数

17.xlNum = https://www.360docs.net/doc/8d10115265.html,edRange.Rows.Count

18.'读取Excel表中记录列数

19.xlCol = https://www.360docs.net/doc/8d10115265.html,edRange.Columns.Count

20.'读取Excel表中i行j列单元格内容

21.xlString = Trim$(xlSheet.Cells(i,j))

22.xlBook.Save