Results 1 to 10 of 10

Thread: Excel VBA - Export data from webpage

  1. #1
    New Member
    Join Date
    Apr 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Lightbulb Excel VBA - Export data from webpage

    Hi.

    Good day everyone.

    I am writing a VBA application for an excel workbook, which contains information regarding multiple funds of multiple fund managers. In the worksheet, there is a "Update" button. When clicked, it grabs the returns and other information from the webpage and place them into the spreadsheet for analysis.

    Does anyone knows the VBA application to get the funds table data from this webpage?
    https://www.fundsupermart.com.my/fsm...-selector/////

    Thanks.

  2. #2
    Board Regular Worf's Avatar
    Join Date
    Oct 2011
    Location
    Rio, Brazil
    Posts
    3,595
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Excel VBA - Export data from webpage

    Welcome to the Board

    There are many options to be selected on the web page. Do you need the code to make choices there, or just accept the defaults and click the generate button?
    Excel 2013 / Windows 8.1 (home)
    Excel 2013 / windows 7 (work)


  3. #3
    Board Regular Worf's Avatar
    Join Date
    Oct 2011
    Location
    Rio, Brazil
    Posts
    3,595
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Excel VBA - Export data from webpage

    This is the first part, code that clicks the green button:

    Code:
    Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
    
    Sub test()
    Dim IE As Object, htmlDoc As Object, sbv
    Set IE = CreateObject("internetexplorer.application")
    With IE
        .navigate _
        ("https://www.fundsupermart.com.my/fsmone/funds/fund-info/fund-selector/////")
        .Visible = True
    End With
    WaitIE IE, 2000
    Set htmlDoc = IE.document
    Set sbv = htmlDoc.getElementsByClassName("btn btn-success m-r-xs")
    WaitIE IE, 1000
    sbv(0).Click
    End Sub
    
    Sub WaitIE(IE As Object, Optional time As Long = 250)
    Do
        Sleep time
    Loop Until IE.readyState = 4 Or Not IE.Busy
    End Sub
    Last edited by Worf; Apr 21st, 2019 at 08:49 PM.
    Excel 2013 / Windows 8.1 (home)
    Excel 2013 / windows 7 (work)


  4. #4
    New Member
    Join Date
    Apr 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel VBA - Export data from webpage

    Quote Originally Posted by Worf View Post
    Welcome to the Board

    There are many options to be selected on the web page. Do you need the code to make choices there, or just accept the defaults and click the generate button?
    Hi Worf.

    I would like the "Wholesale Funds" section to be selected as "All", while the others to be remained as default.

  5. #5
    Board Regular Worf's Avatar
    Join Date
    Oct 2011
    Location
    Rio, Brazil
    Posts
    3,595
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Excel VBA - Export data from webpage

    The code below clicks the desired radio button. However, doing so causes a popup window to appear, requiring clicks on a check box and a button.
    I will be back soon…

    Code:
    Sub test()
    Dim ie As Object, htmlDoc As Object, btn, radio
    Set ie = CreateObject("internetexplorer.application")
    With ie
        .navigate _
        ("https://www.fundsupermart.com.my/fsmone/funds/fund-info/fund-selector/////")
        .Visible = True
    End With
    WaitIE ie, 2000
    Set htmlDoc = ie.document
    Set btn = htmlDoc.getElementsByClassName("btn btn-success m-r-xs")
    Set radio = htmlDoc.getElementsByClassName _
    ("ng-pristine ng-untouched ng-valid ng-not-empty")
    WaitIE ie, 1000
    DoEvents
    radio(15).Click
    WaitIE ie, 1000
    btn(0).Click
    DoEvents
    End Sub
    Excel 2013 / Windows 8.1 (home)
    Excel 2013 / windows 7 (work)


  6. #6
    Board Regular Worf's Avatar
    Join Date
    Oct 2011
    Location
    Rio, Brazil
    Posts
    3,595
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Excel VBA - Export data from webpage

    This version generates the table on the website, but only the first ten results are shown, next step will be to change the dropdown control to show all results.

    Code:
    Sub test()
    Dim ie As Object, htmlDoc As Object, btn, radio, cbox
    Set ie = CreateObject("internetexplorer.application")
    With ie
        .navigate _
        ("https://www.fundsupermart.com.my/fsmone/funds/fund-info/fund-selector/////")
        .Visible = True
    End With
    WaitIE ie, 2000
    Set htmlDoc = ie.document
    Set btn = htmlDoc.getElementsByClassName("btn btn-success m-r-xs")
    Set radio = htmlDoc.getElementsByClassName _
    ("ng-pristine ng-untouched ng-valid ng-not-empty")
    WaitIE ie, 1000
    DoEvents
    radio(15).Click
    WaitIE ie, 1000
    btn(0).Click
    DoEvents
    Set htmlDoc = ie.document                   ' modal popup
    Set cbox = htmlDoc.getElementsByClassName _
    ("ng-pristine ng-untouched ng-empty ng-invalid ng-invalid-required")
    cbox(0).Click                               ' check box
    WaitIE ie, 1000
    Set btn = htmlDoc.getElementsByClassName("btn btn-success mr-1 ml-1 ng-scope")
    btn(0).Click                                ' continue button
    End Sub
    Excel 2013 / Windows 8.1 (home)
    Excel 2013 / windows 7 (work)


  7. #7
    New Member
    Join Date
    Apr 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel VBA - Export data from webpage

    Quote Originally Posted by Worf View Post
    This version generates the table on the website, but only the first ten results are shown, next step will be to change the dropdown control to show all results.

    Code:
    Sub test()
    Dim ie As Object, htmlDoc As Object, btn, radio, cbox
    Set ie = CreateObject("internetexplorer.application")
    With ie
        .navigate _
        ("https://www.fundsupermart.com.my/fsmone/funds/fund-info/fund-selector/////")
        .Visible = True
    End With
    WaitIE ie, 2000
    Set htmlDoc = ie.document
    Set btn = htmlDoc.getElementsByClassName("btn btn-success m-r-xs")
    Set radio = htmlDoc.getElementsByClassName _
    ("ng-pristine ng-untouched ng-valid ng-not-empty")
    WaitIE ie, 1000
    DoEvents
    radio(15).Click
    WaitIE ie, 1000
    btn(0).Click
    DoEvents
    Set htmlDoc = ie.document                   ' modal popup
    Set cbox = htmlDoc.getElementsByClassName _
    ("ng-pristine ng-untouched ng-empty ng-invalid ng-invalid-required")
    cbox(0).Click                               ' check box
    WaitIE ie, 1000
    Set btn = htmlDoc.getElementsByClassName("btn btn-success mr-1 ml-1 ng-scope")
    btn(0).Click                                ' continue button
    End Sub
    Wow yes, it works!
    Next will be changing the dropdown control to show all results, and import the table into excel...

  8. #8
    Board Regular Worf's Avatar
    Join Date
    Oct 2011
    Location
    Rio, Brazil
    Posts
    3,595
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Excel VBA - Export data from webpage

    This code clicks the drop down control on the page:

    Code:
    Sub Scrapper()
    Dim ie As Object, ddown As Object, op, doc As Object, ip As Object
    Set ie = GetIE      ' get that IE window with the desired dropdown
    Set doc = ie.Document
    Set ddown = doc.getElementsByClassName _
    ("full has-items selectize-input items has-options ng-valid ng-pristine")
    Set ip = ddown(0).getElementsByTagName("input")
    ip(0).Click
    Set ddown = doc.getElementsByClassName("focus")
    Set ip = doc.getElementsByClassName("selectize-dropdown-content")
    Set op = ip(8).getElementsByTagName("div")
    op(0).Click                                 ' All
    End Sub
    
    Function GetIE() As Object
    Dim sa As Object, sw As Object, ieo As Object, objwind, sname$
    Set sa = CreateObject("Shell.Application")
    Set sw = sa.Windows()
    On Error GoTo 0
    For Each objwind In sw
        If (Not objwind Is Nothing) Then
            sname = objwind.Name
            If sname = "Internet Explorer" Then
                Set ieo = objwind
                Exit For
            End If
        End If
    Next
    Set sa = Nothing
    Set GetIE = ieo
    End Function
    Last edited by Worf; Apr 28th, 2019 at 09:03 PM.
    Excel 2013 / Windows 8.1 (home)
    Excel 2013 / windows 7 (work)


  9. #9
    Board Regular Worf's Avatar
    Join Date
    Oct 2011
    Location
    Rio, Brazil
    Posts
    3,595
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Excel VBA - Export data from webpage

    This is the last part, importing the table into Excel. Now it is a matter of joining the code pieces together.

    Code:
    Sub Scrapper()
    Dim ie As Object, doc As Object, t, trows, rnum%, cnum%, c, tcells, r
    Set ie = GetIE      ' get IE window with the full table
    Set doc = ie.Document
    Set t = doc.getElementsByClassName("table table-condensed")
    Set trows = t(0).getElementsByTagName("tr")
    rnum = 1
    cnum = 1
    For Each r In trows
        Set tcells = r.getElementsByTagName("td")
        For Each c In tcells
            Cells(rnum, cnum) = c.innerText
            cnum = cnum + 1
        Next
        rnum = rnum + 1
        cnum = 1
    Next
    End Sub
    Excel 2013 / Windows 8.1 (home)
    Excel 2013 / windows 7 (work)


  10. #10
    New Member
    Join Date
    Apr 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel VBA - Export data from webpage

    Quote Originally Posted by Worf View Post
    This is the last part, importing the table into Excel. Now it is a matter of joining the code pieces together.

    Code:
    Sub Scrapper()
    Dim ie As Object, doc As Object, t, trows, rnum%, cnum%, c, tcells, r
    Set ie = GetIE      ' get IE window with the full table
    Set doc = ie.Document
    Set t = doc.getElementsByClassName("table table-condensed")
    Set trows = t(0).getElementsByTagName("tr")
    rnum = 1
    cnum = 1
    For Each r In trows
        Set tcells = r.getElementsByTagName("td")
        For Each c In tcells
            Cells(rnum, cnum) = c.innerText
            cnum = cnum + 1
        Next
        rnum = rnum + 1
        cnum = 1
    Next
    End Sub
    Wow amazing! Managed to put all the codes together and it works!
    Thanks a million Worf!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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