Controlling a web browser using Excel VBA

问题:

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,

回答1:

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

回答2:

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

回答3:

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")