2013年11月15日 星期五

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

張貼留言

LinkWithin

Related Posts Plugin for WordPress, Blogger...

熱門文章