dimanche 20 septembre 2015

VBA to get the href value

I am writing VBA to extract the href value, example here is to extract '/listedco/listconews/SEHK/2015/0429/LTN201504291355_C.pdf' from the html code below. The VBA code is attached. Anyone could help? Thanks in advance!

  <a id="ctl00_gvMain_ctl03_hlTitle" class="news" href="/listedco/listconews/SEHK/2015/0429/LTN201504291355_C.pdf" target="_blank">二零一四年年報</a>

Sub Download_From_HKEX()

            Dim internetdata As Object
            Dim div_result As Object
            Dim header_links As Object
            Dim link As Object
            Dim URL As String
            Dim IE As Object
            Dim i As Object
            Dim ieDoc As Object
            Dim selectItems As Variant
            Dim h As Variant


            Dim LocalFileName As String
            Dim B As Boolean
            Dim ErrorText As String
            Dim x As Variant


            'Key Ratios

            For x = 1 To 1579



            Set IE = New InternetExplorerMedium

            IE.Visible = True

            URL = "http://ift.tt/1Yracjc"

            IE.navigate URL

            Do
            DoEvents
            Loop Until IE.readyState = 4

            Application.Wait (Now + TimeValue("0:00:05"))

            Call IE.Document.getElementById("ctl00_txt_stock_code").setAttribute("value", Worksheets("Stocks").Cells(x, 1).Value)




            Set selectItems = IE.Document.getElementsByName("ctl00$sel_tier_1")

            For Each i In selectItems
                        i.Value = "4"
                        i.FireEvent ("onchange")
            Next i



            Set selectItems = IE.Document.getElementsByName("ctl00$sel_tier_2")

            For Each i In selectItems
                        i.Value = "159"
                        i.FireEvent ("onchange")
            Next i



            Set selectItems = IE.Document.getElementsByName("ctl00$sel_DateOfReleaseFrom_d")

            For Each i In selectItems
                        i.Value = "01"
                        i.FireEvent ("onchange")
            Next i


            Set selectItems = IE.Document.getElementsByName("ctl00$sel_DateOfReleaseFrom_m")

            For Each i In selectItems
                        i.Value = "04"
                        i.FireEvent ("onchange")
            Next i


            Set selectItems = IE.Document.getElementsByName("ctl00$sel_DateOfReleaseFrom_y")

            For Each i In selectItems
                        i.Value = "1999"
                        i.FireEvent ("onchange")
            Next i

            Application.Wait (Now + TimeValue("0:00:02"))

            Set ieDoc = IE.Document
                With ieDoc.forms(0)

            Call IE.Document.parentWindow.execScript("document.forms[0].submit()", "JavaScript")
            .submit
            End With

            Application.Wait (Now + TimeValue("0:00:03"))





            'Start here to extract the href value.

            Set internetdata = IE.Document


            Set div_result = internetdata.getElementById("ctl00_gvMain_ctl03_hlTitle")


            Set header_links = div_result.getElementsByClassName("a")



            For Each h In header_links
            Set link = h.ChildNodes.Item(0)
            Worksheets("Stocks").Cells(Range("L" & Rows.Count).End(xlUp).Row + 1, 12) = link.href

            Next                

            Next x
   End Sub




Aucun commentaire:

Enregistrer un commentaire