VBA取消刪除工作表的提醒,爬蟲取得網頁超連結

VBA自動爬蟲專案會有工作表名稱或內容重複的問題,本文先介紹取消刪除工作表提醒機制、取得原始網頁超連結,進一步說明不同處理方法會遇到的狀況。

上一節為了避免同一天執行兩次的問題,設計確認是否已經有工作表存在的程式,編寫為函數,機制是在遇到有相同工作表時,跳出提醒視窗並結束程式。這樣雖然增加偵錯機制,避免重覆,但實務上也有可能第二次再執行是想更新資料 ,這一節先介紹修改程式檢查機制,接著加入取得網頁超連結的功能,為建立更完整的分析資料庫作準備。

一、Application.DisplayAlerts

重新整理並且合併上一節的程式,如同本節引言所述,修改檢查機制,如果發現已經存在工作表,表示有執行過,直接將這個工作表刪除。這裡在刪除的前後都執行了Application.DisplayAlerts屬性變更的程式,它作用為控制Excel是否提醒訊息。

正常在刪除帶有資料的工作表時,Excel會善意彈出視窗再次確認,雖然善意,但在明知不需要的情況顯得多餘,而且會影響自動化程式的執行,所以在設計自動化流程時通常會有像下面這樣的代碼。

Application.DisplayAlerts = False:關閉Excel提示訊息。
Sheets(Today2).Delete:刪除工作表(不會跳出確認視窗)。
Application.DisplayAlerts = True:開啟Excel提示訊息。

VBA取消刪除工作表的提醒,爬蟲取得網頁超連結 1

二、儲存格超連結

既然工作表名稱是由程式所設定,視情況可作修改,上個步驟有在日期後面加了「-附網址」,測試執行果然如此,可以和先前章節單純取得不帶格式的工作表作個區隔。

VBA取消刪除工作表的提醒,爬蟲取得網頁超連結 3

三、多餘資料刪除

這裡延用本書第二章第二節相同的程式設計概念,標記想保留的資料行,由於重點是書籍所附帶的網址,連結兩行都是內附網址的書名,因此取「i+1」,其餘「i」、「i+4」、「i+5」都在前面加上英文單引號,作用和Excel函數公式一樣,加單引號會變成是單純文字,不會執行程式。

VBA取消刪除工作表的提醒,爬蟲取得網頁超連結 5

四、清理過工作表

執行程式後,得到乾淨資料的工作表。

VBA取消刪除工作表的提醒,爬蟲取得網頁超連結 7

五、Hyperlinks.Address

Excel儲存格裡的連結網址可以複製出來,相對應的也可以設計VBA程式大量執行,如下的程式碼是在提取出第一欄儲存格裡的連結網址內容,設定為第二欄儲存格的值。

For i = 1 To UsedR:依資料範圍的迴圈事件
Cells(i, 2) = Cells(i, 1).Hyperlinks(1).Address:複製儲存格裡的網址連結
Next:依順序迴圈執行

這裡沿用先前章節的方法,以Call指令合併程式,同時也在最後調整報表格式,相關指令其實就是簡單的英文句子,只要掌握VBA基本的對象屬性語法,應該不難理解。

VBA取消刪除工作表的提醒,爬蟲取得網頁超連結 9

六、取得網頁網址

執行程式的結果如圖所示,成功取得排行榜書籍的網址清單。

VBA取消刪除工作表的提醒,爬蟲取得網頁超連結 11

七、Worksheet.Delete

上個步驟可以看到活頁簿裡只有一張工作表,此時如果再執行程式,由於Excel活頁簿裡至少要有一張工作表,設計的程式會將同名工作表刪除,因此執行程式會嘗試將活頁簿裡唯一的工作表刪除,因此會提示「Class Worksheet的Delete方法失敗」,意思是工作表集合裡的唯一元素不可刪除。

VBA取消刪除工作表的提醒,爬蟲取得網頁超連結 13

工作表重複的VBA處理

上一節是將同名工作表的偵查機制設定為結束執行,這一節修改為刪掉後再重新建立,雖然可以達到資料重新更新的效果,但如同這一節最後步驟所示,在活頁簿僅有單一工作表時會導致執行失敗。針對這種情況,可以將兩個情境結合起來,再加一個條件,如果遇到單一工作表時結束程式,在此不多作示範,讀者有興趣可自行嘗試,這裡特別補充說明,只是讓讀者體會實際在設計程式時,都會遇到類似的情況。

VBA取消刪除工作表的提醒,爬蟲取得網頁超連結 17
加入Line社群,口袋裡的Excel小教室!