Results 1 to 4 of 4

Thread: Outputting a JSON response to sheets.

  1. #1
    New Member
    Join Date
    Dec 2018
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Outputting a JSON response to sheets.

    Hi, I am using VBA-Web to pull in some data in a json format via REST.
    Which works fine using

    Code:
    Public Sub PullStaff()Dim Client As New WebClient
    Dim Request As New WebRequest
    Dim RequestResponse As WebResponse
    
    
     WebHelpers.EnableLogging = True
     
    Client.BaseUrl = "https://web.site/webservice/rest/server.php"
    
    Request.Method = WebMethod.HttpGet
    Request.ResponseFormat = WebFormat.Json
    Request.AddQuerystringParam "wsfunction", "core_enrol_get_enrolled_users"
    Request.AddQuerystringParam "moodlewsrestformat", "json"
    Request.AddQuerystringParam "courseid", "1234"
    Request.AddQuerystringParam "wstoken", "111111111111111"
    Request.AddHeader "Authorization", "Token ..."
    
    
    Request.AddQuerystringParam "options[0][name]", "userfields"
    Request.AddQuerystringParam "options[0][value]", "id"
    Request.AddQuerystringParam "options[1][name]", "userfields"
    Request.AddQuerystringParam "options[1][value]", "fullname"
    Request.AddQuerystringParam "options[2][name]", "userfields"
    Request.AddQuerystringParam "options[2][value]", "email"
    Request.AddQuerystringParam "options[3][name]", "userfields"
    Request.AddQuerystringParam "options[3][value]", "username"
    
    
    Set RequestResponse = Client.Execute(Request)
    'Debug.Print RequestResponse.Content
    End Sub
    I get back a response which I can see in the immediate window such as

    Code:
    <-- Response - 10:30:22
    200 OK
    Cache-Control: private, must-revalidate, pre-check=0, post-check=0, max-age=0
    Connection: close
    Date: Wed, 05 Dec 2018 10:30:22 GMT
    Pragma: no-cache
    Content-Length: 874
    Content-Type: application/json
    Expires: Thu, 01 Jan 1970 00:00:00 GMT
    Accept-Ranges: none
    Server: Apache/2.2.15 (CentOS)
    X-Powered-By: PHP/7.1.11
    Access-Control-Allow-Origin: *
    
    
    [{"id":1234,"username":"abc","fullname":"B Smoke","email":"smoke@website.com"},{"id":1235,"username":"def","fullname":"D Blain","email":"blain@website.com"},
    I am now trying to get this into a sheet so I can do some work on it but I have hit a brick wall with this.
    I tried

    Code:
    Dim i As Integer
    i = 4
    For Each item In RequestResponse
        Sheets(1).Cells(i, 1).Value = item("id")
        Sheets(1).Cells(i, 2).Value = item("fullname")
        Sheets(1).Cells(i, 3).Value = item("email")
        Sheets(1).Cells(i, 4).Value = item("username")
        i = i + 1
    Next
    Based off another forum thread I found but its throwing the error "Variable not defined" on
    Code:
    For Each item In RequestResponse
    I am not sure what I should define item as to get this working, or if this even will work? am I totally off base with what I am trying to achieve?

    Thanks for any advice on getting this working.

  2. #2
    Board Regular
    Join Date
    Oct 2007
    Posts
    5,862
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Outputting a JSON response to sheets.

    You seem to be using https://github.com/VBA-tools/VBA-Web. I've never used VBA-Web, but looking at the GetJSON example it looks like you should be using RequestResponse.Data, which is the data structure consisting of Dictionaries and Collections of the parsed json string. Therefore try this:
    Code:
    Dim i As Integer, item As Object
    i = 4
    For Each item In RequestResponse.Data
        Sheets(1).Cells(i, 1).Value = item("id")
        Sheets(1).Cells(i, 2).Value = item("fullname")
        Sheets(1).Cells(i, 3).Value = item("email")
        Sheets(1).Cells(i, 4).Value = item("username")
        i = i + 1
    Next

  3. #3
    New Member
    Join Date
    Dec 2018
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Outputting a JSON response to sheets.

    Quote Originally Posted by John_w View Post
    You seem to be using https://github.com/VBA-tools/VBA-Web. I've never used VBA-Web, but looking at the GetJSON example it looks like you should be using RequestResponse.Data, which is the data structure consisting of Dictionaries and Collections of the parsed json string. Therefore try this:
    Code:
    Dim i As Integer, item As Object
    i = 4
    For Each item In RequestResponse.Data
        Sheets(1).Cells(i, 1).Value = item("id")
        Sheets(1).Cells(i, 2).Value = item("fullname")
        Sheets(1).Cells(i, 3).Value = item("email")
        Sheets(1).Cells(i, 4).Value = item("username")
        i = i + 1
    Next
    Thanks I got it working, just to confirm what is the benefit of putting i = i + 1 at the end rather than the beginning of the loop?
    This is the code I got working from yesterday
    Code:
    For Each item In RequestResponse.Data
        i = i + 1 ' i is an integer here
        Sheets(3).Cells(i, 1).Value = item("id")
        Sheets(3).Cells(i, 2).Value = item("fullname")
        Sheets(3).Cells(i, 3).Value = item("email")
        Sheets(3).Cells(i, 4).Value = item("username")
    
    
    Next

  4. #4
    Board Regular
    Join Date
    Oct 2007
    Posts
    5,862
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Outputting a JSON response to sheets.

    There is no benefit or disadvantage. Where you put i = i + 1 depends on its initial value before the loop and which row number the data is written to first. Incrementing i at the start of the loop means the first row is 5, whereas incrementing it at the end means the first row is 4.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •