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指令,讀者也可以依照需求自行修改,作者在此是起到拋磚引玉的作用。

每天學習,每天充電:VBA爬蟲專案系列文章

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