MacOS - download text from URL where destination is a string (not Range in sheet)

[hilss]

Hello,
I'm a Mac user (currently on macOS Catalina Version 10.15.3). I'm using MS Excel for Mac (Version 16.34).

In my VBA code, I'm using the QueryTables.Add method to download data from a site. I pass in the connection (URL), and the destination (Range("A1")).
The text (JSON) has numerous lines. So it gets pasted in cells A1 through A19. In addition, it seems to be missing some characters (like double quotes, etc.).

Instead of setting the destination to cell A1, can I set it to be a string object? Or can I save it to a text file locally?

Sub DownloadData()

    Dim jsonText As String
    Dim jsonObject As Object

    Dim destSheet As Worksheet
    Set destSheet = Application.Worksheets("DataSheet")

    destSheet.Rows.ClearContents
    destSheet.Activate
    With destSheet.QueryTables.Add(Connection:= _
        "URL;I_removed_the_URL_from_here_bcause_I_am_not_sure_about_the_mrExcels_policies" _
        , Destination:=Range("$A$1"))
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = False
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With

    i = 0
    Do Until Len(destSheet.Range("A1").Offset(i, 0)) < 1
        jsonText = jsonText + destSheet.Range("A1").Offset(i, 0)
        i = i + 1
    Loop

    destSheet.Rows.ClearContents
' because the data is missing " in certain spots, the parser fails.
    Set jsonObject = JsonConverter.ParseJson(jsonText)
' I will do more stuff with the jsonObject here....

End Sub

 

[hilss]

found a solution here:

HTTP Get Request Using VBA in OSX Excel

I'm writing a very simple macro that needs to make an HTTP GET request to a server and the response is not important (it initiates a process on the server). The HTTP GET does NOT require authentica...

stackoverflow.com

is there a way to mark this as "solved" ? Thanks