VBA CreateObject用法是很值得學習的技巧,它可以直接引用Windows系統資源,擴充Excel程式設計的範圍。本文以IE瀏覽器作為介紹範例,讓你開始入門成為進階的開發人員。
目錄
Toggle「CreateObject(“InternetExplorer.Application”)」和「QueryTables.Add」雖然都可以取得網頁資料,但在本質上是不太一樣的工具。「QueryTables.Add」是Excel基於便利性特地為使用者設計的一個制式化指令,和諸如「篩選」、「樞紐分析表」等Excel指令是同一類的東西。
「CreateObject(“InternetExplorer.Application”)」在本質上是一個獨立的應用程式,是微軟所開發的IE網頁瀏覽器,和Excel剛好都是微軟所開發,有高度共通性,所以在Excel需要取得網頁資料、或者是更廣義想要其他電腦應用程式互動時,可以透過VBA 創建對象「CreateObject」的方法,等於是開外掛將其他應用程式打開﹐方便Excel完成跨應用程式的工作任務,在以具體範例作說明:
一、設定引用項目
在VBA編輯環境中,指令列選擇「工具」頁籤,再選擇「設定引用項目」。
二、Microsoft Internet Controls
在跳出來的視窗勾選「「Microsoft HTML Object Library」及「Microsoft Internet Controls」,然後按「確定」。
三、瀏覽物件
有心想進一步鑙研相關知識,可在指令列選擇「檢視」、「瀏覽物件」,或者按快速鍵「F2」,便會跳出物件圖書館,這裡有目前VBA所有可用物件的清單和說明。在像VBA這樣的程式語言中,基本元素有四類:物件、屬性、事件、方法。以熟悉的Excel操作為例,儲存格是一個較底層的物件,它是隸屬於工作表、活頁簿等物件集合之中,儲存格格式是該物件的屬性,點選這個儲存格是一個事件,複製這個儲存格是一個方法(狹義的操作)。然後在VBA語法中,基本的結構有兩種:「物件.方法」、「物件.屬件=值」。其實Excel操作和VBA語法是表裡的一體兩面,在本書中有這麼多的VBA範例,只要想想它在Excel是如何操作的,應該對於此程式的表裡兩面會有深切領悟。最後,以圖片示所為例,「InternetExplorer」是一個物件,亦即IE瀏覽器應用程式,Visible是該應用的一個屬性,可以看到下面個說明:「Determines whether the application is visible or hidden.」,意思為是否顯示此物件。關於此物件及屬性,稍後以範例說明會更加清楚。
四、CreateObject IE
編寫VBA程式碼如下:
10:建立名稱為「Tax_Html_Text」的巨集程序
20:宣告名稱為「ie」為「InternetExplorer」的物件
30:宣告doc、news、Articles等物件
40:建立名稱為「ie」的IE瀏覽器應用程式物件
50:建立一個對象為「ie」的區塊,在「with…End With」之間該對象的屬性及方法可用「.」替代,方便於指定對象執行一連串的程式陳述句,毋須在每一行重覆指定對象,如此不僅簡約程式碼,同時因為減少VBA讀取對象的次數,節省了有限的電腦計算資源。
60:設定此物件的「Visible」屬性為「True」,如同步驟三所述,作用是可以看到VBA外掛的物件運作,亦即IE瀏覽器。
70:設定瀏覽網址。
80~100:這是在執行預計時間較長程式的慣用語句,意思是在網頁下載完成前,暫時將系統操作權交還給使用者,避免VBA一直在下載網頁,中間完全無法作其他操作。「Do…Until…Loop」是一個直到某條件為止的循環程式語句,英文涵義應該不難理解,以後有適當範例再多作介紹。
110:將瀏覽器所取得的網頁HTML文件設定為「doc」。
120:搜尋網頁文件「doc」裡,類別名稱為「list-group-item」的節點集合,並且集合中的第二個設定為「news」(在HTML集合中,第一個出現的為「(0)」,第二個為「(1),依此類推)。
130~160:分別將把「news.outerhtml」、「news.innerhtml」、「news.outerText」、「news.innerText」指定給普通變數「Articles1」、「Articles2」、「Articles3」、「Articles4」。關於這四個屬性,稍後以實際得到結果說明會更加清楚。這裡的「Let」和上一行程式的「Set」同樣皆為定義變數,不過「Let」定義的對象為普通變數,幾乎都會省略,「Set」定義的對象為物件變數,不可省略。
170~200:將「Articles1」、「Articles2」、「Articles3」、「Articles4」這四個變數值輸出於指定的儲存格。
210:結束with區塊。
220:結束「Tax_Html_Text」程序。
五、IE瀏覽器外掛
首先會跳出一個IE視窗,等於是VBA外掛產生一個相聯結的瀏覽器物件,由於設定了此物件的「Visible」屬性為「True」,不但可以看到這個物件,還能像一般網頁般操作,它就是個真正的IE視窗,只不是因為是由VBA所產生,其網頁資料可以再匯回來給VBA。
六、HTML標籤
所取得資料經過格式調整,如下圖所示,經過這麼實際操作,應該能理解「Outerhtml」、「Innerhtml」、「OuterText」、「InnerText」的差別,outer與inner的差別在於是否包含設定對象的標籤本身,於此範例便是「div class=”ap01_02″」及「/div」,Html表示包含非網項內容的HTML標籤,Text代表僅有網頁所顯現出來的內容文字。
七、DOM網頁元素
這一節範例第120行代碼:Set news = doc.getElementsByClassName(“ap01_02”)(0),代表取得某特定分類名稱的網頁內容,在HTML裡的專業術語叫標籤(Tag)及元素(elements),一段網頁內容是一個對象,由前後標籤包含的一個元素,上個步驟便是由標籤「div」及「/div」所包含元素,網頁所呈現的內容為「稅務新聞」,標籤則是告訴瀏覽器該內容要如何呈現。可想而知,一個完整豐富的網頁會有許多元素,這些元素由上到下會構成一個樹狀圖般的結構,稱之為DOM (Document Object Model) ,除了以分類名稱指定特定對象,VBA還提供了許多方法,詳見下表。
Excel+VBA+IE+HTML=Web Scraping
HTML本身是一門很值得研讀的學問,內容不會很艱深,所涉及到的是現代人每天都會接觸的網頁對象,讀者有興趣的話,網路上有相當多的說明文件。在這裡以VBA取得網頁資料而言,通常要取得的對象都是非常明確,例如這節範例的稅務新聞,於此情形下,使用Chrome瀏覽器的「檢視網頁原始碼」功能,適當以F4搜尋,再搭配getElementsByClassName、getElementById 、getElementsByTagName 、getElementsByName等及第七個步驟補充方法,應該都可以順利取得所需要的資料。
歡迎加入Line社群,取得贊贊小屋範例檔案。
VBA課程推薦:零基礎入門進階的20小時完整內容