Run Time type mismatch

[danf11]

Tim,

I am getting a type mismatch when parsing a straightforward json, not sure why. I am just trying to extract the volume field out of the json

Sub getData() Application.DisplayAlerts = False Application.ScreenUpdating = False

Dim wb As Workbook Dim ws As Worksheet Dim rng As Range Dim symbol As String Dim n As Integer Dim lastrow As Long

Set wb = ActiveWorkbook Set ws = Sheets("Sheet1") ws.Activate

'Last row find lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row

Set rng = ws.Range("A4:A" & lastrow)

'Clear Prior Prices ws.Range("B4:B" & lastrow).ClearContents

n = 4 'Get Symbols list For Each x In rng

symbol = x

Set myrequest = CreateObject("WinHttp.WinHttpRequest.5.1")
myrequest.Open "Get", "https://www.alphavantage.co/query?function=GLOBAL_QUOTE&symbol=" & symbol & "&apikey=RYDLM2TSPACNQFJK"
myrequest.Send

MsgBox (myrequest.ResponseText)

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

MsgBox Json(1)("06. volume")

    'i = Json("Global Quote")(1)("06. volume")
 '   ws.Range(Cells(n, 2), Cells(n, 2)) = i

n = n + 1

Next x

ws.Columns("B").AutoFit
MsgBox ("Data is downloaded.")

ws.Range("B4:B" & lastrow).HorizontalAlignment = xlGeneral
'ws.Range("B4:B" & lastrow).NumberFormat = "$#,##0.00"

Application.DisplayAlerts = True Application.ScreenUpdating = True

End Sub

Thanks,

Dan

[rawmud]

Was this solved?

I am running into a similar issue. Below is the code I am using. Basically, I am trying to create a message box that outputs the Low for IBM on 09/17/2020. Nothing is output, it actually errors out and says and cannot fill in the t1 variable. I can't see what is wrong with it.

symb = "IBM" u = "https://www.alphavantage.co/query?function=TIME_SERIES_DAILY&symbol=" & symb & "&outputsize=compact&apikey=RYDLM2TSPACNQFJK"

myrequest.Open "Get", u
myrequest.Send
status1 = myrequest.Status
Set Json = JsonConverter.ParseJson(myrequest.ResponseText)

t1 = Json("Time Series (Daily)")("2020-09-17")("3. low")

MsgBox (t1)

[rawmud]

I was able to get the code to work. In the references to symb, the symbols had an extra space... "IBM " which was throwing it off.