Announcement

Collapse
No announcement yet.

VBA To Fill Web Page Search Form & Activate

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • VBA To Fill Web Page Search Form & Activate



    Hi,
    I tried out a lot to come up with an solution for the latest fancy idea of my boss. He wants his excel program to ask for some derivatices quotes using the following webpage:
    http://www.scoach.de/EN/Showpage.aspx?pageID=19

    On the left there is a text field called product search, where Excel should enter a local security number like "DZ6DJ7". The page uses java to determine the international security number and returns a string / url like http://www.scoach.de/EN/Showpage.asp...N=DE000DZ6DJ71. If i would get something like this, then the rest is very easy :-/

    my problem is now to enter the local ID into the correct field in the java script.It is related to the code below but i really cannot find my way through the java code / excel to enter the right values to the correct place. Any help is higly appreciated.

    Code:
    div class="marginal">
     
    		<input type="hidden" name="ctl00$ctl16$MarginalColumnPostBackHiddenField" id="ctl00_ctl16_MarginalColumnPostBackHiddenField" />
     
    		<div class="kurssuche">
    			<h1 id="ctl00_ctl16_kursSuche">Produktsuche</h1>
    			<input name="ctl00$ctl16$ProductSearch" type="text" id="ctl00_ctl16_ProductSearch" class="left textBox" onkeypress="var key = (event.which) ? event.which : window.event.keyCode; if (key == 13) { window.setTimeout(&quot;setValueAndPostBack('ctl00_ctl16_MarginalColumnPostBackHiddenField','GlobalDerivativeSearch')&quot;, 250); return false; }" />
    			<a id="ctl00_ctl16_SearchButton" class="gobutton left" href="javascript:__doPostBack('ctl00$ctl16$SearchButton','')" style="margin-left:5px;"></a>
    		</div>
    Last edited by mm_sl; January 13th, 2009, 16:28.

  • #2
    Re: VBA To Fill Web Page Search Form &amp; Activate

    Hi MM_SL,

    This is something that I am only getting into myself recently, based primarily on posts in this forum, so the code is a little clunky and it may be possible to do it better / quicker, but in short, it does what you asked for.

    One important thing, is that there MUST a reference to Microsoft Internet Controls (shdocvw.dll) and Microsoft HTML object Library. Do this through the VBA editor menu, Tools/References - see screenshot below. The attachment should have those references set up already though...

    The product code is picked up from a cell (A1) and the new URL (the one your looking for) is returned in a message string. So you just gotta stick the whole thing in a loop if had more to process or stick a userform on the front to make it sexy. It could be a bit slow if you had a lot of pages to get through.

    Code:
    Option Explicit
    
    Public Sub Web_Page_Access()
    
    'make sure you add references to Microsoft Internet Controls (shdocvw.dll) and
    'Microsoft HTML object Library.
    'Code will NOT run otherwise.
    
    Dim objIE As SHDocVw.InternetExplorer 'microsoft internet controls (shdocvw.dll)
    Dim htmlDoc As MSHTML.HTMLDocument 'Microsoft HTML Object Library
    Dim htmlInput As MSHTML.HTMLInputElement
    Dim htmlColl As MSHTML.IHTMLElementCollection
    
    Set objIE = New SHDocVw.InternetExplorer
     
    With objIE
        .Navigate "http://www.scoach.de/EN/Showpage.aspx?pageID=19" ' Main page
        .Visible = 1
        Do While .busy: DoEvents:   Loop
            Do While .READYSTATE <> 4: DoEvents: Loop
            Set htmlDoc = .document
            Set htmlColl = htmlDoc.getElementsByTagName("INPUT")
            For Each htmlInput In htmlColl
                If htmlInput.Name = "ctl00$ctl16$ProductSearch" Then
                    htmlInput.Value = ActiveSheet.Cells(1, 1).Value
                    Exit For
                End If
            Next htmlInput
    
            Set htmlDoc = .document
            Set htmlColl = htmlDoc.getElementsByTagName("a")
            Do While htmlDoc.READYSTATE <> "complete": DoEvents: Loop
            For Each htmlInput In htmlColl
                If Trim(htmlInput.ID) <> "" Then
                    If htmlInput.ID = "ctl00_ctl16_SearchButton" Then
                        htmlInput.Click
                        Exit For
                    End If
                End If
            Next htmlInput
        
        Do While htmlDoc.READYSTATE <> "complete": DoEvents: Loop
        MsgBox "New URL is : " & objIE.LocationURL
        
    End With
                     
    End Sub
    Ger
    Attached Files

    Check out our new reputation system. Click on the "star" under the post!
    _______________________________________________

    There are 10 types of people in the world. Those that understand Binary and those that dont.

    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

    The BEST Lookup function of all time

    Dynamic Named Ranges are your bestest friend

    _______________________________________________

    Comment


    • #3
      Re: VBA To Fill Web Page Search Form &amp; Activate

      This is really great stuff. I works perfectly, will have a closer look later! Thanks a lot mate!!

      Comment


      • #4


        Re: VBA To Fill Web Page Search Form &amp; Activate

        No problem. Good luck with the rest of the project. if you have any new questions (on other areas), post a new thread as this one would be deemed resolved (unless the question is relevant to the code I posted).

        Take care,
        Ger

        Check out our new reputation system. Click on the "star" under the post!
        _______________________________________________

        There are 10 types of people in the world. Those that understand Binary and those that dont.

        Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

        The BEST Lookup function of all time

        Dynamic Named Ranges are your bestest friend

        _______________________________________________

        Comment

        Working...
        X