首頁會計ExcelVBA

VBA如何自動儲存所有開啟中Excel檔案

之前有文章分享過編寫VBA自動儲存Excel檔案或另存備份,雖然程式的設計立意良好,也達到預期效果,但其實存在兩個缺陷:其一、會希望這個程式是真正「自動」執行的,只要電腦開啓任何一個Excel檔案,便會啓動此功能;其二、工作場合中,常常電腦是同時開啓很多檔案,會希望這些開啟中的一切檔案都可以自動儲存。針對這兩點,需要進一步完善VBA,在此介紹如何完善成終極版的自動儲存功能:

一、錄製巨集時,下面有個「將巨集儲存在」,選擇「個人巨集活頁簿」。

二、巨集錄好之後,「Alt+F11」開啟VBA編輯環境,在「專案-VBAProject」視窗中會多一個「PERSONAL.XLSB」,這是Excel隱藏的活頁簿,只要透過上個步驟錄製過「個人巨集活頁簿」,便開始啟動「PERSONAL.XLSB」,此後不管開啟任何一個Excel檔案,就會同時加載「個人巨集活頁簿」,也就是可以使用這個類似公共活頁簿裡的巨集程式。

三、在「Microsoft Excel」物件中有個「ThisWorkbook」,連按兩次後,在右邊的編寫程式碼上方,左邊選擇「Workbook」,右邊選擇「Open」。

四、編寫如圖所示的巨集程式碼:其中「Private Sub Workbook_Open()」代表只要開啟Excel便執行的VBA巨集,「Call Autosave」為呼叫執行在「Autosave」程序。

五、在現有模組(Module 1)或新模組(Module 11)輸入VBA程式,如圖所示有兩個巨集程序,一個是「自動儲存所有開啟中活頁簿」,作用是每五分鐘執行一次「Autosave」,而這個「Autosave」,作用是所有開啟中的活頁簿儲存一次,儲存後再執行「自動儲存所有開啟中活頁簿」。綜合這兩個巨集,結果為每隔五分鐘儲存所有開啟中的活頁簿。

六、除了儲存所有開啟中檔案,另一個重點是開啟任何檔案皆會自動儲存, 這裡會用到前面所述隱藏的Excel檔案。倘若有興趣,必須先把資料夾選項中,「隱藏的項目」打勾設定為開啟。

七、通常在這個路徑可找到「PERSONAL.XLSB」:「C:\Users\b8810\AppData\Roaming\Microsoft\Excel\XLSTART」其中「Users」為系統C磁碟的使用者資料夾、「b8810」為使用者名稱。一般不會直接用到這個檔案,都是像第一個步驟錄製好巨集,每次開啟任何Excel檔案,就會自動加載「PERSONAL.XLSB」所含巨集,在此只是讓各位讀者一探這個隱藏的檔案。

這裡分享如何將所有開啟中的Excel檔案設定為自動儲存,實際使用發現,每隔一段時間Excel會停頓下來,執行VBA程式自動儲存。相較之下,Excel在軟體選項設置提供的自動儲存功能,執行相對流暢,幾乎感覺不到痕跡。這個可能在所難免,因為畢竟是在軟體開發階段,微軟就把這個功能寫在軟體架構裡面了。

雖然內置的自動儲存功能效率較優越,但我遇過幾次慘痛經驗,這個功能失效,Excel當機停止運作,沒辦法找回我原來作業中的檔案,著實欲哭無淚!所以如果是預計要執行一項複雜、多個檔案同時操作、需要長時間進行的任務情況下,建議還是使用這個相對保險、自己設計的VBA巨集程式,因為靠自己最安全。當然,雙管齊下的話,那更是高枕無憂了!