操作工作薄——Excel之VBA（4）

 

For each 语句

For each x in range("a1:a10")　　注：类似For i in n

示例：

1)　　 Sub test() Dim i as integer For i = 1 To 10 　　range("a" & i) = 1 Next End Sub

2) Sub test2() Dim x as range For each x in range("a1:a10")　　注：类似For i in n x = 1 Next End Sub

 

示例2：

 

1)　　 Sub test() Dim i as integer For i = 1 To 10 　　range("a" & i) = i Next End Sub

2) Sub test2() Dim x as range Dim i as integer For each x in range("a1:a10")　　注：类似For i in n i = i+1 x = i Next End Sub

 补充：不等于号　<>

 

Workbook 语句

用来操作工作簿

ActiveWorkbook　　注：当前激活的工作簿

示例1）：

访问表格	创建表格
Sub wenjian() Application.ScreenUpdating = False　　注：关闭屏幕刷新 Application.DisplayAlerts = False　　注：关闭错误提示 Workbooks.Open Filename:="d:\data\1.xslx" ActiveWorkbook.Sheets(1).Range("a1") = "又又到此一游" ActiveWorkbook.Save　　注：保存文件 ActiveWorkbook.Close　　注：关闭文件 Application.DisplayAlerts = True　　注：开启屏幕刷新 Application.ScreenUpdating = True　　注：开启错误提示 End Sub	Sub chuangjian()   Workbooks.Add ActiveWorkbook.Sheets(1).Range("a1") = "哈哈这是我自动创建出来的" ActiveWorkbook.SaveAs Filename = "d:\data\222.xlsx" ActiveWorkbook.Close End Sub

 

示例2）：

表格拆分为多个文件

Sub chaifen()

Dim sht As Worksheet

For Each sht In Sheets
　　sht.Copy
　　ActiveWorkbook.SaveAs Filename := "d:\data\" & sht.Name & ".xlsx"
　　ActiveWorkbook.Close
Next

End Sub

续：