Excel getting data from a web form?

I apologize in advance, because I'm having trouble phrasing this question.

We need to have a dialog box in Excel that can dynamically change based on data from our server.

How can I get Excel to display a form that is generated from HTML (which should be directly retrieved from a webserver), and then take the results of submitting that form into a VBA string that I can then manipulate?

(Assume that the user has a constant reliable connection to the internet, and that our server is never down.)

Is there a way to get Excel to open a browser window whose behavior I can intercept like this? Or am I going to have to use Ajax and parse the HTML myself to create a form out of VBA?

I hope th开发者_StackOverflow社区e question even makes sense! Thank you!

Use the Internet Explorer ActiveX control. Here is a complete tutorial for Excel VBA:

http://vba-corner.livejournal.com/4623.html

If you have Excel 2010, you can try out Data->From Web feature which is quite cool one. I am sure u can intergrate it with VBA

You can use an instance of XMLHttp to request and receive information from a webserver. If the "webservice" you are accessing is under your control then it would be much better to return the data as csv/plain text, or XML if you need a more structured dataset.

If you need to pass in some information in the request then you can use either GET or POST (depending on how much you need to send). This would mimic the posting of a regular web page form.

Here's a simple function which will fetch information from a URL passed to it:

Private Function WebResponse(sURL As String) As String

    Dim XmlHttpRequest As Object
    Set XmlHttpRequest = CreateObject("MSXML2.XMLHTTP")
    XmlHttpRequest.Open "GET", sURL, False
    XmlHttpRequest.send
    WebResponse = XmlHttpRequest.responseText

End Function

You URL could be in the form of: http://yourserver/page.php?id=22

Where "22" is the info you're passing in to the request to determine what information the server is to reply with.