Excel VBA設定檔名及資料夾路徑,定期自動另存新檔

Excel檔案備份是必須有的習慣,以防萬一。本文介紹VBA利用Application.OnTime和Call循環交互執行兩個模組程式、ThisWorkBook.SaveCopyAs另存檔案備份、以及InStrRev處理文字和MsgBox提示訊息等常用語句。

我們在Excel的世界裡,設計很複雜的函數公式,雖然能引用其他儲存格、其他工作表、甚至是其他工作簿的內容,但最終操作結果,都是在目前的儲存格中顯示特定內容,操作的對象總是儲存格。然而在VBA的世界裡,可以直接以工作表、工作簿、甚至是Excel檔案作為操作對象,對他們下指令,如此跨越了儲存格的界線,有很大的發揮空間。比如說,有很多的Excel操作:新建、打開、關閉、儲存、另存等等,這些動作可能在某個例行性工作中一再重複,在Excel世界裡,我們也只能一步一步來,每個月都必須來一次。可是擅用VBA,把這些步驟串成一段指令,每次一鍵讓Excel自動去執行,長期以往,大大節省了時間、提昇了效率,這是Excel自動化的另外一個層次。以下分享VBA如何以檔案作為操作對象,設定另存檔案備份:

一、輸入VBA程式

輸入如下VBA程式:

二、Excel執行VBA

Excel執行VBA程式的畫面如下:

三、定期另存新檔

觀察原Excel檔案所在的資料夾,會發現多了一個「VBA如何另存檔案備份-copy」,修改時間每分鐘更新一次,效果等同於定期另存新檔備份,以下詳細說明此VBA程式。

四、建立Sub程序

建立新的巨集:「Sub Time()」。

五、Application.OnTime

VBA的每次操作是一個事件,其中「OnTime」是時間到了即自動執行的程式,標準語法是「.」隔開,然後空格之後是對於該事件的具體描述(屬性),「Application.OnTime Now + TimeValue(“00:01:00”), “Autosave”」意思是從現在開始,一分鐘之後執行「Autosave」巨集,最後結束此段程式:「End Sub」。

六、ThisWorkbook.SaveCopyAs

編寫另一段程式:

Sub Autosave()

Range(“A1”) = ThisWorkbook.Path

在儲存格「A1」寫入目前工作簿所在的資料夾路徑。

n = ThisWorkbook.Name

定義「n」為目前工作簿的檔案名稱。

Range(“A2”) = n

在儲存格「A2」寫入「n」。

n = Left(n, InStrRev(n, “.”) – 1)

更新定義「n」。這裡使用了兩個函數,「InStrRev」函數是從後面開始尋找,傳回某字串在另一個字串中首次出現的位置,Left函數是傳回文字字串中的前幾個字元 。以這篇文章的Excel檔案為例,原來「n」的內容是「ThisWorkbook.Name」,也就是「VBA如何另存檔案備份.xlsm」,然後又更新了「n = Left(n, InStrRev(n, “.”) – 1)「InStrRev(n, “.”)」,等於是去掉副檔名,結果便是「VBA如何另存檔案備份」。

Range(“A3”) = n

在儲存格「A3」寫入「n」。

ThisWorkbook.SaveCopyAs Filename:=ThisWorkbook.Path & “\” & n & “-” & “copy” & “.xlsm”

此為VBA標準語法,對象和程序中間以「.」隔開,「ThisWorkbook.SaveCopyAs」意思是以目前工作簿為對象,執行另存檔案,空一格後面是具體描述(Excel的正式說法叫屬性),這裡設定檔名為:「Filename:=ThisWorkbook.Path & “\” & n & “-” & “copy” & “.xlsm”」,這句程式對於熟悉Excel公式的人,應該不難理解。

MsgBox “已自動儲存”

提示訊息視窗:「已自動儲存」。

Call Time

召喚執行「Time」巨集。

End Sub

結束「Autosave」巨集。

逐漸熟悉VBA零組件

這篇文章的VBA不會太複雜,但是涉及到的程式概念相當完整。有一個具體的執行程序(事件屬性的書寫語法)、有根據需求所定義的變數(VBA習慣是一再更新定義)、有設計公式計算的函數(Excel原有函數及VBA特有函數)、最後將結果寫入Excel儲存格中,並且配合提示視窗和關聯巨集的操作。如果這些「關鍵零組件」都能夠熟悉,觸類旁通,將會大大強化VBA的實作能力。

每天學習,每天充電:VBA自動化文章合輯

歡迎加入Line社群,口袋裡的VBA小教室!

最新文章: