Excel VBA爬蟲程式專案:Call呼叫整合模組及儲存檔案

Excel VBA程式專案到了設計完成階段,是將所有程式和資料整合在一起,可以利用Call呼叫方式,建立子執行模組及輔助副模組,並且依照需求將報表另存新檔。

本書一到三章為第一篇「建立分析資料庫」,主要介紹利用VBA程式網路爬蟲取得想分析的資料,建立Excel表格資料庫。本章開始第二篇「資料統計分析」,一開始先彙總第一篇所取得資料,接著介紹善用Excel執行統計分析,重點在於表格和樞紐分析表的工具應用。

先前第二章已經將每天排行榜100筆資料彙總,第三章是以個別書籍為對象,取得補充的出版社及分類資訊,在這一節要介紹利用VBA的Call指令,將這兩章的內容整合成一個大型的程式專案,呼叫之前寫好的程式,一次完整執行,達到自動化的效果。

一、Excel資料整合

將第二章的「匯總報表」和第三章的「書籍補充資訊列表」兩張工作表整合在同一個Excel檔案。

Excel VBA爬蟲程式專案:Call呼叫整合模組及儲存檔案 1

二、VBA Call程式整合

沿用第二章第五節的程式,這裡主要多了「Call 彙總書籍補充資訊(當天日期)」和「Call 取得出版社及分類(當天日期)」,用意是利用VBA Call指令整合所有程式。

Sub 取得網路資料()
=>建立Sub程序
Dim 當天日期 As String
=>宣告文字變數
當天日期 = Format(Date, “yyyymmdd”)
=>利用Format函數定義變數
Call 新增工作表並取得網路資料(當天日期)
=>呼叫副程式
Call 多餘資料刪除(當天日期)
=>呼叫副程式
Call 報表格式整理(當天日期)
=>呼叫副程式
Call 彙總書籍補充資訊(當天日期)
=>呼叫副程式
Call 取得出版社及分類(當天日期)
=>呼叫副程式
Call 分析欄位設置(當天日期)
=>呼叫副程式
Call 工作表匯總(當天日期)
=>呼叫副程式
MsgBox “程式執行完畢”
=>顯示訊息視窗
End Sub
=>結束Sub程序

Excel VBA爬蟲程式專案:Call呼叫整合模組及儲存檔案 3

三、VBA輔助副模組

配合本書章節及整體程式專案架構,「Module1」模組是工作表「匯總報表」更新所需要執行的主模組,其中涉及到取得各書籍出版社及分類的部份,由於程式較為複雜,另外放在「Module2」模組,等於是工作表「書籍補充資訊列表」更新所需要執行的副模組。

Sub 彙總書籍補充資訊(當天日期)
=>建立Sub程序,取得Call呼叫所傳遞的參數值
當天日期附網址 = 當天日期 & “-附網址”
=>宣告變數,以「&」符號合併參數及文字
Call 取得網頁網址(當天日期附網址)
=>呼叫其他Sub程序並傳遞參數值
Call 標記當天新書(當天日期附網址)
=>呼叫其他Sub程序並傳遞參數值
Call 取得出版社資料(當天日期附網址)
=>呼叫其他Sub程序並傳遞參數值
Call 取得分類資料(當天日期附網址)
=>呼叫其他Sub程序並傳遞參數值
Call 非當日新書刪除(當天日期附網址)
=>呼叫其他Sub程序並傳遞參數值
Call 工作表匯總(當天日期附網址)
=>呼叫其他Sub程序並傳遞參數值
End Sub
=>結束Sub程序

Excel VBA爬蟲程式專案:Call呼叫整合模組及儲存檔案 5

四、CurrentRegion優化

這裡的重點是將「資料筆數」從「UsedRange.Rows.Count」優化為「CurrentRegion.Rows.Count」,這是因為UsedRange是使用範圍,雖然資料被刪除了,在工作表上已經沒有了,但仍然算是使用過的範圍,在進行「UsedRange.Rows.Count」列數計算時仍然會納入,實際執行有可能會導致錯誤,因此改為CurrentRegion目前範圍,這樣就不用擔心已刪除資料的問題,因為它不會在目前範圍中。

