Excel VBA - Export data from webpage

[Anonymous Chan]

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/fsmone/funds/fund-info/fund-selector/////

Thanks.

 

[Worf]

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?

 

[Worf]

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

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

 

[Anonymous Chan]

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.

 

[Worf]

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…

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

 

[Worf]

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.

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

 

[Anonymous Chan]

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.

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...

 

[Worf]

This code clicks the drop down control on the page:

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

 

[Worf]

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

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

 

[Anonymous Chan]

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

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!