VBA爬蟲取得網頁超連結,工作表重複自動偵錯

VBA網路爬蟲專案可能遇到三個問題:如何保留網頁原始格式、進而取得網頁超連結 、每天執行程式如何避免重複,本文以實際案例介紹怎麼解決這些問題。

本書前兩章已經介紹如何利用VBA取得網路資料,範例為單一網頁,通常在提到這方面的應用,會比較希望大批量取得網路資料,自動化針對多網頁執行程式,可稱之為網路爬蟲。VBA雖然方便,畢竟是Excel附加應用,在執行網路爬蟲有些先天限制,本章沿續先前兩章範例,介紹VBA如何設計網路爬蟲程式,可能會遇到什麼困難,可以怎麼樣因應克服。

前兩章所取得的都是純粹的網頁資料,其實在實際瀏流覽網頁很多文字是超連接,可以連結開啟另外一個相關的網頁,通常會有更詳細的資訊。以書籍排行榜為例,在排行榜上面有雖然有了書名、作者、價格等資訊,但如果通過連結進入某一本書的專門頁面,還會有那本書的系統分類和出版社等補充資訊。所以取得資料的過程,只有排行榜是不够的,還希望能取得各個書籍的網頁資料,這樣子整個待分析資料會比較完整。這一章介紹如何以排行榜網頁資料為基礎,進一步取得單一書籍的網頁資料,並且自動執行資料整理。

一、WebFormatting

第一章第二節有所介紹的取得網頁資料程式,有個屬性WebFormatting一直都設定為xlWebFormattingNone,作用是不取得網頁格式,這裡在前面加一個英文單引號,讓它變成單純文字,如此VBA在執行QueryTables.Add的時候,這個屬性會回復到預設值,也就是會得網頁格式。

VBA爬蟲取得網頁超連結,工作表重複自動偵錯 1

二、保留網頁格式

Excel執行程式所取得的資料格式和先前不太一樣,游標停留在書名上面,會出現那本書的網址,下面一行字「按一下以追蹤」。

VBA爬蟲取得網頁超連結,工作表重複自動偵錯 3

三、原始網頁連結

上個步驟超連結點一下,便會跳瀏覽器那本書的網頁,如同本節一開始所述,讀者實際瀏覽網頁,可以看到它在的分類還有出版社等較為完整的資訊。

VBA爬蟲取得網頁超連結,工作表重複自動偵錯 5

四、VBA爬蟲網址

VBA編輯區域複製第一個步驟的程式碼,將其中的網址更改為第三個步驟的書籍網址。

VBA爬蟲取得網頁超連結,工作表重複自動偵錯 7

五、執行階段錯誤

執行程式之後會跳出「執行階段錯誤」的訊息,提示「該名稱已存在,請嘗試使用其他名稱」,VBA會將「ActiveSheet.Name = Today2」這一行標記黃色,表示執行到這裡時異常。

在操作Excel時,讀者也許有經驗Excel同一本活頁簿是不能新增重複名稱的工作表,本節在第二個步驟有新增了以當天日期為名稱的工作表,這裡如果再執行的話,工作表名稱會重複,因此VBA在跑程式操作Excel時會跳異常。

VBA爬蟲取得網頁超連結,工作表重複自動偵錯 9

六、錯誤檢查程式

建立一個檢查工作表名稱是否重複的程式(VBA Function自定義函數、Dim…As Boolean邏輯值變數、For Each…Next集合迴圈、Exit Sub結束程序)

為了避免上個步驟所遇到的錯誤,設計新增工作表的VBA程式時,通常都會配套設計一個檢查機制。這裡利用VBA新建函數的方式檢查是否存在名稱相同的工作表。

Function 有工作表嗎(ShtNam):建立一個「有工作表嗎」的VBA函數,有一個引數為「ShtNam」。

Excel的函數很方便,其實函數就是微軟開發好的一段小程式,有輸入引數、有計算規則、最後有計算結果。VBA可以自行開發函數,先前的程式都是Sub開頭,表示一個程序,VBA建立函數是以Function開頭,括號裡面是類似Excel函數的引數。
‘定義變數

Dim AnySht As Worksheet, ShtExt As Boolean:電腦最原始值為真或假,Boolean 源自於Boolean algebra(布林運算式),是十九世紀英國數學家所發現的一套邏輯代數系統,後來延伸引用在電腦科學中,最常用是代表真(1)與假(0)的兩種情況,電腦程式語言中,一般Boolean變數便是代表這兩邏輯值的資料型態。

ShtExt = False:先預設ShtExt的布林值為假。

For Each AnySht In Worksheets:For Each…Next可以理解一種特殊的For…Next迴圈事件,它主要是以集合中每個元素作為迴圈順序。這裡意思以目前活頁簿中的所有工作表作為集合,從第一個工作表到最後一個工作表依序迴圈執行程式。

整個VBA「有工作表嗎」函數作用是以「ShtNam」作為條件,檢查活頁簿有是否有該名稱工作表,有的話函數值為真,沒有為假。

所以在接下來的「Sub 取得網路資料2()」中,「If 有工作表嗎(Today2) Then」便是藉用VBA定義好的「有工作表嗎」這個函數,以變數「Today2」的值作為函數引數,有的話條件成立,以MsgBox指令提示「程式想新增的工作表已存在,請確認。」,並且直接「Exit Sub」提前結束程式。

VBA爬蟲取得網頁超連結,工作表重複自動偵錯 11

七、自動偵錯處理

設計好偵錯程式之後,如果當天已經有過程式,再執行時便會跳出提醒訊息。

VBA爬蟲取得網頁超連結,工作表重複自動偵錯 13

VBA函數與程式偵錯機制

這一節先介紹如何修改程式取得原始網頁格式,接著是如何設計VBA函數檢查工作表是否存在。先前章節的程式其實也需要像這樣的檢查機制,因為如果程式是寫給自己用的,就算沒有檢查機制也不會造成困擾,但如果是提供給別人使用,別人在不知情的情況下,遇到VBA執行錯誤可能會無法理解和解決 ,所以儘量在程式設計加上偵錯機制可以加強使用者體驗。

這個檢查也可以直接寫在程式裡,這裡特別設計以函數執行,一方面是藉範例介紹VBA函數功能,另一方面也是由於工作表檢查機制很多地方都用得到,可以說只要有新增工作表的場合就需要,因此設計成VBA函數較有擴充性。