Public Sub 工作表匯總(當天日期附網址)
=>這裡的「Public Sub」多了一個「Public」,意思是公共程序,可以被其他程序(Sub)呼叫引用。前面的程式只有一個「Sub」而不是「Public Sub」,其實結果是一樣的,因為VBA對於「Sub」的預設值便是「Public」,因為沒有特地寫的話,「Sub」一樣會是「Public Sub」,本節重點是Call呼叫指令,所以加以標註並說明,讓讀者更加清楚。
‘定義變數
Dim 資料筆數 As Integer
=>定義「資料筆數」為整數型態的變數
資料筆數 = Sheets(“書籍補充資訊列表”).Cells(1, 1).CurrentRegion.Rows.Count
=>以工作表「書籍補充資訊列表」的儲存格第一列第一欄為基準,Cells(1,1)指的就是第一列第一欄,Cells括號裡第一個「1」是表示列、第二個「1」表示欄,後面再加上「.CurrentRegion.Rows.Count」是以Cells1(1,1)為左上角有向右向下有資料存在的儲存格範圍,計算此範圍有多少列(Rows.Count),等於是計算有多少筆資料。
‘工作表匯總並刪除重複的標題列
Worksheets(當天日期附網址).UsedRange.Copy Sheets(“書籍補充資訊列表”).Cells(資料筆數 + 1, 1)
=>程式新增取得資料的工作表為「當天日期附網址」,複製此工作表的已使用資料的表格範圍(Worksheets(當天日期附網址).UsedRange.Copy),貼到工作表「書籍補充資訊列表」,從目前資料最後一筆的下一列開始添加(Sheets(“書籍補充資訊列表”).Cells(資料筆數 + 1, 1)),這裡的Cells(資料筆數 + 1, 1)先前有說明過,其中「資料筆數+1」是第幾列、「1」是第一欄。
Application.DisplayAlerts = False
=>正在在Excel刪除有資料的工作表時會提示確是否刪除,在此為加快程式自動化執行,暫時取消提醒視窗機制
Worksheets(當天日期附網址).Delete
=>刪除以「當天日期附網址」作為變數名稱的工作,也就是刪除網路爬蟲程式取得資料的工作表
Application.DisplayAlerts = True
=>恢復提醒視窗機制,和先前的「Application.DisplayAlerts = False」是相呼應的,暫時取消,程式自動刪除工作表不提示確認,再恢復機制
Sheets(“書籍補充資訊列表”).Cells.Hyperlinks.Delete
=>將工作表中所有儲存格的連結刪除
Sheets(“書籍補充資訊列表”).Cells.Font.Name = “Noto Sans CJK TC Medium” ‘
=>設定工作表儲存格字型為「Noto Sans CJK TC Medium」,這是Google所開發的免費字型,贊贊小屋習慣使用此字型,讀者也可以更改為目前Windows系統常用的「微軟正黑體」
End Sub
=>結束程序

Excel VBA爬蟲程式專案:Call呼叫整合模組及儲存檔案 7

五、多餘資料刪除

這裡要特別再提醒多餘資料刪除的程式,是以i、i+1、i+4、i+5作為條件,這是因應原始網頁的資料規則所設置的,如果在實際執行網頁格式有變更,需要同步更新。

Sub 多餘資料刪除(當天日期)
=>建立程式
Dim UsedR As Integer, KeyW As String
=>宣告整數及文字變數
UsedR = Worksheets(當天日期).UsedRange.Rows.Count
=>計算工作上使用範圍有多少列數
For i = 1 To Worksheets(當天日期).UsedRange.Rows.Count
=>以工作表使用範圍變數建立迴圈
If Left(Worksheets(當天日期).Cells(i, 1).Value, 3) = “TOP” Then
=>利用Left函數取得逐行資料左邊3個字元,以如果是TOP作為條件
Worksheets(當天日期).Cells(i, 5).Value = “保留行”
=>條件成立的話,同一列的第5欄儲存格標記「保留行」
Worksheets(當天日期).Cells(i + 1, 5).Value = “保留行”
=>條件成立的話,下一列的第5欄儲存格標記「保留行」
Worksheets(當天日期).Cells(i + 4, 5).Value = “保留行”
=>條件成立的話,下面第四列的第5欄儲存格標記「保留行」
Worksheets(當天日期).Cells(i + 5, 5).Value = “保留行”
=>條件成立的話,下面第五列的第5欄儲存格標記「保留行」
End If
=>結束if條件子句
Next i

執行下一筆迴圈

For i = Worksheets(當天日期).UsedRange.Rows.Count To 1 Step -1
=>建立從報表最後一行到第一行的迴圈
If Left(Worksheets(當天日期).Cells(i, 5).Value, 3) <> “保留行” Then
=>如果迴圈的某一行資料有等於「保留行」的話
Rows(i).Delete
=>將該行刪除
End If
=>結束條件式
Next i
=>執行下一筆迴圈
Columns(5).Delete
=>第五欄刪除
End Sub
=>結束程序

Excel VBA爬蟲程式專案:Call呼叫整合模組及儲存檔案 9

六、VBA儲存檔案

