Excel VBA工作簿操作

1、打開檔案與建立檔案

'打開檔案
Application.ScreenUpdating = False  '關閉螢幕更新
Application.DisplayAlerts = False '關閉警告
Workbooks.Open Filename:="D:\XSL\檔案夾\1.xls"
ActiveWorkbook.Sheets(1).Range("a2") = "又到此一遊"
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.DisplayAlerts = True '打開警告
Application.ScreenUpdating = True '打開螢幕更新

'建立檔案
Application.ScreenUpdating = False  '關閉螢幕更新
Application.DisplayAlerts = False '關閉警告
Workbooks.Add
ActiveWorkbook.Sheets(1).Range("a1") = "哈哈，這是我自動建立出來的"
ActiveWorkbook.SaveAs Filename:="D:\XSL\檔案夾\1.xlsx"
ActiveWorkbook.Close
Application.DisplayAlerts = True '打開警告
Application.ScreenUpdating = True '打開螢幕更新
           

2、一檔案（多表）拆為多檔案

'一檔案（多表）拆為多檔案
Dim sht As Worksheet
Application.ScreenUpdating = False  '關閉螢幕更新
For Each sht In Sheets
    sht.Copy
    ActiveWorkbook.SaveAs Filename:="D:\XSL\" & sht.Name & ".xls"
    ActiveWorkbook.Close
Next
Application.ScreenUpdating = True '打開螢幕更新