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呼叫整合模組及儲存檔案 15
人人都學得會的網路大數據分析入門》
Excel VBA爬蟲程式專案:Call呼叫整合模組及儲存檔案 17
歡迎加入Line社群,口袋裡的VBA小教室!
Ask ChatGPT
Set ChatGPT API key
Find your Secret API key in your ChatGPT User settings and paste it here to connect ChatGPT with your Tutor LMS website.