這裡基本上是沿用本書第二章第五節的程式範例,不過先是設計像「Filnam = “C:\Users\b8810\Downloads\” & Today & “.xlsx”」這樣固定路徑的資料夾,跨電腦執行容易出問題,在此更改為「ThisWorkbook.Path」,表示是目前活頁簿路徑,如此較為靈活,在任何電腦上執行程式都不會出錯。

這裡使用到了VBA Dir命令查詢特定資料夾名稱,Dir的第一個參數為查詢對象,第二個參數vbDirectory表示查詢類型是目錄或資料夾,利用條件式判斷,如果查詢長度為零,表示這個資料夾不存在(If Len(Dir(檔案資料夾, vbDirectory)) = 0),那麼就以MkDir命令新增資料夾(Then MkDir 檔案資料夾),等於是加入自動偵錯的機制。

Public Sub 工作表匯總(當天日期)
=>建立程序
Worksheets(當天日期).Copy
=>複製工作表到新活頁簿
檔案資料夾 = ThisWorkbook.Path & “\原始檔案”
=>設定存檔路徑為目前活頁簿資料夾中的「原始檔案」子資料夾
If Len(Dir(檔案資料夾, vbDirectory)) = 0 Then MkDir 檔案資料夾
=>如果資料夾不存在,建立資料夾
Application.DisplayAlerts = False
=>取消提醒視窗機制
ActiveWorkbook.SaveAs 檔案資料夾 & “\” & 當天日期 & “.xlsx”
=>將新增的活頁簿以當天日期為名稱儲存
Application.DisplayAlerts = True
=>恢復提醒視窗機制
ActiveWorkbook.Close
=>關閉目前活頁簿

Dim SumRan As Integer, SumSht As Worksheet
=>宣告整數及工作表變數
Set SumSht = Sheets(“匯總報表”)
=>定義SumSht變數為「匯總報表」工作表
SumRan = SumSht.UsedRange.Rows.Count
=>計算工作表使用範圍有多少列
Worksheets(當天日期).UsedRange.Copy SumSht.Cells(SumRan + 1, 1)
=>將「當天日期」工作表的使用範圍複製到SumSht的報表下面
SumSht.Rows(SumRan + 1).Delete
=>刪除新複製資料的標題列
Application.DisplayAlerts = False
=>取消提醒視窗機制
Worksheets(當天日期).Delete
=>刪除工作表
Application.DisplayAlerts = True
=>恢復提醒視窗機制

SumSht.Select
=>選取工作表
Columns(“A”).ColumnWidth = 10
Columns(“B”).ColumnWidth = 5
Columns(“C”).ColumnWidth = 40
Columns(“D”).ColumnWidth = 15
Columns(“E”).ColumnWidth = 5
Columns(“F”).ColumnWidth = 45
Columns(“G”).ColumnWidth = 15
Columns(“H”).ColumnWidth = 35
=>以上程式皆為設定欄寬
Rows(“1:1”).HorizontalAlignment = xlCenter
=>特定列水平置中對齊
Rows(“1:1”).Font.Bold = True
=>特定列字型粗體
Columns(“B:B”).HorizontalAlignment = xlCenter
Columns(“E:E”).HorizontalAlignment = xlCenter
=>以上兩行程式碼為特定欄垂直置對齊
Cells.Font.Name = “Noto Sans CJK TC Medium”
=>設定工作表所有儲存格的字型
End Sub
=>結束程式

Excel VBA爬蟲程式專案:Call呼叫整合模組及儲存檔案 11

七、程式專案資料

這一節程式全部都結合成單一的「取得網路資料」巨集,執行後果然得到新增當日資料的報表,包括出版社及分類,這裡注意到日期是10/30和11/14,表示即使中間有幾天斷掉了,程式仍然會自動前後彙總在一起。

Excel VBA爬蟲程式專案:Call呼叫整合模組及儲存檔案 13

實務專案優化VBA程式

這一節程式範例算是比較大型的專案了,不過其實都是沿用先前章節內容,讀者應該不致於有太大的障礙。這裡剛好是很好的範例,可以看到如何利用Call呼叫程式並傳遞參數的方式,將各個模組程序整合成一個專案。

本節有將「資料筆數」從「UsedRange.Rows.Count」改為「CurrentRegion.Rows.Count」,同時也將檔案儲存從固定路徑的資料夾,更改為「ThisWorkbook.Path」,這個是在實際執行好幾天的程式之後,發現程式可以優化的地方。本書到這裡大致已經介紹相當完整的VBA指令,讀者也可以依照需求自行修改,作者在此是起到拋磚引玉的作用。

Excel VBA爬蟲程式專案:Call呼叫整合模組及儲存檔案 17
加入Line社群,口袋裡的Excel小教室!

完整學習:VBA爬蟲專案文章合輯