VBA学习总结（2）

一些有用的代码

・创建Excel Workbook和Worksheet

例：

Dim xlApp As Excel.Application

Dim xlBook As Excel.Workbook

Dim xlSheet As Excel.Worksheet

xlApp = CType(CreateObject(“Excel.Application”),Excel.Application)

xlBook = CType(xlApp.Workbook.Add,Excel.Workbook)

xlSheet = CType(xlBook.Worksheet(1),Excel.Worksheet)

xlSheet.Cells(1,1) = “Title”

xlSheet.Application.Visible = True

 

・打开工作簿

例：Workbooks.Open(“test.xls”)

 

・保持工作表

例：ActiveWorkbook.SaveAsFilename:=”C:\test.xlsm”

 

・重命名

例：

Sheets(1).Select

Sheets(1).Name = “new name”

 

・隐藏工作表

例：

Sheet(1).Select

ActiveWindow.SelectedSheets.Visible = False

 

・遍历所有Sheet

例1：

For Each sht In Worksheets

       sht.Activate

       ‘TODO

Next sht

 

例2：

For i=1 TO Worksheets.Count

       ‘TODOwith Worksheets(i)

Next i

 

・获取”A1”所在区域的总行列数

例：

TotalRows = Worksheets(“Sheet1”).Range(“A1”).CurrentRegion.Rows.Count

TotalCols = Worksheets(“Sheet1”).Range(“A1”).CurrentRegion.Columns.Count

 

・使用MsgBox

例：

Dim r As Integer

r = MsgBox(“text”, vbYesNo, “Title”)

If  r= vbYes Then

…

Else

…

EndIf

 

・访问及遍历文件

例：

Set fs = CreateObject(“Scripting.FileSystemObjec”)

Set folder = fs.getFolder(“C:\temp”)

For Each tmpFile In folder.Files

       ‘TODOwith tmpFile

Next tmpFile