Efficient Data Transfer from Excel VBA to Web-Service

Question

I have a large worksheet (~250K rows, 22 columns, ~40MB plain data) which has to transfer its content to an intranet API. Format does not matter. The problem is: When accessing the data like

Const ROWS = 250000
Const COLS = 22

Dim x As Long, y As Long
Dim myRange As Variant
Dim dummyString As String
Dim sb As New cStringBuilder

myRange = Range(Cells(1, 1), Cells(ROWS, COLS)).Value2

For x = 1 To ROWS
    For y = 1 To COLS
        dummyString = myRange(x, y) 'Runtime with only this line: 1.8s
        sb.Append dummyString 'Runtime with this additional line 163s
    Next
Next

I get a wonderful 2D array, but I am not able to collect the data efficiently for HTTP export. An X/Y loop over the array and access myRange[x, y] has runtimes >1min. I was not able to find an array method which helps to get the imploded/encoded content of the 2D array. My current workaround is missusing the clipboard (Workaround for Memory Leak when using large string) which works fast, but is a dirty workaround in my eyes AND has one major problem: The values I get are formatted, “.Value” and not “.Value2”, so I have to convert the data on server site again before usage, e.g. unformat currency cells to floats.

What could be another idea to deal with the data array?

Answer 1

My thoughts are that you create two string arrays A and B. A can be of size 1 to ROWS, B can be of size of 1 to COLUMNS. As you loop over each row in your myRange array, fill each element in B with each column's value in that row. After the final column for that row and before you move to the next row, join array B and assign to the row in A. With a loop of this size, only put necessary stuff inside the loop itself. At the end you would join A. You might need to use cstr() when assigning items to B.

Matschek (OP) was able to write the code based on the above, but for anyone else's benefit, the code itself might be something like:

Option Explicit

Private Sub concatenateArrayValues()

    Const TOTAL_ROWS As Long = 250000
    Const TOTAL_COLUMNS As Long = 22

    Dim inputValues As Variant
    inputValues = ThisWorkbook.Worksheets("Sheet1").Range("A1").Resize(TOTAL_ROWS, TOTAL_COLUMNS).Value2

    ' These are static string arrays, as OP's use case involved constants.
    Dim outputArray(1 To TOTAL_ROWS) As String ' <- in other words, array A
    Dim interimArray(1 To TOTAL_COLUMNS) As String ' <- in other words, array B

    Dim rowIndex As Long
    Dim columnIndex As Long

    ' We use constants below when specifying the loop's limits instead of Lbound() and Ubound()
    ' as OP's use case involved constants.
    ' If we were using dynamic arrays, we could call Ubound(inputValues,2) once outside of the loop
    ' And assign the result to a Long type variable
    ' To avoid calling Ubound() 250k times within the loop itself.

    For rowIndex = 1 To TOTAL_ROWS
        For columnIndex = 1 To TOTAL_COLUMNS
            interimArray(columnIndex) = inputValues(rowIndex, columnIndex)
        Next columnIndex
        outputArray(rowIndex) = VBA.Strings.Join(interimArray, ",")
    Next rowIndex

    Dim concatenatedOutput As String
    concatenatedOutput = VBA.Strings.Join(outputArray, vbNewLine)

    Debug.Print concatenatedOutput

    ' My current machine isn't particularly great
    ' but the code above ran and concatenated values in range A1:V250000
    ' (with each cell containing a random 3-character string) in under 4 seconds.

End Sub