Avatar of Ruffone
Ruffone
 asked on

Read Data from Json into Excel

I am trying to read data into an Excel sheet with the code below but I get a type mismatch error. I get the data as confirm with the message box

    Set myrequest = CreateObject("WinHttp.WinHttpRequest.5.1")
    myrequest.Open "Get", "https://api.iextrading.com/1.0/stock/aapl/chart/dynamic"
    myrequest.Send

    Dim Json As Object
    Set Json = JsonConverter.ParseJson(myrequest.ResponseText)
    
    'MsgBox (myrequest.ResponseText)

        i = Json(1)("marketClose") <---- Error
        ws.Range(Cells(n, 2), Cells(n, 2)) = i

Open in new window

Visual Basic.NETVB ScriptVBA

Avatar of undefined
Last Comment
Ruffone

8/22/2022 - Mon
Rikin Shah

Hi,

Check following code snippet to create an excel file from JSON-

    Using clientjs = New HttpClient()
        Dim responseJSON As HttpResponseMessage = clientjs.GetAsync(url).Result
        responseJSON.EnsureSuccessStatusCode()
        Dim responseBody = responseJSON.Content.ReadAsStringAsync().Result
        Dim data = JsonConvert.DeserializeObject(responseBody)("value")

        Using client = New HttpClient()

            Using formData = New MultipartFormDataContent()
                Dim fileFormat = "xlsx"
                formData.Add(New StringContent("Test"), "FileName")
                formData.Add(New StringContent(fileFormat), "FileFormat")
                formData.Add(New StringContent(JsonConvert.SerializeObject(data)), "Data")
                Dim response = client.PostAsync(webapiurl, formData).Result

                If Not response.IsSuccessStatusCode Then
                    MessageBox.Show("Invalid response.")
                    Return
                End If

                Dim tempPath = Path.Combine(Path.GetTempPath(), Guid.NewGuid().ToString())

                If Not Directory.Exists(tempPath) Then
                    Directory.CreateDirectory(tempPath)
                End If

                Dim tempFilePath = Path.Combine(tempPath, String.Format("{0}.{1}", "Test", fileFormat))

                Using newFile = File.Create(tempFilePath)
                    response.Content.ReadAsStreamAsync().Result.CopyTo(newFile)
                End Using

                Process.Start(tempFilePath)
            End Using
        End Using
    End Using

Open in new window

Ruffone

ASKER
Thanks Rikin,

I just looked at the code in Tim Hall's JsonConverter for the first time to see if it had SerializeObject and it doesn't. I am just trying to use a little VBA on the back of Excel
ASKER CERTIFIED SOLUTION
Bill Prew

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Ruffone

ASKER
That's a beautiful line of code right there
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Bill Prew

Glad that was helpful.


»bp
Ruffone

ASKER
Thanks Bill