Read Data from Json into Excel

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

Select allOpen in new window

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

That's a beautiful line of code right there

Glad that was helpful.

---

»bp

Thanks Bill