贊贊小屋

Excel VBA巨集也能匯出匯入,輕鬆備份共享,完整說明VBA網路爬蟲程式

Excel VBA程式都是寫在類似Word的模組白板裡,可以像上方功能區一樣匯入匯出,藉此實現備份及共享。本文同時詳述QueryTables指令,作為介紹VBA網路爬蟲實務的基礎。

第一章介紹Excel專用載入網頁內容的命令,雖然很方便,但如果是需要大量持續地取得相關網頁資料、進一步整理分析的場合,顯然一次又一次的單獨操不是很有效率。第一章最後一節分享以VBA程式碼方式一鍵取得網頁資料,本章即以此為基礎,進一步說明如何適當應用Excel的VBA,取得個人所需的網頁資料。這一節首先介紹程式如何匯出匯入,因應不同需要作複製及延伸:

一、在VBA編輯環境中,程式碼是存在「模組」這個地方,以上一節為例,在右邊的「Module1」滑鼠右鍵,「匯出檔案」。

Excel VBA巨集也能匯出匯入,輕鬆備份共享,完整說明VBA網路爬蟲程式 VBA網路爬蟲 第1張

二、在「匯出檔案」視窗中,輸入希望的檔案名稱,選擇一個適當的資料夾,注意到「存檔類型」是「Basic檔案(*.bas)」,表示這是VBA程式碼,最後按「存檔」。

Excel VBA巨集也能匯出匯入,輕鬆備份共享,完整說明VBA網路爬蟲程式 VBA網路爬蟲 第2張

三、在新增的Excel檔案中,上方功能區移到「開發人員」頁籤,在「程式碼」中選擇「Visual Basic」:「開啓Visual Basic編輯器」。

Excel VBA巨集也能匯出匯入,輕鬆備份共享,完整說明VBA網路爬蟲程式 VBA網路爬蟲 第3張

四、「檔案」、「匯入檔案」。

Excel VBA巨集也能匯出匯入,輕鬆備份共享,完整說明VBA網路爬蟲程式 VBA網路爬蟲 第4張

五、「開啓」第二個步驟所儲存的程式碼檔案。

Excel VBA巨集也能匯出匯入,輕鬆備份共享,完整說明VBA網路爬蟲程式 VBA網路爬蟲 第5張

六、「專案-VBAProject」多了一個模組資料夾,裡面的「Module1」便是上一節編輯好的程式碼。

Excel VBA巨集也能匯出匯入,輕鬆備份共享,完整說明VBA網路爬蟲程式 VBA網路爬蟲 第6張

七、由於這是VBA取得網頁資料的重要程式,完整解釋程式碼如下:

Sub DownloadWeb()

建立一個VBA巨集程序,名稱為「DownloadWeb」。

Application.CutCopyMode = False

清空剪貼簿。

With ActiveSheet.QueryTables.Add _

With……End With是一組固定用法,方便設置同一對象的各種屬性,中間例如「.Name = “index”」表示將這對象的「Name」屬性設置為「index」,「QueryTables.Add」是VBA取得外部資料來源的命令,「ActiveSheet.QueryTables.Add」表示將取得的外部資料建立在目前工作表,「 _」空一格再緊接著下橫線是VBA慣用符號,將過長的程式碼換行。

(Connection:=”URL;https://money.udn.com/money/index”, _

外部資料來源的路徑,可以是資料庫或者文字檔,這裡是引用網頁內容,所以是想要取得資料的網址,「 _」同樣是換行符號。

Destination:=Range(“$A$1”))

所取得外部資料的目的地,熟悉樞紐分析表的讀者,對於這裡的路徑和目的地應該覺得很類似。

‘.CommandType = 0

因為錄製巨集所產生的不必要參數,如上一章最後一節所述,前面加一個單引號「’」,已經轉換成單純文字,其實也可以直接刪除。

.Name = “index”

設置這個外部資料的名稱。

.FieldNames = True

