Feed data from an external database directly into an array

Question

I have a 4D database from which I need to periodically pull data using Excel and VBA. For now, the machine that I intend to run this on is similar to a Mac mini (Late 2014) on OS 10.13.6 and Excel for Mac 2011. I know, it is old, but it is what I have to work with for now.

Actually, I've been doing this for years now and it works fine to use a QueryTable if I only need to pull the data one time (or a small number of times). There is a REST interface on the database side. Now though, I need to query the database somewhere in the range of 1500 or 2000 times each time I run this macro. The results of each query have to be individually written to the spreadsheet and it runs very slowly. I think it would work much faster if I could append the results of the queries to an array, and just write the array to the spreadsheet in one block. However, QueryTables apparently must write the results to a specified range, so I'm forced to do ~2000 individual writes. It takes a little over 5 minutes each time and needs to be run a few times a day.

I can't just write it in SQL or the Mac equivalent of ADO, because I do not have the ability to change my source or its REST interface right now. I don't know SQL or ADO in any case, although I could probably learn them if it made the difference, but as I understand, it won't.

It does run considerably faster on an M1 Mac mini (about 1.5 minute run time), so the slowness can't be blamed on the network speed or the database server.

Just for fun, I compared writing a 2000 line array in one block versus writing it one line at a time. In my experiment, the block copy was at least 25 times faster.

Is there another VBA function that can be used to get data from an external source and append it to an array? Or can the functionality of QueryTables be built from scratch in VBA such that I can write it to an array instead of a range?

FWIW: Here is the sub I currently use to get data. It works, but is just slow for this application.

Public Sub REST_FillCellsWithDataFrom4D(strStartCell As String, _
                                        strServerIPAddress As String, _
                                        strServerPort As String, _
                                        strUserName As String, _
                                        strUserPassword As String, _
                                        strCallType As String, _
                                        strTableName As String, _
                                        strFieldNamesList As String, _
                                        strSearchFieldsList As String, _
                                        strSortFieldsList As String)
                                                                
    Dim strURL As String
    
    strURL = "URL;http://" & strServerIPAddress & ":" & strServerPort & "/" & strCallType
    strURL = strURL & "/table=" & strTableName
    strURL = strURL & "&fieldnames=" & strFieldNamesList
    strURL = strURL & "&searchfields=" & strSearchFieldsList
    strURL = strURL & "&sortfields=" & strSortFieldsList

    Worksheets("TablesAndFields").Range("A99") = strURL

    With ActiveSheet.QueryTables.Add(Connection:=strURL, Destination:=Range(strStartCell))
        .PostText = "user=" & strUserName & ";password=" & strUserPassword
        .RefreshStyle = xlOverwriteCells
        .SaveData = True
        .BackgroundQuery = False
        .Refresh
        .Delete
    End With
End Sub

Answer 1

I was able to put together a solution, and it is safe to say that for this operation, curl is considerably faster than QueryTable. Here is the heart of the code I used:

Public QuerySpecs As Range
Public QuerySheet As Worksheets, TargetSheet As Worksheets
Global recordsArray() As String
Global valuesArray() As Variant
Private Declare Function web_popen Lib "libc.dylib" Alias "popen" (ByVal command As String, ByVal mode As String) As Long
Private Declare Function web_pclose Lib "libc.dylib" Alias "pclose" (ByVal file As Long) As Long
Private Declare Function web_fread Lib "libc.dylib" Alias "fread" (ByVal outStr As String, ByVal size As Long, ByVal items As Long, ByVal stream As Long) As Long
Private Declare Function web_feof Lib "libc.dylib" Alias "feof" (ByVal file As Long) As Long

Function getHTTP(sUrl As String, sUsername As String, sPassword As String, numReportColumns As Long) As String
    Dim web_Command As String
    Dim web_File As Long
    Dim web_Chunk As String
    Dim web_Read As Long
    Dim Counter As Long
    Dim i As Long, j As Long
    Dim pasteRange As Range
    
    'Construct the curl command with username and password
    web_Command = "curl -d 'user=" & sUsername & ";password=" & sPassword & "' '" & sUrl & "'"
    
    web_File = web_popen(web_Command, "r")
    
    If web_File = 0 Then
        Exit Function
    End If
    
    Do While web_feof(web_File) = 0
        web_Chunk = Space$(50)
        web_Read = web_fread(web_Chunk, 1, Len(web_Chunk) - 1, web_File)
        If web_Read > 0 Then
            web_Chunk = Left$(web_Chunk, web_Read)
            getHTTP = getHTTP & web_Chunk
        End If
    Loop
    
    web_pclose (web_File)
    
    'Split the string into an array of records using the carriage return as the delimiter
    recordsArray = Split(getHTTP, vbCr)
    
    'Resize the ValuesArray to match the number of records and values
    ReDim valuesArray(1 To UBound(recordsArray) + 1, 1 To numReportColumns)
    
    'Iterate over the records and split each record into values using the tab character as the delimiter
    For i = LBound(recordsArray) To UBound(recordsArray)
        Dim values() As String
        values = Split(recordsArray(i), vbTab)
        For j = LBound(values) To UBound(values)
            valuesArray(i + 1, j + 1) = values(j)
        Next j
    Next i

End Function

My database REST interface is just a good fake of a REST interface, so I'm sure someone else's curl command would need to be significantly different. But it can definitely be done. Thanks for the help!