How to pass a parameter using vba to an asp.net web api?

Question

I'm trying to use vba to post to a web api. The problem is that the parameter is blank in the web api. The web api is written in asp. I called the api from c# and it works but I can't get it to work from vba.

Here is the vba call to the api:

Sub Test()

    Dim http As New MSXML2.XMLHTTP60
   
    http.Open "POST", "http://10.5.72.172:108/api/vbaemail", True

    http.SetRequestHeader "Content-type", "application/json"
    
    Call http.send("abc")
   
End Sub

Here's my simple web api.

public void Post([FromBody] string strJson)
{
    SqlConnection cnnCom = null;
    SqlCommand cmdCom = null;
    string strSql = null;

    //write parameter to table
    cnnCom = new SqlConnection(CF.Get_Connection_String(CF.DatabaseName.COMMON));
    cnnCom.Open();
    strSql =
        "INSERT INTO Test( " +
        "F1) " +
        "VALUES( " +
        "'" + strJson + "')";
    cmdCom = new SqlCommand(strSql, cnnCom);
    cmdCom.ExecuteNonQuery();
    cnnCom.Dispose();
}

Here is the working c# call to the web api:

 static void Main(string[] args)
{
    HttpClient hc = new HttpClient();
    Task<HttpResponseMessage> task1 = null;

    //call web api
    task1 = hc.PostAsJsonAsync<string> 
        ("http://10.5.72.172:108/api/vbaemail", "abc");
}

see if this works for you gist.github.com/six519/5ed917850f402b94ee6b — Sowmyadhar Gourishetty
– Sowmyadhar Gourishetty, Commented Jul 16, 2021 at 18:25
How come you don't see any vb code there? Can you check here once gist.githubusercontent.com/six519/5ed917850f402b94ee6b/raw/… — Sowmyadhar Gourishetty
– Sowmyadhar Gourishetty, Commented Jul 16, 2021 at 18:34
Does this answer your question? Pass Parameters in VBA HTTP Post Request — Seeds
– Seeds, Commented Jul 16, 2021 at 18:35

Shaybakov · Accepted Answer · 2021-07-16 18:42:17Z

0

http.Open "POST", "http://10.5.72.172:108/api/vbaemail", True

instead True set False

answered Jul 16, 2021 at 18:42

Shaybakov

7668 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Caius Jard Over a year ago

(For the benefit of other readers, True means asynchronous, False means synchronous)

Madison320 Over a year ago

Same thing, the parameter passed to the web api is blank, also I get "operation aborted" during the send command.

Madison320 · Accepted Answer · 2021-07-21 13:51:40Z

0

I was able to get it to work by surrounding the string with single quotes. The problem is I can't use single quotes anywhere in the body being passed to the web api, although there's probably a way to escape them. In our case it's not a big deal, we're just trying to patch some old vba programs that are going away eventually.

Sub Test()

    Dim http As New MSXML2.XMLHTTP60
   
    http.Open "POST", "http://10.5.72.172:108/api/vbaemail", True

    http.SetRequestHeader "Content-type", "application/json"
    
    Call http.send("'" + strJson + "'")  
   
End Sub

answered Jul 21, 2021 at 13:51

Madison320

2574 silver badges15 bronze badges

Collectives™ on Stack Overflow

How to pass a parameter using vba to an asp.net web api?

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related