Excel VBA以Application.InputBox選擇年份幣別,建立網路爬蟲網址清單

Excel VBA網路爬蟲最關鍵的是網頁網址。本文以台灣銀行匯率為例,介紹利用For Next、If Then Else、Cstr建立清單,再以Application.InputBox方法引用儲存格參照作為年份幣別確定網址。

上一節透過定義月份變數的方式,將期間固定為一到十二月,一次下載2015年度各個月份的美元匯率。除了特定年度及幣別,實務上有時需要下載不同年度、不同幣別的歷史匯率資料,甚至是一次下載許多年度和幣別的資料,以VBA程式碼而言,亦即關於歷史匯率還有兩個關鍵變數:年度及幣別。透過定義變數的方式,再搭配For…Next的迴圈設計,視情況也許需要加上條件判斷,便可以達到自由選擇年度及幣別的功能。這一節先詳盡說明條件判斷語法,接著以先前章節介紹過的Application.InputBox方法,於對話方塊輸入幣別。

一、VBA If Then條件判斷

稍加修改上一節批次取得匯率的程式碼,重點說明條件判斷的程式寫法。

批次取得匯率的程式碼

說明如下:

Sub IfFunction()

編寫名稱為「IfFunction」的巨集程式碼。

Dim i As Integer, Month, WebAddress, DesCell As String

Dim是宣告變數,其中i為整數(Integer),Month、WebAddress、DesCell為文字(String),如果省略,VBA會自動將出現的變數設定為萬用類型(Variant)。在電腦的記憶體倉庫裡,每種變數類型佔用的空間不同,萬用類型所佔用空間當然是最多的,因此要養成宣告變數的習慣,一來避免降低Excel執行效率,二來寫長程式必須結構嚴謹,明確宣告變數便是嚴謹的要求之一。

For i = 1 To 12

設置1到12的迴圈,如前面文章所述,這裡其實省略了「step 1」,因為對執行效率沒有影響,以整數為迴圈是一般用法,所以毋須像Dim宣告變數那麼嚴謹。

If i < 10 Then

這個應該蠻容易理解,「If+條件+Then」是VBA規範寫法,如圖所示,一定要換行之後,再寫滿足條件時的執行程式,然後換行「Else」,接著再換行寫否則時的執行程式,最後也一定不能漏掉換行「End If」,在VBA編寫條件判斷程式,一定要照這樣的規矩走,不然程式無法順利被讀取執行,Excel會跳錯。

Month = “0” & CStr(i)

滿足i<10條件時,將文字變數Month前面加一個零,這個套用Cstr函數,「CStr(i)」用意是將整數類型的變數i轉換成特定文字,前面「”0″」是加雙引號表示文字,和Excel資料編輯列相同用法。例如在迴圈i=1的情況下,這裡的Month便是文字「01」,依照迴圈和條件式跑下來,Month會從「01」一直跳到「09」。

Else

在整個條件語句結構,這裡是表示條件不滿足、否則的時候要怎麼做。

Month = CStr(i)

當「i<10」不成立時,設定Month為轉換成文字的i變數,因為一開始的迴圈是「For i = 1 To 12」,這裡的結果便是「10」、「11」、「12」,分別會於第10次、第11次、第12次迴圈時賦值。

End If

結束If判斷句。

WebAddress = “URL;https://rate.bot.com.tw/xrt/quote/2015-” & Month & “/USD”

設定文字變數WebAddress,在固定的網址中間,套用一個文字變數Month。順著迴圈跑「For i = 1 To 12」,便是2015年1到12月的匯率網址。

DesCell = Cells((1 + (i – 1) * 25), 1).Address

設定文字變數DesCell,搭配迴圈為工作表座標(1,1)、(26,1)、(51,1)等,「.Address」意思是取儲存格的參照。於此再複習一次,VBA的標準語法為「物件.屬性」或「物件.方法」,這裡的物件為Cells,屬性為其參照。

Cells(i, 1).Value = Month

Cells(i, 2).Value = DesCell

Cells(i, 3).Value = WebAddress

依照迴圈順序,將三個文字變數的值,顯示於工作表。

Next i

執行變數i的下一次迴圈。

End Sub

結束程式。

二、建立12個月份網址清單

執行結果如圖所示。

執行結果如圖所示

三、建立15種幣別清單

整理台灣銀行的幣別資料如圖片所示,在這裡選擇比較常見的15種幣別。

整理台灣銀行的幣別資料如圖片所示

四、Application.InputBox方法

修改程式碼如圖所示。Dim多增加了變數Year及Currence的宣告,多了兩行引用「Application.InputBox」方法定義變數,其中「Type:=2」為輸入文字、「Type:=8」為儲存格參照,關於不同Type型態的說明,可參考第三章第三節「新增工作表視窗」第一步驟的圖片。另外也依照變數設定,更改網址定義:「WebAddress = “URL;https://rate.bot.com.tw/xrt/quote/”& Year & “-” & Month & “/” & Currence」。

Dim多增加了變數Year及Currence的宣告

五、跳出視窗輸入

執行Currence巨集程序,首先於跳出來的視窗中,直接輸入「四位數西元年份」,範例是輸入「2016」。

執行Currence巨集程序

六、選擇幣別

「選擇儲存格參照」為「B3」(港幣HKD),顯示於視窗即為「幣別!$B$3」。

「選擇儲存格參照」

七、所選擇幣別的網址清單

如圖片所示,成功列出2016年HKD的歷史匯率網址。

成功列出2016年HKD的歷史匯率網址

工作表上先建立目標網址清單

這一節成功設置了年份和幣別的選擇,然而僅僅是在工作表上體現儲存格參照和網址,用意在於瞭解如何適當地定義變數,下一節即以此為基礎,進一步取得批次網頁資料並修改格式。

每天學習,每天充電:VBA爬蟲文章合集

最新文章: