Help us understand the problem. What is going on with this article?

VBA で Web API を呼び出す

VBA で Web API を呼び出す

by MakotoIshikawa
1 / 5

準備

VBA-JSON のインポート

  1. VBA-JSON の最新版 (.zip) をダウンロードする。
  2. ダウンロードしたファイルを解凍する。
  3. 解凍したフォルダを任意の場所に配置する。
  4. Excel を起動し、VBA の開発ツールを開く。
  5. [ファイル(F)] > [ファイルのインポート(I)] を選択し、解凍したフォルダ内にある「JsonConverter.bas」を選択しインポートします。
  6. [ツール(T)] > [参照設定(R)] から「Microsoft Scripting Runtime」にチェックを入れます。
    • チェックすることで、Dictionary 型を使用できるようになります。

VBA コード

Web API を呼び出すメソッド

Web API を呼び出すメソッドのサンプルです。

Public Function KickWebApiOfJson(ByVal request As String, ByVal url As String, Optional ByVal param As Object) As Object
    Dim json
    json = ConvertToJson(param)

    Dim http As Object
    Set http = CreateObject("MSXML2.XMLHTTP")
    With http
        .Open request, url, False
        .SetRequestHeader "Content-Type", "application/json; charset=UTF-8"
        .send json

        If .ResponseText <> "" Then
            Set KickWebApiOfJson = ParseJson(.ResponseText)
        End If
    End With
End Function

メソッドの呼び出し方

GET と POST の呼び出し方のサンプルです。

Sub OnClick_GetAllButton()
    Dim res As Collection
    Set res = KickWebApiOfJson("GET", "http://localhost/api/Values/")

    Cells(1, 7) = "ID"
    Cells(1, 8) = "値"
    Cells(1, 9) = "コメント"

    Dim i As Integer
    i = 2
    Dim row As Variant
    For Each row In res
        Cells(i, 7) = row("id")
        Cells(i, 8) = row("value")
        Cells(i, 9) = row("comment")
        i = i + 1
    Next row
End Sub

Sub OnClick_GetButton()
    Dim id As Integer
    id = Range("C2").Value
    Dim res As Dictionary
    Set res = KickWebApiOfJson("GET", "http://localhost/api/Values/" & id)

    Range("C3").Value = res("id")
    Range("C4").Value = res("value")
    Range("C5").Value = res("comment")
End Sub

Sub OnClick_PostButton()
    Dim param As New Dictionary
    param("id") = Range("C3").Value
    param("value") = Range("C4").Value
    param("comment") = Range("C5").Value

    Call KickWebApiOfJson("POST", "http://localhost/api/Values", param)
End Sub

参考サイト

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away