3、工作簿Workbook的操作

大六九

workbooks由当前所有在内存中打开的workbook对象组成
向workbooks添加workbook对象

1.新建工作簿 Add
Sub 新建工作簿()
Dim wkb As Workbook '声明wkb为工作簿
Set wkb = Workbooks.Add '新建工作秒簿
wkb.SaveAs "c:\123.xls" '保存为工作簿
End Sub

2.打开工作簿 Open
Sub 打开工作簿()
Dim wkb As Workbook
Set wkb = Workbooks.Open("c:\123.xls")
End Sub

3.关闭工作簿
Sub 关闭()
Workbooks("123").Close True
End Sub

3.文件复制与删除
Sub 文件复制与删除()
FileCopy "c:\123.txt", "c:\321.txt"  '对所有文件类型都起作用
Kill "c:\321.txt"
End Sub

实例应用1
Sub 指定文件合并工作簿()
Dim xlBook As Workbook
Dim xlSheet As Worksheet
Application.ScreenUpdating = False
   file = "c:\测试"            '定位处理文件夹
   F = Dir(file & "\*.xlsx")
For i = 1 To 5
    Set xlBook = Workbooks.Open(file & "\" & F)   
    '打开已经存在的工件簿文件
    For Each sh In xlBook.Worksheets   
    '遍历打开工作簿的工作表
      With sh
          arr = .[a1].CurrentRegion
      End With
      Windows(ThisWorkbook.Name).Activate
     '回到打开的工作簿
          Sheets.Add after:=Sheets(Sheets.Count)
          With ActiveSheet
          .Name = Left(xlBook.Name, 1) & sh.Name
          .[a1].Resize(UBound(arr), 3) = arr
           End With
           Erase arr
    Next
           Windows(F).Close True
          '关闭打开的工作簿，并保存。
           F = Dir
Next i
Application.ScreenUpdating = True
End Sub
实例应用2
Sub 拆分到工作簿()
Dim a$, b%, i%
Application.ScreenUpdating = False
b = Sheets.Count
For i = 1 To b
If Sheets(i).Visible = 0 Then GoTo line
Sheets(i).Copy
a = "11月份-" & ThisWorkbook.Worksheets(i).Name
With ActiveWorkbook
.SaveAs Filename:=ThisWorkbook.Path & "\" & a & ".xlsx"
.Close
End With
line:
Next i
Application.ScreenUpdating = True
End Sub