Excel VBA自動新增日期工作表,爬蟲取得分析資料

VBA網路爬蟲可能是每天取得最新資料,本文介紹自動新增Excel工作表,利用Date及Format函數重新命名為當天日期,跨檔案複製程式碼,合併為同一個Sub程序執行。

這一章在先前章節介紹了網頁資料和新建工作表的程式,也介紹如何將程式匯出匯入,在這裡要將這些東西整合,設計一個自動取得網頁資料,並且將資放到新增的工作表上,除此之外,因為資料大多數是持續取得的,所以要以日期作為新工作表的名稱。

一、VBA Date函數

Dim Today As Date

定義一個Today變數,它的類型是Date,表示是日期。

Today = Date

這裡的「Date」是一個函數,和Excel一樣VBA也有快速執行功能的函數,少部份函數是Excel和VBA共通,大部份是VBA特有的函數。這裡的Date便是VBA裡傳回系統日期函數,作用等於Excel的Today函數。注意到這一行的Date和上一行Date雖然是同一個單字,但一個是變數類型,一個是VBA函數。就像Today在此是VBA的變數,在Excel裡可能一個函數,只是剛好名稱相同,必須看上下文確認它的功用,這在寫程式裡很重要。

MsgBox “今天是” & Today

MsgBox函數第本章第三節有介紹過,這裡仍然是使用文字+變數的結構。

截圖是完整的程式和執行結果,簡單三行程式碼,可以做出Excel做不到的效果。

二、程式複製貼上

上一節介紹匯出匯入的方式取得寫好的程式,其實有一個更快方法,把兩個檔案都開啟,直接在VBA專案視窗就可以複製貼上操作,把每個模組當作是一個Word文書即可,差別只在於內容是程式碼而已。

例如這裡先選取「1.3 新增工作表」的「Module1」裡「Sub 新增工作表()」全部的程式碼,反白之後滑鼠右鍵,快捷選擇「複製」,操作流程和Excel或是Word是完全一樣。

三、VBA Format函數

「新增工作表」是根據上個步驟所複製的「新增工作表」修改,程序名稱相同,但卻是存在不同檔案的程式。在此針對較為特別的程式說明如下:

Today2 = Format(Today1, “yyyymmdd”)

Format也是VBA函數之一,功用類似於Excel的TEXT函數,可以轉換數值類型。在第一個步驟可以知道Date函數會得到「2020/7/30」,但有在Excel新增工作表的讀者,應該瞭解工作表名稱不可以是像「/」這樣的特殊符號,因此在此把它轉換成20200730(”yyyymmdd”)。

Worksheets.Add after:=Sheets(Sheets.Count)

原始程式「新增工作表」中是以ShCount變數指稱工作表數目,這裡為簡化程式起見,直接將其內建到Sheets集合對象的參數,表示指定所有Sheets集合中的某一個工作表,亦即最後一張工作表。

ActiveSheet.Name = Today2

除了主詞+動詞,英語裡還一個基本句型是主詞+Be動詞+形容詞,用來說明主詞的性質。程式語言則是對象.屬性,用來設定對象的屬性值,這一行程式碼意思是將目前工作表(ActiveSheet)的名稱()設定為變數值(Today2)。因為上一行程式碼是新增工作表,Excel會把剛新增的工作表指定為目前工作表,配合這裡的ActiveSheet便是將新增工作表重新命名。

執行「新增工作表」巨集之後,果然在Excel活頁簿多了一個「20200730」工作表。

四、模組名稱修改

用上一節介紹方法,將這一章「取得網頁資料」的程式模組匯入,由於原模組名稱為「Module1」,和目前活頁簿專案的「Module1」名稱衝突,VBA自動將名稱改為「Module11」。

VBA編輯介面中左上區域是專案視窗,左下區域是所選取專案對象的屬性視窗,例如「Module11」的「Name」屬性欄位,選取「Module11」,可以在這裡直接更改。

五、合併Sub程式

沿續上個步驟,先複製整個「新增工作表」程式碼,將「Sub 新增工作表」改為「Sub 新增工作表並取得網路資料」,然後將「Module11」名稱更改為「Module2」,把「取得網頁資料」中With…End With的程式碼複製貼到「Sub 新增工作表並取得網路資料」的下面。只要瞭解VBA程序Sub…End Sub和一行一行依序執行程式的性質,就能清楚地進行像這樣的操作。

六、執行階段錯誤

執行「新增工作表並取得網路資料」巨集會提示錯誤,這個錯誤很容易理解,解決方法也很簡單,就是先把現有的「20200730」工作表刪除,接著就可以順利執行程式了。

七、VBA爬蟲程式

刪除名稱重複的工作表後再次執行巨集,果然成功新增一個以當天日期為名稱的工作表,而且這個工作表上已經下載好了所需要的網頁資料。

VBA程式精簡及優化

關於這一章最後這一節有三點補充:

1.程式碼精簡:

第三步驟有利用Sheets(Sheets.Count)簡化程式,這個隨著程式碼越來越多是蠻重要的不過對於程式初學者或者以實務用途為主而言,不用急著要精簡程式,只要依照思惟邏輯設計,養成像是設定變數的良好習慣即可。隨著程式經驗的累積,自然會知道怎樣寫程式比較好,這個跟寫文章是一樣道理。

2.Excel操作:

VBA本質上仍然是操作Excel,只是比較特別以程式碼方式執行,所以這一節遇到的名稱不可有特殊符號、名稱不可重複,這個是Excel本身的限制,VBA程式碼並不會突破Excel範圍進行操作。

3.程式優化:

這一節程式範例如果工作表名稱相同會跳錯,其實隨著程式設計功力的強化,可以進一優化,例如預先偵錯是否相同名稱的工作表,有的話,跳出對話方塊,提醒操作者並且提供是否刪除或覆蓋等選項,這個程式碼精簡一樣,不必操之過急,本書後面章節有適當範例,會說明如何優化程式執行體驗。

本文內容取自《人人都學得會的網路大數據分析入門》,書本仍然是最好的學習方法,省下一張電影票的錢,今天就買本書吧!

相關文章