VBA如何取得網頁資料時自動整理格式

本書前面幾章已基本介紹如何以VBA取得網頁資料,本章上一節介紹了台灣銀行匯率的網址結構,接下來當然要以VBA方式取得銀行匯率資料。有自己先嘗試過的讀者應該發現,Excel VBA所取得匯率資料,在格式上會有不必要空格和錯位的情形,畢竟網頁資料不是為Excel所準備的,這種情形不但會發生在台灣銀行網頁,也很有可能發生在其他網頁,為達到有效率取得網頁資料,勢必要取得資料同時調整格式,本節即介紹如何設計格式調整的VBA程式碼。

一、沿用上一章所熟悉的VBA程式碼:

二、所圖所示,取得資料是網頁表格的部份,剛好符合需要。下載會發現Excel欄位變得很寛,所以手動調整了欄寛、將儲存格設定為置中對齊,另外和原始網頁兩相比較,應該是因為表格合併,造成圖片標黃色部份有錯位的情形。

三、每次下載一個網頁,都必須整理格式和調整錯位,像這樣機械式的操作,便是VBA可以發揮的地方之一。首先,各位讀者應該還記得第一章第四節所介紹的「錄製巨集程式」。

四、將原始下載資料整理成如圖所示。

五、原始錄製的巨集程式,總共有152行!

六、整理後的程式碼共12行。利用Excel錄製的巨集極具參考價值,但是如同第一章第五節「編寫巨集程式」所述,現成的程式碼可能無法執行、可能太多不必要的設定,因此進一步瞭解研究並加以改造,才是學習VBA的王道。如圖所示,新增一個「Module4」,內容參考所錄製的巨集「Module3」,於VBA編輯環境可以將兩個以上模組都點開視窗,在不同模組複製貼上非常方便。

七、程式碼說明如下:

Columns(“A:I”).ColumnWidth = 10

設定A到I欄的欄寛為10。

Columns(“A:I”).HorizontalAlignment= xlCenter

設定A到I欄的水平置中。

Range(“C1”).Cut:Range(“B1”).Select: ActiveSheet.Paste

Range(“B2”).Cut:Range(“C2”).Select: ActiveSheet.Paste

Range(“D1”).Cut:Range(“C1”).Select: ActiveSheet.Paste

Range(“G1”).Cut:Range(“E1”).Select: ActiveSheet.Paste

以上四行程式碼,第一行是剪下「C1」,貼上到「B1」,接下來三行以此類推。程式碼中間的「:」,是VBA程式碼小幫手,作用是把兩行較短的代碼串連起來,和先前介紹的換行符號「 _」,一個是合併程式碼、一個是切開程式碼,作用剛好相反。

Range(“A1:A2,B1:B2,C1:D1,E1:F1”).MergeCells= True

將「A1:A2」、「B1:B2」等範圍儲存格合併。

Range(“A1:F2”).Interior.ColorIndex= 45

Range(“A1:F2”).Font.ColorIndex =2

Range(“A1:F2”).Font.Bold = True

以上三行程式碼,分別設定儲存格填滿顏色、字型色彩、粗體,關於「Colorindex」顏色代碼,可以設置56種顏色,如下圖所示。

Range(“A1:F22”).Borders.LineStyle= 1

「A1:F22」範圍裡的儲存格字體設置為粗體。

Range(“C3:F22″).Style =”Comma”

「C3:F22」範圍裡的儲存格數值格式為仟分位、小數點兩位。

所有網頁取得的資料,如同直接於Excel編製的報表,格式上都需要再作調整。以這一章為例,匯率資料是會經常性取得的,當然不會想說每次取得、每次都要再調整格式,因此編寫設計相關VBA程式碼時,應該把內容格式也考量在內。

取得網頁資料是稍微複雜的程式,格式調整相對而言較為簡單容易理解,還能像這一節所示,透過Excel本身錄製巨集的方式作為參考工具,因此何樂而不為呢。擴大而言,除了匯率資料,無論是取得哪一類型網頁的資料,都可以用這一節相同方式自動調整資料格式。

本文內容取自《人人做得到的網路資料整理術》:http://www.books.com.tw/products/0010775391