Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
39
Help us understand the problem. What is going on with this article?
@MakotoIshikawa

VBA で Web API を呼び出す

More than 1 year has passed since last update.

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

参考サイト

39
Help us understand the problem. What is going on with this article?
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

No comments
Sign up for free and join this conversation.
Sign Up
If you already have a Qiita account Login
39
Help us understand the problem. What is going on with this article?