Consulting

Results 1 to 2 of 2

Thread: Sleeper: VBA Macros 438 runtime error

  1. #1

    Sleeper: VBA Macros 438 runtime error

    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

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •