I've seen that VBA Excel can be used to control Internet Explorer - but how??

londonman81

Hi all,

I have seen an Excel application which allows the user to input data from an Excel sheet into boxes/form on an Internet Explorer page.

I have tried to find out more information about controlling Internet Explorer using VBA Excel through Googling etc but I found surprisingly little in the way of introducing code etc.

What little I have found does not quite do what I need. I have figured out how to open a webpage from within Excel - but I can't seem to find code that will help me to identify boxes on a webpage and to enter data into it.

Does anyone have any links/knowledge of how to go about doing this? I'm sure it's not difficult but I'm just amazed that there is so little coverage of VBA Excel interfacing with Explorer...I would have thought it was very useful and more popular.


Thanks!

L

meclive

This might help you: http://www.dicks-blog.com/archives/2004/09/22/automating-internet-explorer/

Chippy_Minton

I've done something similar, but the other way round, i.e. Excel hosting Internet Explorer and then extracting data from web pages and saving it in Excel.

Reply no. 13 in the link that meclive posted appears to do what you want from the IE side of things. It's just a matter of getting the data from the Excel cell and passing it as parameter to the appropriate function, e.g. the TextBox function shown.

londonman81

Chippy_Minton wrote:

I've done something similar, but the other way round, i.e. Excel hosting Internet Explorer and then extracting data from web pages and saving it in Excel.

Reply no. 13 in the link that meclive posted appears to do what you want from the IE side of things. It's just a matter of getting the data from the Excel cell and passing it as parameter to the appropriate function, e.g. the TextBox function shown.

Thanks Chippy - actually I also want to do what you mentioned too - i.e taking data from IE and dumping it into Excel. So I would like to take data both ways from Excel to IE and IE to Excel.

I have tried using the Import>Web Query function which opens up a copy of IE within Excel and then I can select the ticks which correpond to the part of the webpage that have the data I want. But it takes the whole lot and doesn't allow me to chop out bits within that selection that I don't want e.g want numbers in a table and not the labels/headers. Each time it reloads the data it loads it in the original full form again which I don't want.


Anyway - I've had a look at point 13 in the link and it doesn't seem to mention how we identify the TEXTBOX in IE.....any clues as to how to do this?? Or any decent reference for this whole VBA Excel + IE business??


Thanks!

L

Chippy_Minton

londonman81 wrote:

Anyway - I've had a look at point 13 in the link and it doesn't seem to mention how we identify the TEXTBOX in IE.....any clues as to how to do this?? Or any decent reference for this whole VBA Excel + IE business??

In the code

Public Function TextBox(Name As String, Text As String) As Boolean
‘ Fill textbox Name with Text
Dim Element As Variant

TextBox = False
Set Element = brs.Document.getElementsByName(Name)
If Not Element Is Nothing Then
Element(0).Value = Text
TextBox = True
End If
End Function

the getElementsByName is the basis for identifying the textbox by the 'Name' argument of the TextBox function. There is also getElementsByTagName.

E.g. if you view the HTML source of the MSE page when replying to a threading, the Title text box has the following HTML:

<input type="text" class="bginput" name="title" value="" size="50" maxlength="85" tabindex="1" title="Optional" />

Here, 'title' is the value of the name attribute and you would specify "title
" as the Name parameter to the TextBox function.

At the top of that page it shows the Microsoft Internet Controls reference ticked. You also need to tick the Microsoft HTML Object Library to get the DOM objects etc.