「True」代表所取得外部份資料有標題欄。

.RowNumbers = False

是否將列號指定為新增資料表的第一欄,比較不適用於取得網頁資料庫,設置為「False」。

.FillAdjacentFormulas = False

是否於重新整理時更新資料表右邊的公式,比較不適用於取得網頁資料庫,設置為「False」。

.PreserveFormatting = True

是否保留格式,通常設置為「True」。

.RefreshOnFileOpen = False

開啓檔案時是否更新,「False」代表不自動更新。

.BackgroundQuery = True

是否於後台背景中執行,設置為「True」代表Excel在取得資料同時,可以進行其他操作。

.RefreshStyle = xlInsertDeleteCells

取得資料時對於原工作表的插入或刪除方式,以便寫入外部資料,通常會在空白工作表匯入,所以保留預置值即可。

.SavePassword = False

是否儲存密碼,比較不適用於取得網頁資料,通常設置為「False」。

.SaveData = True

是否儲存所取得資料,通常設置為「True」。

.AdjustColumnWidth = True

是否自動調整欄寛,通常設置為「True」。

.RefreshPeriod = 0

設定重新整理間的分鐘數,「0」代表不會自動更新。

.WebSelectionType = xlEntirePage

取得網頁內容的型態,通常設置為「xlEntirePage」,代表取得整個網頁資料。

.WebFormatting = xlWebFormattingNone

是否沿用網頁格式,通常設置為「xlWebFormattingNone」,代表只匯入資料,不匯入格式。

.WebPreFormattedTextToColumns = True

是否同時匯入網頁中HTML資料剖析欄的標籤,通常設置為「True」。

.WebConsecutiveDelimitersAsOne = True

連續分隔符號是否視為單一的分隔字元,通常設置為「True」,有操作過Excel資料剖析的讀者,應該都能理解上面這兩個參數的意義。

.WebSingleBlockTextImport = False

網頁中HTML的

標籤是否一次性匯入,通常設置為「False」。

.WebDisableDateRecognition = False

是否停用匯入資料的日期格式辯識,通常設置為「False」,表示辯識日期。

.WebDisableRedirections = False

網頁查詢時是否重新導向時是否停用,通常設置為「False」。

.Refresh BackgroundQuery:=False

與資料庫建立連線之後,送出查詢執行後是否於背景更新,比較不適用於取得網頁資料,通常設置為「False」。

End With

結束前面以「With」開始,一連串對於參數的屬性設置,經過這麼多的程式說明之後,應該能理解為何要用With……End With簡化程式碼編寫。

End Sub

(「DownloadWeb」)程序結束。

Excel VBA巨集也能匯出匯入,輕鬆備份共享,完整說明VBA網路爬蟲程式 VBA網路爬蟲 第7張

雖然這麼多行的程式碼,經過每一行簡短說明之後,其實還是回歸到最主要「ActiveSheet.QueryTables.Add」,這是VBA取得外部來源資料的主要命令,也是Excel匯入網頁資料的關鍵方法。熟悉Excel樞紐分析表操作的讀者,都知道建立樞紐分析表有兩大參數,其一是資料來源範圍,其二是產生報表的位置,同樣道理套在取得網頁資料,VBA「ActiveSheet.QueryTables.Add」最主要也是兩大參數,以Excel說明手冊的術語來說,其一是「查詢表的資料來源」(Connection),其二是「位於查詢表目的範圍的左上角的儲存格」(Destination)。其他雜七雜八的屬性,如果沒有衍生問題,毋須特別花費時間研究,例如將會造成程式錯誤的「CommandType = 0」直接刪掉即可。

本文內容取自《人人做得到的網路資料整理術》,最好學習方法仍然是書本,今天就買本書吧!

金石堂網路書店網址:https://www.kingstone.com.tw/basic/2014941521928



本網站所有內容皆受版權保護,网站备案编号:苏ICP备14051307号-1