Controlling a web browser using Excel VBA

Question

I have been assigned the task of automating a web based task ( for a HTTPS website). The users currently are filling in the Excel sheet with the data, they now want to automate excel in such a way that it directly controls the browser and fills in the data.

I found the iMacros Scripting edition as a possible solution for doing this, I wanted to know if there are any other similar tools which can be used for controlling the browser and filling in data.

I also had a look at the Selenium Client Driver, but I am not sure on how to use it in Excel VBA.

Any help would be appreciated.

Thanks,

Plenty of examples out there if you google for "VBA automate IE" (also plenty of VB6 examples which should map almost unchanged to VBA). How familiar are you with web programming? That's really the biggest hurdle to accomplishing this type of task: it's easy to get a reference to the HTML document, but locating and manipulating objects within that is more tricky. — Tim Williams
– Tim Williams, Commented Sep 20, 2011 at 18:31
actually I am not very familiar with it. I think it will take quite a bit of figuring out on how to work with it. Any suggestions or tools which can help with the job? — Darshan Bhatia
– Darshan Bhatia, Commented Sep 21, 2011 at 6:12
If I need to do this I tend to use VBA, but only because that's what I'm familiar with. Plenty of other languages and frameworks out there, and Google will point you to tons of examples. It's easier to give advice on specific problems, so you should just try out some simple examples and check back here if you have questions on the next steps. — Tim Williams
– Tim Williams, Commented Sep 21, 2011 at 6:31
@Darshan Bhatia: If you can share the link then I can post a sample. You can write to controls like Textboxes/Comboboxes etc in a web browser after you refer to them by their Element IDs. — Siddharth Rout
– Siddharth Rout, Commented Mar 6, 2012 at 20:17
@Siddharth: The website is internal and hence i would not be able to post the link. — Darshan Bhatia
– Darshan Bhatia, Commented Apr 21, 2012 at 15:29

florentbr · Accepted Answer · 2012-03-06 18:31:12Z

You can use Selenium from Visual Basic Editor by installing the tools provided here :

http://code.google.com/p/selenium-vba/

There is a Selenium IDE plugin to automatically record a script in VBA and an installation package to run Selenium command in Visual Basic Editor.

The following example starts firefox, opens links in the 1st column, compares the title with the 2nd column and past the result in the 3rd column. Used data are in a sheet, in a range named "MyValues".

Public Sub TC002()
   Dim selenium As New SeleniumWrapper.WebDriver, r As Range
   selenium.Start "firefox", "http://www.google.com" 
   For Each r In Range("MyValues").Rows
     selenium.open r.Cells(, 1)
     selenium.waitForNotTitle ""
     r.Cells(, 3) = selenium.verifyTitle(r.Cells(, 2))
   Next
   selenium.stop
End Sub

Bruno Leite · Accepted Answer · 2011-09-21 00:54:17Z

1

This sample open stackoverflow site an show IE

Sub OpenIE()
'officevb.com
Dim ie As Object
Set ie = CreateObject("InternetExplorer.Application")

ie.Navigate "http://www.stackowerflow.com"

 'wait load
 While ie.ReadyState <> READYSTATE_COMPLETE
  DoEvents
 Wend

ie.Visible = True

End Sub

[]'s

answered Sep 21, 2011 at 0:54

Bruno Leite

1,47713 silver badges17 bronze badges

Comments

Sirga · Accepted Answer · 2011-10-10 06:52:03Z

I use this code for reading data from excel and passin it to selenium for to do task like "click, select, close etc" and also you can write data to excel.

This is in python i don know VB and i do know perl if u wish i'll give same code in perl too.

i hop this may help.

from xlwt import Workbook

import xlrd

testconfigfilename="testconfig.xls"

    if (len(sys.argv) > 1):

        testconfigfilename=sys.argv[1]       

    wb = xlrd.open_workbook(testconfigfilename);

    wb.sheet_names();

    sh = wb.sheet_by_index(0); 'Sheet 0 - selenium server configuration'



    seleniumHost = sh.cell(1,0).value

    seleniumPort = int(sh.cell(1,1).value)

    testBaseURL = sh.cell(1,2).value

    browser = sh.cell(1,3).value

    timeout = int(sh.cell(1,4).value)

    path = sh.cell(1,5).value

outputwb = Workbook()

    outputsheet = outputwb.add_sheet("result",cell_overwrite_ok=True) #get the first sheet in the result xls 

outputsheet.write(RowNumber,colNumber,"data")

Collectives™ on Stack Overflow

Controlling a web browser using Excel VBA

3 Answers 3

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Linked

Related