Sleeper: VBA Macros 438 runtime error

[supriya2309]

Public filepath As String
Public Sub ConvertJSON()
UserForm1.Show
MsgBox (filepath)
ActiveWorkbook.Queries.Add Name:="IMportJSON", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Json.Document(File.Contents(""" & filepath & """ ))," & Chr(13) & "" & Chr(10) & " #""Converted to Table"" = Record.ToTable(Source)" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Converted to Table"""
ActiveWorkbook.Worksheets.Add
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=ImportJSON;Extended Properties=""""" _




I guys very new to coding got an error 438 on the line which is in bold, please do help


Thanks and Regards,
Supriya

[Bob Phillips]

I couldn't see much wrong with that code, but I did change it to make it more readable.


MsgBox filepath
ActiveWorkbook.Queries.Add Name:="IMportJSON", Formula:= _
"let" & vbNewLine & _
" Source = Json.Document(File.Contents(""" & filepath & """))," & vbNewLine & _
" toTable = Record.ToTable(Source)" & vbNewLine & _
"in" & vbNewLine & _
" toTable"

But when I tried this with a Json doc I quickly knocked up it failed as the import created a list of 3 records, and it couldn't convert the list to a Record. I got around that with


totable = = Record.ToTable(Source{0})

but of course that only converted the first record.

I suggest you import that Json doc using Power Query and play around in there to get what you want, that will provide the query for you.