Excel VBA - Export data from webpage

Anonymous Chan

New Member
Joined
Apr 19, 2019
Messages
4
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.
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
3,942
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

Well-known Member
Joined
Oct 30, 2011
Messages
3,942
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:

Anonymous Chan

New Member
Joined
Apr 19, 2019
Messages
4
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

Well-known Member
Joined
Oct 30, 2011
Messages
3,942
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
 

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
3,942
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
 

Anonymous Chan

New Member
Joined
Apr 19, 2019
Messages
4
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...
 

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
3,942
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:

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
3,942
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
 

Anonymous Chan

New Member
Joined
Apr 19, 2019
Messages
4
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,096,248
Messages
5,449,242
Members
405,560
Latest member
Jadax

This Week's Hot Topics

Top