ExcelVBAでOdata接続（SAP）

※この記事は [chillSAP 夏の自由研究2022](https://note.com/chillsap/n/n62740563aee0)の記事として執筆しています。

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


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

また、URIの構成などを知りたいときは@tamiさんの↓の記事が細かくて助かっています。
[【SAPUI5】OData(1) ODataとは](https://qiita.com/tami/items/411a226d1ea6bb25b5f1)

# excelの準備

 - ExcelのVBAを起動、ツール-参照設定より以下のライブラリ参照をチェックします。
　　Microsoft Scripting Runtime　…　後で使うJsonをパースするために使用します。
 　　Microsoft XML, v6.0　…　MSXML2.XMLHTTPを使ってOdataをコールするために使用します。
        ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/146086/b002c5fe-e1d5-7ef7-18b9-46dff076b0f8.png)

# SAP API Business Hubから使うOdataを探す
  使ったのはおなじみ[SAP API Business Hub](https://api.sap.com/)より、S4HCのodata(v4)”BANK”RequestURLを使いました。なんでもよし。
（今回は取得するほう、”GET”のOdataを使います。こちらから送信するほうはPOSTですね）
TryOutタブに移動して、そのまま「RUN」アイコンクリックすれば実行結果が出ます。
そのときに一緒にRequestURLも表示されているため、こちらをとっておいてください。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/146086/6d612d83-1f66-bcf3-258c-eab01b793ee7.png)


# せっかくなのでPostmanで繋ぎ
その前にPostmanでも接続確認。なんとなく同じようなものがとれました。GETの設定は簡単です。
①requestURLを貼り付ける
②Authorizationタブの認証設定のところに設定項目を入力
　※ここはただのBasic認証を入れることが多いんですが、Sandbox環境はAPIKeyで接続するので以下設定してください。
    　　Authorization Type：APIKey
    　　Key:APIKey
    　　Value:BusinessHUBで確認した各自のAPIKey
③Sendボタン押下。
④の箇所に結果が出ています。Status：200 OKとでたら接続は成功しています(HTTPのステータスコードです）
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/146086/3bcbf918-c875-e5a7-90d6-f22dafd3c3e1.png)


APIKeyはサンドボックス環境だとこれ押したら出てくるやつですね↓
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/146086/4819c913-61a3-bd74-e6f9-0abbcfa46d14.png)


# 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っぽい結果が確認できますよ！
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/146086/fd4e094c-e60a-3ef2-472d-9e8e5fe73316.png)

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

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

Jsonをパースするには「VBA-JSON」をファイル内に組み込んで使いなさい、とのことですので素直にダウンロードします。
手順は[こちら](https://vba-labo.rs-techdev.com/archives/1401)を参考にしました。
- [VBA-JSON](https://github.com/VBA-tools/VBA-JSON)のGitHubのサイトよりzipをダウンロード
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/146086/97bc8ca4-4a0d-8948-a1f4-686936c412bc.png)
解凍すると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")
（略）
```
結果はこのようになりました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/146086/64646f4b-9307-0e19-5a8e-4f13f3ec17d5.png)

あとは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
    
```
結果はこんな感じ。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/146086/b53918f2-2df4-6442-d804-05158413db0c.png)



# 参考記事
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/