Macro to open web page, find text, and click on text's URL link?

  • Hi
    I need a macro to open an instance of IE and browse to a specific URL on company intranet.
    I then need to search for a hypertext link named "Download Search Results in Excel Format" within the page i've navigated to.
    This hypertext link URL is dynamic (changes) hence I cant link directly to it.
    The hypertext link function is to open a CSV spreadsheet.
    Clicking on the hypertext link brings up the SaveAS dialog box. I only need to open the CSV file.
    I have the code to import the spreadsheet to another workbook.
    Can someone please help me with code to open the web page, click on the specific hypertext link, select open the file.
    The web page is built with HTML code.
    Cheers

  • Re: Macro to open web page, find text, and click on text's URL link?


    Try something like this. In the VBA editor, Tools - References menu, set references to MS Internet Controls and HTML Object Library, otherwise the code won't work.


    The Workbooks.Open line attempts to open the CSV file. This should work as long as clicking the hyperlink doesn't run any scripts such as Javascript or CGI.


  • Re: Macro to open web page, find text, and click on text's URL link?


    Thank you John for your reply.


    The code works to download the CSV file but the file is not populated with any data.


    Also, when I normally download the file, clicking the OPEN button on the IE9 dialog bar, the file downloads and has a CSV file name "SearchResults". Your code resulted in a CSV file with name "f". Not sure why.


    If it helps - this is the HTML code of the hypertext link to download the CSV file. Remember this hypertext link is dynamic as the numbers change whenever a new search is performed.


    </tr></table></td></tr>
    <tr><td class="t6bottom" colspan="99"><a href="f?p=128:1:16277908830085:FLOW_EXCEL_OUTPUT_R28176648772060915_en-au" >Download Search Results in Excel Format</a></td></tr>
    </table></td></tr>


    I have this other code below I found on net. It works similar to yours, but I have to click the OPEN button to get the CSV file to download.


    This opens the CSV file, but it to has no data populated in the CSV file. The file is however named SearchResults.csv as if I performed the function manually.


    Any further help or suggestion appreciatted.


    Kind Regards


    Sub Search_PADI_Import()
    '
    ' Code to open web page and then click the hypertext link.
    Dim IE As Object
    Dim NewURL As String
    Dim sURL As String
    Dim strCountBody As String
    Dim lStartPos As Long
    Dim lEndPos As Long
    Dim TextIWant As String
    Application.ScreenUpdating = False
    Set IE = CreateObject("Internetexplorer.Application")
    sURL = "company intranet search site" 'hidden for security reasons.
    With IE
    .navigate sURL
    ' uncomment the line below if you want to watch the code execute, or for debugging
    .Visible = True
    End With
    ' loop until the page finishes loading
    Do While IE.Busy
    Loop
    ' click a text link on the page after that
    Set ElementCol = IE.document.getElementsByTagName("a")
    For Each Link In ElementCol
    If Link.innerHTML = "Download Search Results in Excel Format" Then
    NewURL = Link.GetAttribute("href")
    Exit For
    End If
    Next Link
    With IE
    .navigate NewURL
    ' uncomment the line below if you want to watch the code execute, or for debugging
    .Visible = True
    End With
    ' loop until the page finishes loading
    Do While IE.Busy
    Loop
    End Sub

  • Re: Macro to open web page, find text, and click on text's URL link?


    Hi John


    I delved into it a bit more. This issue I have is after navigating to the web page, it needs to click once on the SEARCH button. The search results then update on the web page. After this the code to click "Download Search Results in Excel Format" should be able to execute.


    Any help on that?


    The SEARCH button is in this part code:


    td><td rowspan="5" align="left"><a href="javascript:apex.submit(&#x27;Go&#x27;);" class="t6Button">SEARCH</a><a href="javascript:apex.submit(&#x27;CLEARSEARCH&#x27;);" class="t6ButtonAlternative2">Reset</a><label for="P1_ROWS"><span class="t6OptionalLabel"> Display </span></label><input type="hidden" name="p_arg_names" value="28182221304060935" /><select id="P1_ROWS" name="p_t09" class="selectlist" size="1" onchange="apex.submit(&#x27;P1_ROWS&#x27;);" ><option value="10">10</option>


    Thanks

  • Re: Macro to open web page, find text, and click on text's URL link?


    The HTML you've posted doesn't show if there's an easier way of referencing the SEARCH link, e.g. by id or name attribute of a parent element, so this code uses the same technique as my previous code to search for the link and then click the link.