Excel to Webpage automation

s_s_sridhar · Sep 29, 2005

Hi All,

I have a web based application that consists of text fields and button. I have a set of data in Excel sheet. I have to enter these data into some of the text fields of the web applicaton.

Can any body tell me how to automate this process as the volume of data is huge. I can write and understand Excel macros. If this is not posible through Excel please tell me how to achive this.

Please help me in this.

Thanks,
Sridhar

scifibum · Sep 29, 2005

Hi.

OK, if I understand correctly you have data in an Excel sheet that you want to enter into a form on a web page.

Below is some code to look at to get you started. This instantiates a new Internet Explorer window, loads the google home page, and enters information into the search form and submits the form. You should be able to use this as a starting point for a macro that loads your web application and enters information into it.

Of course you will need to add all the code that will move through your data. And it won't be efficient to open a new browser for each set of data that you enter on your form, so you'd want to probably loop through the data after opening the browser, and enter it all in the same instance of the browser.

Please look this over and then if you have more questions post back with what you need more help with.

Code:

Sub Googlesearch()

    'This project includes references to "Microsoft Internet Controls" and
    '"Microsoft HTML Object Library"
    
    'Variable declarations
    Dim myIE As New InternetExplorer
    Dim myURL As String
    Dim myDoc As HTMLDocument
    Dim strSearch as String
        
    'Set starting URL and search string
    myURL = "http://www.google.com"
    strSearch = "MrExcel"

    'Make IE navigate to the URL and make browser visible
    myIE.navigate myURL
    myIE.Visible = True
    
    'Wait for the page to load
    Do While myIE.Busy Or myIE.readyState <> READYSTATE_COMPLETE
        DoEvents
    Loop
    
    'Set IE document into object
    
    Set myDoc = myIE.document
 
    'Enter search string on form
    myDoc.forms(0).q.Value = strSearch

    'Submit form
    myDoc.forms(0).submit
    
    'Wait for the page to load
    Do While myIE.Busy Or myIE.readyState <> READYSTATE_COMPLETE
        DoEvents
    Loop
    
    MsgBox myIE.LocationName
    
End Sub

scifibum · Sep 29, 2005

Here's a better example that illustrates a loop to move through data and entering it on a form.

Code:

Sub Googlesearch()

    'This project includes references to "Microsoft Internet Controls" and
    '"Microsoft HTML Object Library"
    
    'Variable declarations
    Dim myIE As New InternetExplorer
    Dim myURL As String
    Dim myDoc As HTMLDocument
    Dim strSearch As String
    Dim c As Range
    
    'Set starting range (first cell of data)
    Set c = Sheets("Sheet1").Range("D3")
    
    'Set starting URL and search string
    myURL = "http://www.google.com"
   
    'loop through list of data
    Do While c.Value <> vbNullString

        'Make IE navigate to the URL and make browser visible
        myIE.navigate myURL
        myIE.Visible = True
        
        'Wait for the page to load
        Do While myIE.Busy Or myIE.readyState <> READYSTATE_COMPLETE
            DoEvents
        Loop
        
        'Set IE document into object
        
        Set myDoc = myIE.document
     
        'Enter search string on form
        myDoc.forms(0).q.Value = c.Value
        
       'Submit form
        myDoc.forms(0).submit
        
        'Wait for the page to load
        Do While myIE.Busy Or myIE.readyState <> READYSTATE_COMPLETE
            DoEvents
        Loop
        
        Set c = c.Offset(1, 0)
    Loop
    
End Sub

s_s_sridhar · Oct 20, 2005

Hi,

Thanks lot. sorry for the belated reply.

How do you find 'q' as name of the text field. In my case the page will be generated dynamically. To be specific, it's a web based CRM application, which has lot of text fields and buttons.

Do we have solution for this case.

Thanks,
SRI

davers5 · Mar 28, 2007

Sorry to dredge up this old post but this seems to get to the heart of my question. If there is a text box on a page that I want to enter data in from vba, how do I find it's name so I can manipulate it? Specifically, I am trying to manipulate a drop down box on a page.

Thanks!
Dave

tightwad · Mar 28, 2007

Best way to find the name of the textbox is to look at the html code of the page. Drop down boxes usually have a numerical value associated with the options. I have also used "Edit with Word" to help out at times...since it lets you see all the names and values of each object on the webpage.

I have done this successfully many times with the help of the masters on this site.

davers5 · Mar 28, 2007

Ok great. So here is the snippet of the html I would like to modify:

Rich (BB code):

<td class="player"><div nowrap>Jason Kendall </div> <div class="detail"><span>(Oak - C)</span>  </div></td>
<td class="edit"><select name="5562"><option value="C" selected>C</option><option value="Util">Util</option><option value="BN">BN</option></select></td>

The <option> tag looks like it defines a drop down box. The "selected" value looks like it means that "C" is selected (this is the case on the page). But how would I go about changing it to "BN". Once I do this, I'd have to submit the changes, but that's just a button click and that is easy. It's the change to "BN" I can't seem to figure out.

Thanks,
Dave

Norie · Mar 28, 2007

Dave

It would probably be better if you started a new thread for this.

And to help us help you include the relevant URL in that thread.

davers5 · Mar 28, 2007

As requested by Norie, I started a new thread for this. It is entitled:
Manipulate Web Controls through VBA

Thanks!

Excel to Webpage automation

s_s_sridhar

New Member

Excel Facts

scifibum

Well-known Member

scifibum

Well-known Member

s_s_sridhar

New Member

davers5

Active Member

tightwad

Well-known Member

davers5

Active Member

Norie

Well-known Member

davers5

Active Member

Similar threads

Forum statistics

Share this page

Excel to Webpage automation

New Member

Excel Facts

Well-known Member

Well-known Member

New Member

Active Member

Well-known Member

Active Member

Well-known Member

Active Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock