Share your coding samples or ask questions

Question Pull HTML Table Data into excel

  • Tyger0951
  • Topic Author
  • Visitor
  • Visitor
8 years 3 months ago - 8 years 3 months ago #418 by Tyger0951
Pull HTML Table Data into excel was created by Tyger0951
Need some help. Have been able to use below code to open and grab all the data I need from a webpage but its putting the table in text form into one cell instead of breaking into each cell like a table and I can't seem to have any luck getting the data to separate. I want to be able to get all the table data into excel as though it was copy and pasted basically or extract several key peices into specific cells.
Code:
Dim objIe As Object Set objIe = CreateObject("InternetExplorer.Application") objIe.Visible = True objIe.navigate "http://cctools/reports2/main.php" While (objIe.Busy Or objIe.readyState <> 4): DoEvents: Wend objIe.document.getElementById("submit").Click Set xobj = objIe.document.getElementById("reportOut") Sheet2.Range("A1") = xobj.innerText Set xobj = Nothing objIe.Quit Set objIe = Nothing End Sub
I've tried :
Code:
Sub Sample() Dim objIe As Object Dim TDelements As IHTMLElementCollection Dim TDelement As HTMLTableCell Set objIe = CreateObject("InternetExplorer.Application") objIe.Visible = True objIe.navigate "http://cctools/reports2/main.php" While (objIe.Busy Or objIe.readyState <> 4): DoEvents: Wend objIe.document.getElementById("submit").Click Set xobj = objIe.document.getElementById("reportOut") Sheet2.Range("A1") = xobj.innerText r = 0 For Each TDelement In TDelements 'Look for required TD elements - this check is specific to VBA Express forum - modify as required If TDelement.className = "data data3" Then Sheet2.Range("A1").Offset(r, 0).Value = TDelement.innerText r = r + 1 End If Next Set xobj = Nothing objIe.Quit Set objIe = Nothing End Sub
And get a

Run Time error '424' object required possiblycause code doesn'tlike the classname method being used.


I added in:
Code:
Set TDelements = xobj.innerText




and still get same 424 error.

Here is the HTML code from the data I'm trying to pull
Code:
<div id="reportOut"> <script type="text/javascript"></script> <h2></h2> <div class="bold font_2 large black"></div> <table class="data data3" cellspacing="0"> <tbody> <tr class="altrow"></tr> <tr> <td></td> <td></td> <td></td> <td></td> <td></td> <td></td> <td></td> <td></td> <td> 81.52%
I'm trying to pull all table data or just extract the 81.52%
Last edit: 8 years 3 months ago by Tyger0951.

Please Log in or Create an account to join the conversation.

More
8 years 3 months ago #419 by roller
A bit hard for me to work out what is going wrong without having the the Excel file in front of me but can I suggest a different way to go about this - it might be simpler and saves you some VBA coding.

Go to the Excel Ribbon>Data and from the Get external data section first from the left choose From Web, enter your URL and check out the Options menu there are few things you can do like getting full HTML or Rich Text, etc...

Try this method see if it is easier. Maybe also record a macro while setting this up so you can use part of it in your VBA.

try that and see if this helps.

Please Log in or Create an account to join the conversation.

  • Tyger0951
  • Topic Author
  • Visitor
  • Visitor
8 years 3 months ago - 8 years 3 months ago #420 by Tyger0951
Replied by Tyger0951 on topic Pull HTML Table Data into excel
Normally would do that method but the url is not dynamic at all. So have to navigate then input parameters click run then data appears. Website doesn't allow &"text"= type inputs for url. Its not cool. :-(. So this is only other option than copy and pasting whole site then pasting into excel. And really want to get the html way to work
Code:
appIE.ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DODEFAULT appIE.ExecWB OLECMDID_COPY, OLECMDEXECOPT_DODEFAULT appIE.Quit Range("A1").Select ActiveSheet.Paste

Works but annoying cause have to manipulate the data more in the sheets. And if something gets added to header and not the table could mess up cell reference.
Last edit: 8 years 3 months ago by Tyger0951.

Please Log in or Create an account to join the conversation.

More
8 years 3 months ago #421 by roller
OK I am driving blind here but try addgin the " While (objIe.Busy Or objIe.readyState <> 4): DoEvents: Wend" after each click event. Maybe the error related to the Class ID is there before the document is not READY yet and have not rendered the full HTML before your VBA line start looking for Set xobj = objIe.document.getElementById("reportOut")

Also it is worth noting that innerText property doesn't not work it all browsers, I think it works in IE though.

If still no luck send me the files and URLs if possible by your work and I will check out on Monday.

Please Log in or Create an account to join the conversation.

  • Tyger0951
  • Topic Author
  • Visitor
  • Visitor
8 years 3 months ago - 8 years 3 months ago #422 by Tyger0951
Replied by Tyger0951 on topic Pull HTML Table Data into excel
Set xobj = objIe.document.getElementById("reportOut")

Grabs everything. Cause

Set xobj = objIe.document.getElementById("reportOut")
Sheet2.Range("A1") = xobj.innerText

works to paste everything into one cell. Just the one cell looks like as though i pasted it into a txt file. All info jammed in one cell. Want that table being pasted broken into cells like a normal table.
Last edit: 8 years 3 months ago by Tyger0951.

Please Log in or Create an account to join the conversation.

More
8 years 3 months ago #423 by roller
OK I get you, can you paste in here the full HTML that you are gettting all in on cells, let's see if there is a pattern that we can use the Len function to pick the value you need. I am sure there are more than one way but let's try this. Paste all the cell content in a post and tell me which value you want to pull out.

Please Log in or Create an account to join the conversation.

Time to create page: 0.565 seconds
Powered by Kunena Forum