title: Efficient Data Transfer from Excel VBA to Web-Service
body: <p>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 </p>
  
  <pre><code>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
  </code></pre>
  
  <p>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 <code>myRange[x, y]</code> 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 (<a href="https://stackoverflow.com/questions/52390267/workaround-for-memory-leak-when-using-large-string">Workaround for Memory Leak when using large string</a>) 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. </p>
  
  <p>What could be another idea to deal with the data array?</p>