+ Reply to Thread
Results 1 to 5 of 5

Click on particular name in webpage using VBA in excel

Hybrid View

  1. #1
    Registered User
    Join Date
    04-03-2014
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    5

    Click on particular name in webpage using VBA in excel

    Hi everyone,

    I want to write a macro with which i can click on a particular hyperlink in that website.

    For example...the website is "http://data.worldbank.org/country"...In this websote I want to click on the particular country (Let say "Gabon")...but I want to click it by using vba from excel.

    I hope you guyz are expert and can help me out on this problem.


    Thanks,
    Ankit.

  2. #2
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,330

    Re: Click on particular name in webpage using VBA in excel

    one way

    create a userform and add a combo box and command button , Reference Library "Microsoft HTML Object Library"
    copy and paste the code below into the userform
    Option Explicit
     Private Sub CommandButton1_Click()
        Dim appIE  As Object
        Dim sURL   As String
        Application.ScreenUpdating = False
            Set appIE = GetIE
            sURL = "http://data.worldbank.org/country/" & Me.ComboBox1.Value
            With appIE
                .navigate sURL
                .Visible = True
            End With
        Application.ScreenUpdating = True
        Set appIE = Nothing
    End Sub
    Private Sub UserForm_Initialize()
        Dim htm As HTMLDocument, objTable As Object, objTd As Object, objDat As Object
        Dim Myarray() As String, intRow As Long, intCol As Long
        Set htm = New HTMLDocument
        With CreateObject("MSXML2.XMLHTTP")
            .Open "GET", "http://data.worldbank.org/country", False
            .send
            htm.body.innerHTML = .responseText
        End With
        With htm
            Set objTable = .getElementById("block-views-countries-block_1").getElementsByTagName("tr")
            ReDim Myarray(1 To (objTable.Length + 1), 1 To 1)
            For intRow = 0 To objTable.Length - 1
                Set objTd = objTable(intRow).getElementsByTagName("td")
                For Each objDat In objTd
                    Debug.Print objDat.innerText
                    intCol = intCol + 1
                    Myarray(intRow + 1, 1) = objDat.innerText
                Next
            Next
        End With
        Me.ComboBox1.List = Myarray
    End Sub
    
    Function GetIE() As Object
        On Error Resume Next
        Set GetIE = CreateObject("InternetExplorer.Application")
    End Function
    If the solution helped please donate to RSPCA

    Site worth visiting: Rabbitohs

  3. #3
    Registered User
    Join Date
    04-03-2014
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Click on particular name in webpage using VBA in excel

    Thanks Pike... It is working fine..
    You have an amazing knowledge about how code works. I will really appretiate if you could add comment to your code (wherever required) therefore it may be easy to understand for me ?

    Thanks again,
    Ankit.

  4. #4
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,330

    Re: Click on particular name in webpage using VBA in excel

    Hi bhavsarankit05
    Sure ... hmmm....Basically

    The UserForm_Initialize uses the XMLHTTP object to retrieve the result of the query, instead of instantiating Internet Explorer it uses the MSXML Object Model. Using the the HTML Object Model the result is HTML, not XML. It saves the url page in the HTMLDocument Object. After manually looking at the markup code (source code) to find specfic HTML ID. It uses the getElementByid Method return a specfic HTML ID and the getElementsByTagName Method to return a collection of all the elements with the given tag name in the ID. It then loops through the resulting collection and puts them into a array to use in the combobox.
    The combobox/comandbox use Internet Explorer to navigate to the site.
    Attached Files Attached Files
    Last edited by pike; 04-17-2014 at 12:10 AM.

  5. #5
    Registered User
    Join Date
    04-03-2014
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Click on particular name in webpage using VBA in excel

    Thank you very much Pike....Perfect explanation....

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. VBA to click on 'Continue' button on webpage
    By Jimbo77 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-20-2014, 06:49 PM
  2. VBA to click on link in webpage that uses jquery
    By CJK in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-10-2013, 11:09 AM
  3. How to Click a Hyperlink on webpage
    By sugaprasad in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-04-2013, 10:10 AM
  4. click webpage button problem
    By ckjason in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-01-2013, 03:56 AM
  5. [SOLVED] Help click on link javascript webpage
    By rickmdz in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-06-2012, 11:59 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1