Excel VBA 活頁簿(Workbooks)管理－電腦學習園地

語法筆記

' ------------------------------------------------------------
' 查看目前開啟excel檔案數量
Dim OpenCnt as Integer
OpenCnt = Application.Workbooks.Count

' ------------------------------------------------------------
' 依序查已開檔名 - 方法一

    Dim i As Integer
    For i = 1 To Workbooks.Count
        MsgBox i & " " & Workbooks(i).Name
    Next

' ------------------------------------------------------------
' 依序查已開檔名 - 方法二

Dim my Sheet As WorkSheet
For Each mySheet In Worksheets
MsgBox mySheet.Name
Next mySheet

' ------------------------------------------------------------
' 開啟特定檔案 - 方法一
filename = "C:\VBA\test.xls"
Workbooks.Open filename

' ------------------------------------------------------------
' 開啟特定檔案 - 方法二

Dim filename As String
filename = "C:\ExcelVBA\test.xls"
    Dim sn As Object
    Set sn = Excel.Application
    sn.Workbooks.Open filename
    ' sn.Workbooks(filename).Close ' 關閉
    Set sn = Nothing

' ------------------------------------------------------------
' 關閉指定檔案, 不提示訊息
    Dim filename As String
    filename = "Test.xls" ' 這裡只可以給短名,給全名會錯
' 假設 Test.xls 已於開啟狀態
    Application.DisplayAlerts = False ' 關閉警告訊息
    Workbooks(filename).Close
    Application.DisplayAlerts = True ' 再打開警告訊息

' ------------------------------------------------------------
' 關閉所有開啟檔案, 但留下主視窗
Workbooks.Close

' ------------------------------------------------------------
' 關閉 excel 程式

Application.Quit

' ------------------------------------------------------------
' 直接進行存檔

Dim filename As String
filename = "a.xls" ' 只可為短檔名
WorkBooks(filename).Save

' ------------------------------------------------------------
' 指定檔名進行另存新檔，並關閉
' 假設要將 "a.xls" 存成 "C:\b.xls"

Application.DisplayAlerts = False ' 關閉警告訊息
Workbooks("a.xls").SaveAs "C:\b.xls" ' 另存新檔
Workbooks("b.xls").Close ' 關閉 b.xls
Application.DisplayAlerts = True ' 開啟警告訊息

' ------------------------------------------------------------
' 指定當前活頁簿

Dim Caption as String
Caption = "a.xls"
Workbooks(Caption).Activate ' 將視窗切到 a.xls