Trong công việc, đôi khi chúng ta cần lấy, truy xuất dữ liệu theo tiêu chí nhất định từ một nhà cung cấp dịch vụ nào đó, ví dụ như lấy hóa đơn điện tử rồi đưa kết quả ra tập tin Excel hoặc tra cứu địa chỉ dựa vào tọa độ vĩ độ, kinh độ bằng Google GeoCoding chẳng hạn. Kết quả trả về thường là tập tin XML, JSON hay HTML, v.v, tuy nhiên phổ biến nhất vẫn là hai định dạng XML và JSON. Đối với định dạng XML, chúng ta có thể sử dụng thư viện Microsoft XML v6.0 để tạo yêu cầu (Request) đến nhà cung cấp dịch vụ và lọc ra kết quả mong muốn trong kết quả trả về và xuất ra tập tin Excel. Còn với định dạng JSON, VBA lại không có công cụ chính nào hỗ trợ xử lý định dạng này ngoài giải pháp của bên thứ ba VBA-JSON:
GitHub - VBA-tools/VBA-JSON: JSON conversion and parsing for VBA (https://github.com/VBA-tools/VBA-JSON).
Ví dụ: Viết một hàm tự tạo tra cứu nhiệt độ, trạng thái thời tiết của một địa điểm:
Chúng ta sẽ sử dụng dịch vụ tra cứu thời tiết từ nhà cung cấp dịch vụ OpenWeather, tất nhiên là việc sử dụng bản miễn phí sẽ có một số hạn chế nhất định so với bản trả phí.
Tiến hành đăng ký tài khoản, đăng ký lấy khóa API, nghiên cứu tài liệu để sẵn sàng viết hàm tự tạo.
View attachment 1440027
Nghiên cứu tập tin XML trả về sau khi Request để lấy những dữ liệu mong muốn. Ở đây chúng ta chỉ quan tâm đến thông tin nhiệt độ và trạng thái thời tiết hiện tại (thẻ temperature và thẻ weather).
XML:
<current>
<city id="3163858" name="Zocca">
<coord lon="10.99" lat="44.34"/>
<country>IT</country>
<timezone>7200</timezone>
<sun rise="2022-08-30T04:36:27" set="2022-08-30T17:57:28"/>
</city>
<temperature value="298.48" min="297.56" max="300.05" unit="kelvin"/>
<feels_like value="298.74" unit="kelvin"/>
<humidity value="64" unit="%"/>
<pressure value="1015" unit="hPa"/>
<wind>
<speed value="0.62" unit="m/s" name="Calm"/>
<gusts value="1.18"/>
<direction value="349" code="N" name="North"/>
</wind>
<clouds value="100" name="overcast clouds"/>
<visibility value="10000"/>
<precipitation value="3.37" mode="rain" unit="1h"/>
<weather number="501" value="moderate rain" icon="10d"/>
<lastupdate value="2022-08-30T14:45:57"/>
</current>
Tiến hành viết hàm tự tạo, tham chiếu đến thư viện Microsoft XML v6.0:
Code:
Option Explicit
Public Function CurrentWeather(Location As Variant, Optional Degree As Variant = "c") As Variant
Dim objXMLHTTP As MSXML2.XMLHTTP60
Dim objXMLDoc As MSXML2.DOMDocument60
Dim objXMLNodeList As MSXML2.IXMLDOMNodeList
Dim objXMLNode As MSXML2.IXMLDOMNode
Dim strTempValue As String, strWeatherCondition As String
Dim strURL As String, strResult As String
Dim strAPI As String
Dim strDegree As String
strAPI = "9d06ba06acac21af5c90d5aa7ea59ddf"
Select Case Degree
Case "c"
strURL = "https://api.openweathermap.org/data/2.5/weather?q=" & Location & "&appid=" & strAPI & "&lang=vi&units=metric&mode=xml"
strDegree = " C, "
Case "f"
strURL = "https://api.openweathermap.org/data/2.5/weather?q=" & Location & "&appid=" & strAPI & "&lang=vi&units=imperial&mode=xml"
strDegree = " F, "
Case Else
strURL = "https://api.openweathermap.org/data/2.5/weather?q=" & Location & "&appid=" & strAPI & "&lang=vi&units=standard&mode=xml"
strDegree = " C, "
End Select
Set objXMLHTTP = New MSXML2.XMLHTTP60
With objXMLHTTP
.Open "GET", strURL, False
.Send
If .Status = 200 Then
Set objXMLDoc = New MSXML2.DOMDocument60
objXMLDoc.LoadXML .ResponseText
Set objXMLNodeList = objXMLDoc.SelectNodes("current")
For Each objXMLNode In objXMLNodeList
strTempValue = objXMLNode.SelectSingleNode("temperature").Attributes.getNamedItem("value").Text
strResult = strTempValue & strDegree
strWeatherCondition = objXMLNode.SelectSingleNode("weather").Attributes.getNamedItem("value").Text
strResult = strResult & strWeatherCondition
Next
End If
End With
CurrentWeather = strResult
Set objXMLHTTP = Nothing
Set objXMLNodeList = Nothing
Set objXMLNode = Nothing
Set objXMLDoc = Nothing
End Function
Hàm CurrentWeather ở trên có hai tham số: Tham số thứ nhất Location lấy giá trị kiểu chuỗi đại diện cho tên thành phố viết bằng tiếng Anh (cái này hơi bất cập một chút vì hỗ trợ mỗi tiếng Anh), tham số thứ hai Degree là tham số tùy chọn, lấy giá trị kiểu chuỗi gồm "c" sẽ cho kết quả nhiệt độ trả về là độ C (Celsius) và "f" sẽ cho kết quả nhiệt độ trả về là độ F (Fahrenheit), mặc định là giá trị "c".
Chạy thử trong Excel để xem kết quả:
View attachment 1440082
Một số ví dụ khác về việc sử dụng API trong VBA:
- Đọc dữ liệu của một vùng (Range) trong tập tin Google Sheets
Giả sử chúng ta có một tập tin Google Sheets đã bật quyền chia sẻ như sau:
View attachment 1443521
Chúng ta muốn đọc dữ liệu trong vùng A1:E5. Như vậy để làm được việc này, chúng ta cần đăng ký dịch vụ Sheets API và lấy khóa API, sau đó đọc tài liệu để nắm được các bước viết macro:
View attachment 1443522
View attachment 1443523
Tiến hành viết macro:
Code:
Option Explicit
Private Sub GetGoogleSheetsRangeValues()
Dim objWinHttpRequest As WinHttp.WinHttpRequest
Dim objDict As Scripting.Dictionary
Dim i As Integer, j As Integer
Set objWinHttpRequest = New WinHttp.WinHttpRequest
With objWinHttpRequest
.Open "GET", "https://sheets.googleapis.com/v4/spreadsheets/1C0pYTtba4H7rg8MKLMVdYt9k6F3kz1MNZX6dw3kUrXo/values/Sheet1!A1:E5?key=API_KEY"
.Send
If .Status = 200 Then
' VBA-JSON: https://github.com/VBA-tools/VBA-JSON
Set objDict = JsonConverter.ParseJson(.ResponseText)
For i = 1 To objDict.Item("values").Count
For j = 1 To objDict.Item("values")(i).Count
Debug.Print objDict.Item("values")(i)(j)
Next
Next
End If
End With
Set objWinHttpRequest = Nothing
End Sub
Private Function Quote(Text As String) As String
Quote = Chr(34) & Text & Chr(34)
End Function
So sánh kết quả trả về với nội dung trong sheet:
View attachment 1443524
2. Tương tác với dịch vụ lưu trữ và chia sẻ trực tuyến FShare:
FShare là một trong nhiều dịch vụ lưu trữ và chia sẻ trực tuyến phổ biến ở Việt Nam. Với việc dịch vụ này hỗ trợ API, chúng ta có thể dễ dàng viết macro để tương tác với dịch dụ này.
Fshare API - Fshare (https://www.fshare.vn/api-doc)
Trong ví dụ này chỉ đề cập đến thao tác đăng nhập vào dịch vụ này bằng API.
Tiến hành nghiên cứu tài liệu:
View attachment 1443602
Khi đăng ký sử dụng API thành công, phía FShare sẽ gửi cho chúng ta một email với nội dung như sau:
View attachment 1443627
Thông tin đăng nhập được gửi qua phần request body. Tiến hành viết macro:
Code:
Option Explicit
Public Sub LoginToFShare()
Dim objWinHTTP As WinHttp.WinHttpRequest
Dim strRequestBody As String
strRequestBody = "{" & _
Quote("user_email") & ": " & Quote("Account_Name") & ", " & _
Quote("password") & ": " & Quote("Account_Password") & ", " & _
Quote("app_key") & ": " & Quote("app_key") & " " & _
"}"
Set objWinHTTP = New WinHttp.WinHttpRequest
With objWinHTTP
.Open "POST", "https://api.fshare.vn/api/user/login"
.SetRequestHeader "User-Agent", "User_Agent"
.SetRequestHeader "Accept", "application/json"
.Send strRequestBody
If .Status = 200 Then
Debug.Print .ResponseText
Else: Debug.Print .ResponseText
End If
End With
Set objWinHTTP = Nothing
End Sub
Private Function Quote(Text As String) As String
Quote = Chr(34) & Text & Chr(34)
End Function
Nếu không có gì sai sót thì response text nhận được sẽ có nội dung đại loại như sau:
JSON:
{
"code": 200,
"msg": "Login successfully!",
"token": "884fde3ee0a7fa60998",
"session_id": "ksuku8vdfqd"
}
Từ bước này, chúng ta có thể thực hiện một số thao tác quản lý tập tin trên FShare, để biết thêm chi tiết, vui lòng tìm hiểu thêm tại đây:
Fshare API - Fshare (https://www.fshare.vn/api-doc#/)
masothue.com
