ExcelVBAでOdata接続（SAP）

※この記事は chillSAP 夏の自由研究2022の記事として執筆しています。

こんにちは、めっきりひきこもりメンタルになりました陰キャなかのんです。
今回はExcelVBAでODataの結果を取得した機会がありましたのでやってみました。その備忘録です。
Excelメニューバーのデータ＞データの取得でOdataを設定すればよいじゃん？ええ、それでもいいです…まぁ備忘録なので…

その前にODataって何ぞや？と言う場合は@Auggie_2345さんの8月7日の記事を参照下さい。
ODataの基本とSAP内部/外部からの動作確認

また、URIの構成などを知りたいときは@tamiさんの↓の記事が細かくて助かっています。
【SAPUI5】OData(1) ODataとは

excelの準備

• ExcelのVBAを起動、ツール-参照設定より以下のライブラリ参照をチェックします。
  　　Microsoft Scripting Runtime　…　後で使うJsonをパースするために使用します。
  　　Microsoft XML, v6.0　…　MSXML2.XMLHTTPを使ってOdataをコールするために使用します。
  

SAP API Business Hubから使うOdataを探す

使ったのはおなじみSAP API Business Hubより、S4HCのodata(v4)”BANK”RequestURLを使いました。なんでもよし。
（今回は取得するほう、”GET”のOdataを使います。こちらから送信するほうはPOSTですね）
TryOutタブに移動して、そのまま「RUN」アイコンクリックすれば実行結果が出ます。
そのときに一緒にRequestURLも表示されているため、こちらをとっておいてください。

せっかくなのでPostmanで繋ぎ

その前にPostmanでも接続確認。なんとなく同じようなものがとれました。GETの設定は簡単です。
①requestURLを貼り付ける
②Authorizationタブの認証設定のところに設定項目を入力
　※ここはただのBasic認証を入れることが多いんですが、Sandbox環境はAPIKeyで接続するので以下設定してください。
　　Authorization Type：APIKey
　　Key:APIKey
　　Value:BusinessHUBで確認した各自のAPIKey
③Sendボタン押下。
④の箇所に結果が出ています。Status：200 OKとでたら接続は成功しています(HTTPのステータスコードです）

APIKeyはサンドボックス環境だとこれ押したら出てくるやつですね↓

VBAで繋ぎます

そんなわけで、はい、繋ぎます。

早速ですが、VBAコードはこちら

Sub Test()
    '--------変数定義-------
    Dim httpReq As New XMLHTTP60
    Dim strURL As String               　　　　　　　　　　　　　　　 'RequestURL格納
    strURL = "https://sandbox.api.sap.com/XXXXXXXXX/Bank?$top=50"  'RequestURL
    Set httpReq = CreateObject("MSXML2.XMLHTTP")
    httpReq.Open "GET", strURL, False
    '------ヘッダ認証-------
    httpReq.setRequestHeader "APIKey", "APIKEYをセット"　　　　　　　　  '認証のAPIKeyを設
    '------実行-------
    httpReq.send    
    '-----結果確認-------
    Debug.Print httpReq.responseText
End Sub

XMLHTTP60のオブジェクトを作成し、パラメータを設定して、実行すれば完了です。
.setRequestHeaderでヘッダパラメータをセットできるので、他に必要な項目があった場合は同様に設定が可能です。
例えばPOSTの時にはトークンが必要になりますが、
その値も.setRequestHeaderに設定してあげることで接続が可能になります。
（httpReq.setRequestHeader "x-csrf-token", "トークンコード"で行けたはず...)

成功すればイミディエイトウィンドウに先ほどpostmanでも見ることのできた、Jsonっぽい結果が確認できますよ！

ただし、今のままだとただのテキスト状態なので、欲しいデータをパースしないといけません。

Jsonを分解するために、VBA-JSONを使う

今回は返り値がJsonフォーマットで返ってきているので、そのフォーマットを分解します。
（だんだんSAPとは関係なくなってきましたが）

