Posting huge datasets to a webservice from excel VBA

Question

Hello fellow Excel vba coders

I have this great macro in my excel sheet, where i compile XML based on the rows the user puts in - after this it post the xml to a webservice.

You can have a look at my code below - It is fairly simple:

Set XMLHttpRequest = New MSXML2.XMLHTTP    
With XMLHttpRequest
    .Open "POST", URL, False
    .setRequestHeader "Content-Type", "text/xml; encoding='utf-8'"
    .setRequestHeader "Content-Length", strLength
    .send strXML
End With

Right now it works great when there is less than 200 rows, yet it times out when the row number gets above 1000 rows. The string of XML I post is really big, and i'm quite sure that's the reason it times out.

Now my problem is, how do i post this huge dataset, that exceed 1.000 rows, maybe even above 20.000 rows, to a webservice?

So far i have spend a lot of time to look for a possible solution around the web, but have yet to find a way to handle this. So far i have the following ideas to solve the problem:

• Copy the sheet to a new workbook, take the location of the new workbook and convert the file to a Base64 string and post the entire file to a new .asmx service and handle the "workbook" in C# code.
• Convert the huge string to some kind of byte array and post that to a new .asmx webservice and handle the C# code.

I really hope one of you guys can point me in the right direction and help me solve this problem?

Answer 1

Instead of sending the entire sheet as a single object, send each row individually.

This will require your web service to be modified to accept just a row, rather than, what I would guess is currently being sent as an array of rows.

This would allow you to process any number of rows as the most you are processing at any one time is exactly one.