VBA存檔後關閉:設定1個另存新檔路徑自動執行

VBA存檔後關閉是在開發方案常用到的技巧,本文以爬蟲取得財務報表為範例,介紹如何設定1個自動執行的另存新檔路徑,讓你的資料處理儲存更加靈活。

本章第一節到第三節,分享如何將每日匯率儲存於分別的工作表,累積一段時間再彙總分析,如此雖然方便,然而所有工作表資料放在同一個活頁簿,數量多的話,不是很好處理。例如一年有365天、扣除假日估算250個工作天,也就是在同一活頁簿會有250個工作表,光是想像就不太容易。這裡以第五章財務報表為例,介紹另一種可行方法,把每次取得的資料儲存為一個Excel檔案,有需要時再調出來整理分析,以下具體分享:

一、Excel參數設定報表

首先,建立一個Excel報表,於格式設計包含所有關鍵參數。

E06e01

二、集合迴圈取得財報

編寫如下VBA程式碼:

E06e02

10~30:「Option Explicit」意思是強制必須宣告變數,在程式碼較多的情況,建議在一開始、程序的上面寫上這麼一段話。接著的「Public」為宣告公共變數,如果有定義那些公共變數的話,其值可以跨模組延用。

70~140:和先前章節類似的變數定義方式,這裡用到「CInt」函數將值轉換為整數,其用法和「CStr」文字函數類似。這裡的「Application.InputBox」方法在以前章節常用,意思是以輸入視窗的方式填入儲存格參照,並且將同一行的其他欄位資料合併,作為想取得資料網頁的止。

160~200:先前章節習慣使用「For…Next」,其間皆是以等差級數作為循環,但如果遇到像這裡想以一組文字依序循環,必須用數列設定集合變數,宣告一個「Variant」變數代表集合中的個數。以這裡為例,便是將「FR(2)」、「FR(3)」、「FR(4)」設定為文字變數「report」,其值依序為「Balance Sheet」、「Income Statement」、「Cash Flows」。

200~350:如同上一章第五節所言,「For Each In…Next」是一種特別的「For…Next」循環語句,它循環的對象為集合或數列裡的每一個元素或項目。搭配「T1」、「T2」的計算式,循環依序建立「Balance Sheet」、「Income Statement」、「Cash Flows」三個工作表,並且將同一網頁中第2個、第3個、第4個表格資料下載到各個工作表。

三、變數未定義編譯錯誤

先測試看看,故意將程式碼中的「WebAddress」改成「WebAdress」,因為拼字錯誤並沒有宣告變數,執行程式果然跳出提示視窗:「編譯錯誤:變數未定義」。

E06e03

四、Application.InputBox

正式執行巨集程式,首先會跑「Application.InputBox」,出現輸入儲存格參照的視窗,在這裡選擇「A2」。

E06e04

五、同時下載三大財報

如願新增了工作表,同時還將三大財務報表都下載好了!

E06e05

六、財務報表另存新檔

同場加映,設計VBA程式碼將財務報表另存新檔。

E06e06

七、財報資料庫新增檔案

果然冒出來:「1-2002-中鋼-2016-4」,這是財報資料庫的第一個檔案。

E06e07

八、目前所在資料夾

最後補充,因為每位讀者所偏好的資料夾不同,可以在自己電腦滑鼠右鍵,點選「內容」,出現如圖所示的視窗,其中的「位置」便是目前所在的資料夾路徑:「C:\Users\b8810\Documents」,再加上本身的資料夾名稱,即為這一節VBA程式碼的「C:\Users\b8810\Documents\FRs\」。

E06e08

Excel表單輸人與VBA程式輔助

此處介紹的方法是先在Excel建立一個表單,執行程式時讓操作者選擇表單中任何一項,不過其實也可以預設好想下載哪些財報,編寫於VBA程式中,直接將網頁資料儲存成電腦檔案。這裡之所以透過表單中介,一方面是留有一份資料庫清單,另一方面保有彈性,用Excel輸入資料還是比較快,毋須捨本逐末,什麼都要寫成VBA程式碼。


贊贊小屋服務:3,000元網頁設計,5,000元Google Kinsta主機,8,000元Excel ERP系統

歡迎前往贊贊小屋VBA教學中心

取得範例程式請前往VBA社團,訂閱請加入VBA Line社群

VBA課程推薦:零基礎入門進階的20小時完整內容

VBA課程:20小時完整入門進階,職場及投資應用

最新文章: