Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 4 of 4

Thread: VBA To Fill Web Page Search Form & Activate

  1. #1
    Join Date
    6th January 2009
    Posts
    19

    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.

    VB:
    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("setValueAndPostBack('ctl00_ctl16_MarginalColumnPostBackHiddenField','GlobalDerivativeSearch')", 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 at 15:28.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    16th June 2005
    Location
    Dublin
    Posts
    4,486

    Re: VBA To Fill Web Page Search Form & 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.

    VB:
    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 Images
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    _______________________________________________
    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

    _______________________________________________

  3. #3
    Join Date
    6th January 2009
    Posts
    19

    Re: VBA To Fill Web Page Search Form & Activate

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

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    16th June 2005
    Location
    Dublin
    Posts
    4,486

    Re: VBA To Fill Web Page Search Form & 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
    _______________________________________________
    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

    _______________________________________________

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Activate Web Page Application
    By FrankTheTank in forum EXCEL HELP
    Replies: 5
    Last Post: April 16th, 2008, 02:42
  2. Fill Web Page Form With Cell Data
    By Jeremy_Key in forum EXCEL HELP
    Replies: 1
    Last Post: April 9th, 2008, 04:27
  3. Activate Web Page For Entry
    By Prezken in forum EXCEL HELP
    Replies: 12
    Last Post: October 13th, 2007, 06:35
  4. need a formula on page 2 to search data on page 1
    By indiansfan in forum EXCEL HELP
    Replies: 4
    Last Post: May 20th, 2006, 20:56
  5. fill in a special form (tax form)
    By tgawalid in forum EXCEL HELP
    Replies: 1
    Last Post: December 30th, 2004, 17:08

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno