From C# API Datatable to ADOB recordset for VBA

Question

I have an REST API, written in C#. The API queries the data from a Database and returns a message with POST. The API is called from an Excel Macro. The issue that I am facing is with the results of the query.

If I try to return a ADODB.Recordset then the message cannot be serialized (xmlSerializer.Serialize method fails).

If I return a datatable then - it is not recognized in VBA code.

(For i = 0 To dataTable.Columns.Count - 1) returns the error "Object does not support this property or method."

Can you please advise me about the best way approach this issue.

Thanks in advance.

Answer 1

After investigating I successfully used the following method to solve this problem:

Return ADODB.RecordSet from ReST Call as ADODB.Recordset

Server Code -

ADODB.Stream stream = new ADODB.Stream();
rs.Save(stream, PersistFormatEnum.adPersistXML);
rs.Close();
rsString = stream.ReadText();
return Ok(rsString);

Client Code _

Recordset ret = null;
string strResponse = string.Empty;
Does things to call out to the ReST web service...
if (!string.IsNullOrEmpty(strResponse))
{
    strResponse = Replace(Replace(Replace(strResponse, "\\t", "    "), "\\r\\n", " "), "\", "");
    ret = new Recordset();
    Dim s As Object
    Set s = CreateObject("ADODB.Stream")
    stream.Open();
    stream.WriteText(strResponse);
    stream.Position = 0;
    ret.Open(stream);
}

return ret;