How can I get the table from this web page?

Hi all,

I have a task to download a table from this web page (http://sdinotice.hkex.com.hk/di/NSAl...=MAIN&lang=EN&). I know the html code for the table should be something like
(<table cellspacing="0" cellpadding="0" align="Center" rules="all" border="1" id="grdPaging" style="width:970px;border-collapse:collapse;">
<tr class="boldtxtw" valign="top" style="background-color:#999999;">
<td>Name of substantial shareholder / director / chief executive</td><td>Reason for disclosure </td><td>No. of shares bought / sold / involved</td><td>Average price per share</td><td>No. of shares interested (See *Notes above)</td><td>% of issued share capital</td><td>Date of relevant event (dd/mm/yyyy)</td><td>Interests in shares of associated corporation</td><td>Interests in debentures</td>)
As there are several "table" in the whole html code, I would like to use getelementsbytagname("table") first and then look for id by searching getelementbyid("grdPaging") to download the table only. But I don't know how to write this command in vba code. Could anyone shows me a direction please? Thanks in advance.

Thanks a lot Karedog, but is it possible to have a table format like the one shown on the webpage at excel?

Originally Posted by karedog

Hi,

Since the id of an html element should be unique, so you can directly using getElementById() function.

You can try this code :

Please Login or Register  to view this content.

Thanks a lot Karedog, but is it possible to have a table format like the one shown on the webpage at excel?

Originally Posted by karedog

Hi,

Since the id of an html element should be unique, so you can directly using getElementById() function.

You can try this code :

Please Login or Register  to view this content.

Thanks Marc L, but I cannot run the code. Do I need to do any settings on excel in order to run it?

Originally Posted by Marc L

Hi,

if you need to copy the Web table to a worksheet, paste this code directly in a worksheet module :

Please Login or Register  to view this content.

Do you like ? So thanks to click on bottom left star « Add Reputation » !

Works like a breeze on my side on several computers with differents Excel & Windows versions !

If you can run karedog's code, you can run mine !

Hi Marc L,

seem error comes in this code (Me.Paste [C1]: .ParentWindow.clipboardData.clearData "Text")

Originally Posted by Marc L

Works like a breeze on my side on several computers with differents Excel & Windows versions !

If you can run karedog's code, you can run mine !

Did you ever follow my direction before the code ?! Where did you paste it ?!

Oops.... I made a mistake to paste in a wrong place. It works now. Thank you so much

Originally Posted by Marc L

Did you ever follow my direction before the code ?! Where did you paste it ?!

karetog, faster by pasting the table's outerHTML code in a HTMLFile object
and reading its elements like in your code from this object … Piloting IE is the slowest !

My suggestion was pasting table code from IE to an HTMLFile object, faster than processing only in IE …

Hi Karedog, can I set the URL as a variant since the link I gave in the example was a searched result that varies depending on the search target? Thanks

Originally Posted by karedog

Hi, sorry for the late. Thanks for the reputation point.

I see that Mark's code have already served you well, but in case you need Internet Explorer object for the solution (IE object has GUI and can be shown if needed, but slower than Mark's code that is using XML object), here is the code :

Please Login or Register  to view this content.

Hi Marc L, I got a Run-time error 429, ActiveX component can't create object in using your code in my home pc which is operating windows 8.1 and MS Office 2010 (32 bit). But the code works well in another pc with windows xp and MS Office 2010 (32 bit).

Originally Posted by Marc L

Hi,

if you need to copy the Web table to a worksheet, paste this code directly in a worksheet module :

Please Login or Register  to view this content.

Do you like ? So thanks to click on bottom left star « Add Reputation » !

Depends on which line error occurs !

Seems something is missing on that computer or maybe administrator mode is required, no permissions allowed …

Hi Karedog, May I know how could I remain the hyperlink rather than text in the table, for example in the column of "reason for disclosure"? Many thanks

Originally Posted by karedog

Hi lubbamkt,
Sure you can, just don't forget to put the variable name in Dim statement, since the code is marked as Option Explicit. You also can show the IE window during the process so you know how it progessing.

Please Login or Register  to view this content.

Example in this thread …