Support > Wagby Developer Network(R7) > REST API usage guide > [Application example] Operating Wagby from Excel via REST API [1] Basic form
ja | en

Here is an example of manipulating Wagby from Excel via REST API. R7.5

Customer model

Here, we will explain the simple customer model shown in Fig. 1 as an example.

Figure 1 Definition of customer model

After building, start the application.As sample data, two items as shown in Fig. 2 are registered in advance.

Figure 2 Sample data

Start Excel

Open the Excel file that contains the program (VBA) that uses the REST API. At this time, make sure that the Wagby application is running.This is for HTTP communication from Excel.

Figure 3 Starting the sample application

Enter the customer ID ("1000" here) and press the "Get data" button.

Fig. 4 Specifying customer ID to acquire data

I got the data of Wagby.(FIG. 5)

Figure 5 Data was acquired from Wagby and displayed

Changing the customer ID also changes the acquisition data.(FIG. 6)

Fig. 6 Acquire different data

The sample application is written in Excel VBA.I will check its contents. I will explain it using Excel 2007.

Open "Macro".

Figure 7 Opening a Macro

Edit the macro name "sample".

Figure 8 Editing a macro

The code is displayed.

Figure 9 Code displayed

Code description

We will reprint the VBA code used in this application.

Sub sample()
  '--------------------------------------------------------------------
  '  変数定義
  '--------------------------------------------------------------------
  Dim objHttpReq As Object          ' XMLHTTP オブジェクト
  Dim strJSON As String             ' レスポンスで受け取るJSONデータ
  Dim strURL As String              ' アクセス先URL
  Dim objJSON As Object             ' レスポンスの JSON 文字列をパースした情報を格納

  '------------------------------------------------------------------
  ' 検索文字付きURLを作成する
  '------------------------------------------------------------------
  strURL = "http://localhost:8921/wagby/rest/customer/entry/" & Range("C2").value

  '------------------------------------------------------------------
  ' XMLHTTP オブジェクトを生成する
  '------------------------------------------------------------------
  Set objHttpReq = CreateObject("MSXML2.XMLHTTP")
  objHttpReq.Open "GET", strURL, False
  '------------------------------------------------------------------
  ' XMLHTTP のリクエストヘッダーを指定する
  '------------------------------------------------------------------
  ' ログイン認証
  objHttpReq.setRequestHeader "X-Wagby-Authorization", "YWRtaW46YWRtaW4="
  'ドメイン名:ポート番号
  objHttpReq.setRequestHeader "Host", "localhost" + ":8921"
  'キャッシュ対策(常にレスポンスが取得できる状態にする)
  objHttpReq.setRequestHeader "If-Modified-Since", "Thu, 01 Jun 1970 00:00:00 GMT"

  '------------------------------------------------------------------
  ' リクエストを送信する
  '------------------------------------------------------------------
  objHttpReq.send (Null)

  '------------------------------------------------------------------
  ' レスポンスを取得する
  '------------------------------------------------------------------
  'レスポンス情報を変数に格納する
  strJSON = objHttpReq.responseText
  'MsgBox (strJSON)
    'レスポンスで取得したJSONをパース
    Set objJSON = parseJSON(strJSON)
    
  If IsNull(objJSON.Item("entity")) Then
    Range("C4").value = "No data."
    Range("C5").ClearContents
    Range("C6").ClearContents
    Range("C7").ClearContents
  Else
    'Call DumpJSONObj(objJSON)

    '取得したレコードからフィールドの値を取得
    Dim entity As Object
    Set entity = objJSON.Item("entity")
    Debug.Print entity.Item("customerid_") & vbCrLf;
    Debug.Print entity.Item("customername_") & vbCrLf;
    Debug.Print entity.Item("customerkana_") & vbCrLf;
    Debug.Print entity.Item("companyname_") & vbCrLf;
    Debug.Print entity.Item("tel_") & vbCrLf;

    Range("C4").value = entity.Item("customername_")
    Range("C5").value = entity.Item("customerkana_")
    Range("C6").value = entity.Item("companyname_")
    Range("C7").value = entity.Item("tel_")
  End If
End Sub

Sub clear()
  Range("C2").ClearContents
  Range("C4").ClearContents
  Range("C5").ClearContents
  Range("C6").ClearContents
  Range("C7").ClearContents
  Range("C2").Activate
End Sub

'--------------------------------------
'JSONをパースしたオブジェクトを生成
'--------------------------------------
Public Function parseJSON(strJSON As String) As Object
  Dim lib As New JSONLib 'Instantiate JSON class object
  '------------------------------------------------------------------
  ' パースした内容を objJSON 変数に格納する
  '------------------------------------------------------------------
  Set parseJSON = lib.parse(CStr(strJSON))
End Function

Private Sub DumpJSONObj(ByRef json As Variant)
    If TypeName(json) = "Dictionary" Then
        Call DumpDictionaly(json)
    ElseIf TypeName(json) = "Collection" Then
        Call DumpCollection(json)
    Else
        Debug.Print "value:" & json
    End If
End Sub

Public Sub DumpCollection(ByRef dic As Variant)
    Dim keys
    Dim i
    For i = 0 To dic.Count - 1
        Debug.Print "ArrayNo:" & i
        Call DumpJSONObj(dic.Item(i + 1))
    Next i
End Sub

Public Sub DumpDictionaly(ByRef dic As Variant)
    Dim keys
    Dim i
    keys = dic.keys
    For i = 0 To dic.Count - 1
        Dim key
        key = keys(i)
        Debug.Print "key:" & key
        Call DumpJSONObj(dic.Item(key))
    Next i
End Sub
  • In the strURL variable of line 13, write the destination URL of the target Wagby application.It is assumed here that it was started on the same machine.
  • Assume that the "C2" cell stores "customer ID".This is because the REST API uses the primary key to operate it.
  • Logon information is prepared on line 24.Information that is encoded with BASE 64 is set up as a name of X-Wagby-Authorization with a pair of logon ID and password ("admin: admin" in this example).
  • Do not forget to take measures against caching on line 28.Always acquire the latest information.
  • Connect to Wagby on line 33.At this time, within Wagby, logon processing and processing of REST API (return of one data here) is done.
  • On line 39 we get the return value as a JSON string.Line 40 is for debugging.If you uncomment comments, you can check the actual JSON string in the message box.
  • Line 42 converts the JSON string to an object.After that, the value contained in the object is embedded in the cell.
  • Line 80 describes the parsing process to convert the JSON string to an object.Actually I use the function of library called JSONLib.
  • By enabling the DumpJSONObj function on line 50, you can check the contents of the JSON object.

The point is to use a library called JSONLib.JSONLib is provided in open source.
https://code.google.com/p/vba-json/

You can download the complete repository, sample data, sample application (Excel file) introduced here.

In order to use JSONLib, you need to import the jsonlib class module into the target Excel. Since the Excel file that can be downloaded on this page has already been imported, we recommend you develop it by editing it.

"REST API utilization guide> logon/logoff> automatic logon> SecurityPlease read.

In the following part of VBA, you may encounter "user defined type is not defined" error.

Dim objHttpReq As MSXML2.XMLHTTP   ' XMLHTTP オブジェクト
次のように修正してお試しください。
Dim objHttpReq As Object   ' XMLHTTP オブジェクト

You can download the file that made this fix.