Jsonをパースするには「VBA-JSON」をファイル内に組み込んで使いなさい、とのことですので素直にダウンロードします。
手順はこちらを参考にしました。

• VBA-JSONのGitHubのサイトよりzipをダウンロード
  
  解凍するとJsonConverter.basファイルがあるため、その中のJsonCoverterモジュールを自分の実行マクロ内にインポートします。
  ※これはファイル毎に行う必要があります。使わない方法があればいいのですけれど……

Jsonを分解

さて、実際にパースするには以下の様に記載します。先ほど使用したJsonConverterを使用するだけです。

    Dim jsonObj As Object
    Set jsonObj = JsonConverter.ParseJson(httpReq.responseText)

後は、先ほど取得したPostmanの結果などを参考にして、欲しい箇所を指定します。
例えば、↓の一つ目のBankInternalIDを取得したい場合は「jsonObj("value")(1)("BankInternalID")」の様に記載するだけです。

BANKの結果↓

{
    "@odata.context": "$metadata#Bank",
    "@odata.metadataEtag": "W/\"XXXXXXX"",
    "value": [
        {
            "BankCountry": "AXX",
            "BankInternalID": "20321",
            "BankName": "Allgemeine Sparkasse Oberösterreich - SAMPLE BANK",
（略）
        },
        {
            "BankCountry": "AU",
            "BankInternalID": "001-143",
            "BankName": "HSBC Bank - SAMPLE BANK",
            "Region": "",
（略）

なので、以下の様に指定すると

（略）
    '------パース------
    Dim jsonObj As Object
    Set jsonObj = JsonConverter.ParseJson(httpReq.responseText)
    
    ’valueの中の一つ目の要素
    Debug.Print "BankInternalID:" & jsonObj("value")(1)("BankInternalID")　
    Debug.Print "value:"; jsonObj("value")(1)("BankName")
　　’valueの中の二つ目の要素
    Debug.Print "BankInternalID:" & jsonObj("value")(2)("BankInternalID")
    Debug.Print "value:"; jsonObj("value")(2)("BankName")
（略）

結果はこのようになりました。

あとはVBAでループすれば一覧を表に格納できます。.countで要素を取得することも可能です
（今回はURIで50件指定しているので、50件だと決まってはいますが）。

以下がまとめのコードです。Odataで取得した結果をJsonConverterでパースして、ループでセットしています。

Sub Test()
    '--------変数定義-------
    Dim httpReq As New XMLHTTP60
    Dim strURL As String                'RequestURL格納
    strURL = "https://sandbox.api.sap.com/XXXXXXXXX/Bank?$top=50"
    Set httpReq = CreateObject("MSXML2.XMLHTTP")
    httpReq.Open "GET", strURL, False
    '------ヘッダ認証-------
    httpReq.setRequestHeader "APIKey", "XXAPIKEYXX"
    '------実行-------
    httpReq.send    
    '-----結果確認-------
    Debug.Print httpReq.responseText
    '------パース------
    Dim jsonObj As Object
    Set jsonObj = JsonConverter.ParseJson(httpReq.responseText)
    
    '取得結果をシートに記載
    Dim i As Long
    Dim sheet1 As Worksheet
    Set sheet1 = Worksheets(1)
    sheet1.Range("A:C").NumberFormatLocal = "@" '文字列指定
    
    For i = 1 To jsonObj("value").Count
        sheet1.Range("A" & i).Value = jsonObj("value")(i)("BankInternalID")
        sheet1.Range("B" & i).Value = jsonObj("value")(i)("BankName")
        sheet1.Range("C" & i).Value = jsonObj("value")(i)("Bank")
    Next

End Sub
    

結果はこんな感じ。

参考記事

https://qiita.com/haseshin/items/4acf31db9a672ed691df
https://vba-labo.rs-techdev.com/archives/1426
https://vba-labo.rs-techdev.com/archives/1406
https://outofmem.hatenablog.com/entry/2013/10/04/115600
https://dampgblog.hinohikari291.com/jsonparse/