贊贊小屋

Excel VBA網路爬蟲12個月匯率:Interior.Colorindex 及For Next雙迴圈事件

Excel VBA分析網址結構並瞭解資料規則後,利用For Next迴圈事件便可以批次網路爬蟲。本文首先介紹如何建立顏色代碼表,再設計程式一次取得12個月外幣匯率。

如果你進入Excel顏色代碼表的連結,會看到代碼表。那張表本身便是VBA程式碼自動產生的,運用的是一般程式設計裡都有的「迴圈」概念。在依照某個規則大量運行程式的場合,非常適合設計迴圈。這一節首先以顏色代碼表為例,簡單介紹迴圈如何設置,接著再以這一章取得匯率的主題,運用迴圈批次取得一整年12個月的歷史匯率。

一、迴圈建立色彩索引

圖片所示的程式碼說明如下:「Cells.Clear」先清除工作表。「For i = 1 To 14 Step 1」以變數「i」設置迴圈程式,從「1」開始到「14」,「Step 1」表示1、2、3數列的順序,如果是「Step 2」便是2、4、6數列,這裡是為求範例完整加了個「Step 1」,其實這是系統預設值可以省略的。「Cells(1, i)」是儲存格對象,裡面的「1」代表第幾列、「i」代表第幾欄,「Value」為儲存格值的屬性,「Interior.Colorindex」為儲存格背景色彩的屬性。圖片綠色部份前面加「’」為註記文字,這是編寫程式的慣常用法。

「Interior.Colorindex」為儲存格背景色彩的屬性

二、Colorindex示意圖

執行巨集「Colorindex」的結果如圖所示。

執行巨集「Colorindex」

三、雙迴圈顏色代碼圖

依照相同思惟,外面再加一層變數「j」迴圈,計算式作一些變化,編寫如圖所示的程式,執行結果便是上一節最後一張顏色代碼圖。

外面再加一層變數「j」迴圈

四、取得網路匯率程式

接下來要將迴圈設計的概念,嵌入取得匯率資料的程式裡,以便一次取得數期的歷史匯率。首先,整理先前的程式碼,取消InputBox,回到一開始直接寫入網址及位置的方式,最後加一個欄寛及水平置中的格式調整,避免每次下載完欄位變得很寛,很不方便,其餘顏色及框線僅僅是美觀考量,暫不設置。

將迴圈設計的概念,嵌入取得匯率資料的程式裡

五、一個月匯率資料

執行結果如圖所示,標黃色部份是第23行,也就是網頁一個月的匯率資料有22行,如果要一次取得12個月的資料,等於是設置「step 22」的迴圈,讓第二個月的資料從第23行開始,第三個月之後依序類推。

等於是設置「step 22」的迴圈

六、迴圈取得網路匯率

在上一步驟的基礎上,設計迴圈,編寫程式如圖所示。這裡用到的概念有變數計算、迴圈設置、條件判斷、格式調整,除了條件判斷,其他概念在先前章節都有相關範例,條件判斷在下一節再多加介紹。

這裡用到的概念有變數計算、迴圈設置、條件判斷、格式調整

七、12個月匯率資料

執行「DownloadWeb」巨集,成功一次取得2015年12個月的美金歷史匯率,下載到Excel工作表上,因資料量多,以20%比例呈現,Excel會自動加上外部資料的標題,從「外部資料1」到「外部資料12」,包含中間空白總共有300行的歷史匯率明細。

成功一次取得2015年12個月的美金歷史匯率

程式所取得資料再分析

從略縮圖來看,顯然在不同月份的資料表之間,留有許多行的空白,在格式也沒有多作設定,必須下載完資料後再作整理。另外這個程式碼是固定的,例如我如果想要的是2016年的美金資料、或者是2015年的歐元資料,只能再修改程式碼,凡此種種,都需要進一步設計完善,這個會在往後章節繼續介紹。

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

博客來網路書店網址:

Excel VBA網路爬蟲12個月匯率:Interior.Colorindex 及For Next雙迴圈事件 VBA網路爬蟲 第8張