1

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 the question even makes sense! Thank you!

Improve this question
3
  • What are you running on the server? Does it have to be a HTML page? Connecting directly to a database (like mySQL) might be much more convenient - I don't know how well Excel handles those though Commented Jun 7, 2011 at 16:06
  • Why not just use Excel's feature to retrieve data from a webpage (For 2007/2010; Data tab on the Ribbon, "From Web" option)? Commented Jun 7, 2011 at 16:06
  • This is as a part of a larger macro-writing project, I don't have control over what's coming from the server. It's HTML, although I might be able to convince them to give me XML. Commented Jun 7, 2011 at 16:08

3 Answers 3

Reset to default
2

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

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

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

Comments

1

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

Improve this answer

1 Comment

Intriguing.. I wonder if there's a way to get it to display a form to the user without all the ugly arrows all over the place (at least the ugly arrows show up in the Excel 2003 equivalent), and to allow pasting of POST data. I'll investigate this option.
0

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.

Improve this answer

3 Comments

I know about AJAX, but the trick is that I need to take an HTML form from a web server and display it to the user, allowing them to submit the form and then take the output from the submitted form to manipulate it.
MSXML2.ServerXMLHTTP would be more suited in this case interacting with a server. @Josh - You could spawn an instance of IE but it's slow and dirty. Or use MSXML2.ServerXMLHTTP and load the response text into an HTML doc eg Set HTMLDoc = New HTMLDocument then HTMLDoc.body.innerHTML = XMLHTTP.responseText and display the doc. This may also help
@osknows - it wasn't clear from the original question whether the HTML input form was required, or whether they're just piggy-backing on an existing page. I guessed they could manage without it, which has the benefit of not having to parse out data from HTML. I think the ServerXMLHTTP is optimized for use on a server, rather than client-side (support.microsoft.com/kb/290761).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.