1

Hi i have a Json response at a webpage that i am trying to capture update the existing spreadsheet with that data. I have referenced using the following VBScript. I am getting a blank excel and somehow the spreadsheet is not receiving the json data

JSON Response at the webpage

[
    {
        "Sno ": "1",
        "Name": "Alex",
        "Age ": "27",
        "City": "Newyork"
    },
    {
        "Sno ": "2",
        "Name": "Smith",
        "Age ": "25",
        "City": "Los angeles"
    },
    {
        "Sno ": "3",
        "Name": "austin",
        "Age ": "26",
        "City": "Calfornia"
    }
]

VBScript being used (referrenced it from stack source link)

Sub Button4_Click()    
    'clearing the contents of the sheet prior to synchronisation of data with GCP
    Dim str As String
     Dim myarray() As Variant
    'Delete existing data
    Sheets("Backup_sheet").Activate 'Name of sheet the data will be downloaded into. Change as required.
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents

    Url = "http://127.0.0.1:5555/refresh"
    Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
    objHTTP.Open "GET", Url, False, "john", "hello"
    objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
    objHTTP.setRequestHeader "Content-Type", "application/json"

    QueryQuote:
                With Sheets("Backup_sheet").QueryTables.Add(Connection:="Url;" & str, Destination:=Sheets("Backup_sheet").Range("a1"))
                    .BackgroundQuery = True
                    .TablesOnlyFromHTML = False
                    .Refresh BackgroundQuery:=False
                    .SaveData = True
                End With
    Sheets("Backup_sheet").Range("a1").CurrentRegion.TextToColumns Destination:=Sheets("Backup_sheet").Range("a1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=True, Space:=False, other:=True, OtherChar:=",", FieldInfo:=Array(1, 2)

    Sheets("Backup_sheet").Columns("A:B").ColumnWidth = 12
    Range("A1").Select
End Sub

Expected output post fetching data

enter image description here

Can someone help where i am getting it wrong/ any suggestions how to achieve this would be helpful. Thank you.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
prasanna kumar
  • 283
  • 2
  • 11
  • Where is your authorization header? – Mech Mar 18 '20 at 14:59
  • 2
    You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – Pᴇʜ Mar 18 '20 at 14:59
  • 1
    You set all these parameters for `objHTTP` but you never send it with `objHTTP.Send`. So I assume it does nothing at all. – Pᴇʜ Mar 18 '20 at 15:03
  • 1
    Hi @Mech in am very new to Vb script so not familiar with the ask. Can you guide me any resources or how to use regarding Authorization header – prasanna kumar Mar 18 '20 at 15:06
  • 1
    Since this is a referrenced code I may not able to get back correctly. Yes I want to send read the json data using the HTTP request object and update that data to spreadsheet. Any guidance on what to learn to correct this script would be helpful – prasanna kumar Mar 18 '20 at 15:12
  • @prasannakumar I think you mixed up 2 different things. One approach is using `MSXML2.ServerXMLHTTP` and request the JSON that you then need to parse te get the data (see [Parsing JSON in Excel VBA](https://stackoverflow.com/questions/6627652/parsing-json-in-excel-vba)) • And another approach is using `QueryTables`. Also see [this answer](https://stackoverflow.com/a/15164315/3219613). – Pᴇʜ Mar 18 '20 at 15:12
  • Could also be helpful: [Handle JSON Object in XMLHttp response in Excel VBA Code](https://stackoverflow.com/questions/16817545/handle-json-object-in-xmlhttp-response-in-excel-vba-code) – Pᴇʜ Mar 18 '20 at 15:13
  • Where your developing locally (http://127.0.0.1), it's probably not the issue now but very well may cause an issue for you in the future. I misspoke when I said "authorization". I meant to reference "authentication". This is a decent read: https://stackoverflow.com/questions/33505130/how-to-assign-basic-authentication-header-to-xmlhttprequest. *Please* note that Base64 is not secure. – Mech Mar 18 '20 at 15:14
  • 1
    Thanks @Mech for pointing out but it was used just for testing on a local machine as the json response is getting created using a flask application written in python. – prasanna kumar Mar 18 '20 at 15:20

0 Answers0