VBA loop through Webpage Tables

[JimboP77]

Hi all

Can someone please give me some simple VBA code that loops through all the tables in a webpage?

I would like to the debug.print the contents of the tables.

Thanks in advance.

Regards

Jim

 

[JimboP77]

Anyone?

 

[Anthony47]

One of my typical answer:

Sub GetTabbbSub(ByVal myURL As String)
'need to receive the target Url
'
Set IE = CreateObject("InternetExplorer.Application")
   
With IE
    .navigate myURL
    .Visible = True
    Do While .Busy: DoEvents: Loop    'Attesa not busy
    Do While .readyState <> 4: DoEvents: Loop 'Attesa documento
End With
'
myStart = Timer  'attesa addizionale
Do
    DoEvents
    If Timer > myStart + 1 Or Timer < myStart Then Exit Do
Loop

'Write tables on the ActiveSheet
Set myColl = IE.document.getElementsByTagName("TABLE")
For Each myItm In myColl
    Cells(I + 1, 1) = "Table# " & ti + 1
    ti = ti + 1: I = I + 1
    For Each trtr In myItm.Rows
        For Each tdtd In trtr.Cells
            Cells(I + 1, j + 1) = tdtd.innerText
            j = j + 1
        Next tdtd
        I = I + 1: j = 0
DoEvents
    Next trtr
I = I + 1
Next myItm
'
''Stop    'SEE TEXT
'Chiusura IE
IE.Quit
Set IE = Nothing
End Sub

Then you shall call GetTabbbSub passing it the target Url and also managing the sheet to be written; For example:

Sub call1()
    Sheets("Foglio1").Select       '<<< Which sheet will be used for the tables
    Cells.ClearContents            '<<< Clear the sheet before read new tables?
    Call GetTabbbSub("http://www.sisal.it/virtual-race/archivio-gare")		'<<< URL to be read
    Cells.WrapText = False         'Suggested
'Other web pages (on other worksheet)??
'
'
End Sub

Bye

 

[JimboP77]

Thanks Anthony.

So how would I loop through to find the "Yes" in "Bank Details Supplied" for the below html?

                                                                                                                                                                                                                                                                    Student NameXXX, XXXXDomicileXXXXXCorrespondence AddressXXXXXX XXXXCustomer Reference NumberXXXXXXXXXXXSSNXXXXXXXXXXXXXBank Details SuppliedYesCityXXXXXXXXXFailed PaymentNoCountyPostcodeXXXX XXXXDate of Birth00/00/0000Sex

XXXXXX    Home Tel No00000000000Email AddressXXXX@XXXXX.XX.XXMobile Tel No0000000000000

</form>

 

[JimboP77]

<input type="hidden" name="service" value="direct/1/XXXXXdetails:Home/student_detls_form">Apologies - how do I post html code without it converting to html?!
<input type="hidden" name="sp" value="S0">
<input type="hidden" name="Form0" value="goBackLink">

<!-- ------------------------------ Main Detail 1 ---------------------------------- /-->












































































[TABLE="class: main, width: 818"]
<tbody>[TR]
[TH="class: tableheaders, width: 155"]Student Name[/TH]
[TD="class: tableheaders, width: 254"]XXX, XXXX[/TD]
[TH="class: tableheaders, width: 155"]Domicile[/TH]
[TD="class: tableheaders, width: 254"]XXXXX[/TD]
[/TR]
[TR]
[TH="class: tableheaders"]Correspondence Address[/TH]
[TD="class: tableheaders"]XXXXXX XXXX[/TD]
[TH="class: tableheaders"]Customer Reference Number[/TH]
[TD="class: tableheaders"]XXXXXXXXXXX[/TD]
[/TR]
[TR]
[TH][/TH]
[TD="class: tableheaders"][/TD]
[TH="class: tableheaders"]SSN[/TH]
[TD="class: tableheaders"]XXXXXXXXXXXXX[/TD]
[/TR]
[TR]
[TH][/TH]
[TD="class: tableheaders"][/TD]
[TH="class: tableheaders"]Bank Details Supplied[/TH]
[TD="class: tableheaders"]Yes[/TD]
[/TR]
[TR]
[TH="class: tableheaders"]City[/TH]
[TD="class: tableheaders"]XXXXXXXXX[/TD]
[TH="class: tableheaders"]Failed Payment[/TH]
[TD="class: tableheaders"]No[/TD]
[/TR]
[TR]
[TH="class: tableheaders"]County[/TH]
[TD="class: tableheaders"][/TD]
[TH="class: tableheaders"][/TH]
[TD="class: tableheaders"][/TD]
[/TR]
[TR]
[TH="class: tableheaders"]Postcode[/TH]
[TD="class: tableheaders"]XXXX XXXX[/TD]
[TH="class: tableheaders"][/TH]
[TD="class: tableheaders"][/TD]
[/TR]
[TR]
[TH="class: tableheaders"]Date of Birth[/TH]
[TD="class: tableheaders"]00/00/0000[/TD]
[TH="class: tableheaders"][/TH]
[TD="class: tableheaders"][/TD]
[/TR]
[TR]
[TH="class: tableheaders"]Sex[/TH]
[TD="class: tableheaders"]


XXXXXX
[/TD]
[TH="class: tableheaders"][/TH]
[TD="class: tableheaders"][/TD]
[/TR]
[TR]
[TH="class: tableheaders"]Home Tel No[/TH]
[TD="class: tableheaders"]00000000000[/TD]
[TH="class: tableheaders"]Email Address[/TH]
[TD="class: tableheaders"]XXXX@XXXXX.XX.XX[/TD]
[/TR]
[TR]
[TH="class: tableheaders"]Mobile Tel No[/TH]
[TD="class: tableheaders"]0000000000000[/TD]
[TH="class: tableheaders"][/TH]
[TD="class: tableheaders"][/TD]
[/TR]
[TR]
[TD="colspan: 4"] [/TD]
[/TR]
</tbody>[/TABLE]

 

[Anthony47]

Probably I missed your initial question, and what I suggested will import for you all the tables available on a web page. Now that all your data are there you can use "conventional" formulas or macro to search what you need.

It is also unclear to me if you need reading data from the web page or writing something using a submit form. Which is your case?

Anyway developing a code that point to specific information on the page (either to read or to write) requires the html source code (for simple static, ie no scripted pages) or accessing the web page (to exploit the "inspecting" facility of the browser).

Bye

 

[JimboP77]

Hi Anthony

I just need to iterate through the table to on the webpage to see if the "Yes" exists.

Is this possible?

James

 

[Anthony47]

As I sayd, access the field directly into the web page requires the html code and (in many cases) accessing the wep page itself? Can you share the url?

Or you import the table(s) to an Excel sheet (this is what Sub GetTabbbSub does) and check there (in Excel) if the field contains Yes or not. Is this concept clear? Is it applicable to your data?

Bye

 

[JimboP77]

Hi Anthony

How do I post the html in the forum? When I tried before it converted it!

I cannot give access to the url as it is a secure site.

I do not wish to import the data into Excel.

Thanks

James

 

[Anthony47]

Try using the "code tag" (#)
But you should first confirm that the page is static, ie without scripts
And anyway readyng the cde without having the possibility of using the "inspecting" facility of the browser is a time consuming process, maybe you should use the methond that you don't wish to use.

Bye