Data from WebService to VBA

[omar_85]

Hi all,

I am struggling with this for last 2-3 days.

I have developed a web service in C#. The web service returns large sum of data containing lot of columns and rows. For the data to be accessible in VBA I return data from web service to excel in the form of XML.

At excel side, from VBA I call the web service and get my xml as string. Now the problem is when I load data from XML to excel.

1) I tried using ActiveWorkbook.XmlImport, but when the data is imported the excel automatically do some kind of formatting on the data and also it enables the filter automatically. I want to have my own formatting. I tried a lot to remove the formatting done while using XmlImport, but in vain. I would be happy if any one can help me on a method to remove the formatting.
Or
2) Inorder to overcome that problem I use my own for loop to parse through XML document open using MSXML2.DOMDocument. But it is taking double the time it takes to open with XmlImport. I would like to know a better way to load data from XML to excel. I tried opening my xml using a recordset, but it failed since the xml is not generated using ADO.
Or
3) I would have no problem in changing the web service to return something else instead of Xml. But I need the VBA to load the data faster. My web service have to return data consisting of 4000 rows and 75 columns.

I would be really happy if anyone can help me on this. I just need a faster way to get data from Web service to be loaded into excel.

Thanking you all for the time.

Regards,
Me.

 

[omar_85]

I didn't get any reply, anyway i am putting what i have done to over come it.

The for loop idea being too slow, I opted for the xmlimport itself. The problem with XMLImport was that the formatting wont get active unless we edit each cell.

one idea is to run a macro on columns were formatting is done. This also is a bit time consuming.

What I did is, I wrote a macro to select the range of data on which we have applied the formatting and did text to column operation on that data.
Then all the data gets formatted. The text to column operation refreshes the data selected. So it gets formatted. Its a lot faster than the for loop option.

If anyone didn't understand plz feel free to ask, I will explain in detail.

Regards,
Me