Excel VBA建立Workbook_Open事件及If…Then判斷,開啟檔案自動爬蟲

目錄

Excel VBA網路爬蟲最終目的是建立自己的資料庫。本文介紹如何以Workbook_Open事件設定開啟檔案即執行,If…Then…Else邏輯判斷程式執行條件,最後用For Next迴圈事件彙總每天報表。

辦公室工作離不開電腦,電腦打開後勢必會用到Excel,而像匯率這樣的資料是每天會更新,可能每天都需要取得網頁上的匯率,到了月底還需要統計一整個月的匯率,如此說來,可能會希望一開啓某個活頁簿,Excel便會貼心提醒該取得當天匯率,然後可以將一段期間的資料合併,以下具體介紹設置方法:

一、Workbook_Open事件

想一開啓檔案便執行巨集,在VBA界面點選將滑鼠游標移到「ThisWorkbbok」,連按兩下,右邊會跳出編寫程式視窗,左上角拉下選單,點擊「Workbook」,右上角會自動變成「Open」選項,表示編寫開啓活頁簿時即執行的程式。

二、編寫VBA程式

30~50:這裡的變數宣告中,「Worksheet」為工作表物件,「Boolean」為邏輯值,「True」為真,「False」為假,另外「False」預設數值為「0」和Excel相同,但是「True」預設數值為「-1」,這個和Excel的「1」不盡相同。

70~110:以「Hour(Time)的函數方式將H變數設定為目前時間,因為台灣銀行是上班時間過後才會更新匯率,也就是早晨之前會抓取到前天的匯率,為避免系統日期和網頁匯率日期差一天的情況,抓個保守時間,如果是在早上九點之前開啓檔案,提示「今日尚未公佈匯率,請於九點後取得匯率資料。」,並且結束程式。

130~140:編寫「確認是否已取得匯率資料」的程式,這裡的「Else」是相對於第90列程式的「If H < 9 Then」,表示如果是在早上九點過後開啓檔案,便執行接下來的程式。

150~160:先利用「Date」函數取得目前日期,再分別利用「Year」、「Month」、「Day」將日期從「2017/10/22」改為「20171022」,再把「20171022」設定為變數「D」的值,和上一節的作法相同,這是配合工作表名稱及檔案不能含有「/」字元的權宜作法。

170~200:「For Each Sht In ThisWorkbook.Worksheets」這是VBA關於工作表很常用一個語法,意思是以活頁簿中的每個工作表作為變數值,依序檢查是否已經存在名稱為「D」的工作表,有的話,將邏輯變數「FX_Exist」值設定為「True」,否則維持預設的「False」。

210~220:根據「FX_Exist」的邏輯值設定文字變數Txt,作為「MsgBox」的提示訊息,這裡用到VBA的判斷函數「IIf」,其用法如同Excel裡「If」函數的翻版。

230~250:結束從第90列開始的「If…Then…Else」陳述式,最後並且結束此巨集。

三、錯誤時間執行程式測試

嘗試於三更半夜或大清早開啓檔案,果真跳出提示訊息框。

四、尚未取得匯率提醒

在當天尚未取得匯率資料的情況下,會自動跳出提醒。

五、彙總表格式

如圖所示,藉助提醒及上一節取得匯率的巨集,已經得到連續三天的匯率 (20171022~20171024)的資料,現在想將各個匯率工作表整合成明細表,先手動建立「Summay」工作表,適當整理過標題及格式。

六、合併工作表程式

編寫「Combined_Sheets」巨集,其中用到VBA語法的對象、屬性、方法,前面章節或多或少都有提過,因此毋庸贅述。

七、彙總匯率

成功彙總各個日期的匯率資料!

Excel VBA網路爬蟲三部曲

綜合上一節到這一節所述,本書介紹三個取得資料的步驟:首先是得到當天資料作為單獨一個工作表、接著設定某個Excel檔案作為資料庫、最後將一段期間的各個工作表彙總合併,兼顧每天即時需求及歷史存檔統計的功能。只要是像匯率這一類每天更新的網頁,這樣方法都可以類推適用,匯率是較為符合財務會計的用途,如果是貿易商,可能想取得大宗物料的每日價格,如果是出版社,可能想取得書籍銷售的每日排行,如果是個人,可能是投資分析取得公開財報,在如今網路上什麼都有的資訊時代,確實相當方便。

相關文章