Pulling a table out of a mess of HTML w/ VBA & Excel

Question

'get the table based on the table’s id
Set ieDoc = ieApp.Document
Set ieTable = ieDoc.all.Item("contentBody")

'copy the tables html to the clipboard and paste to teh sheet
If Not ieTable Is Nothing Then
    Set clip = New DataObject
    clip.SetText "<table>" & ieTable.innerHTML & "</table>"
    clip.PutInClipboard
    Sheet1.Select
    Sheet1.Range("A1").Select
    Sheet1.PasteSpecial "Unicode Text"
End If

I've played with the code above and know there's a better way. the "contentBody" includes:

<div id="contentBody">
<div id="breadcrumb_navigation">
<ul class="nav nav-pills" style="margin-bottom: 0px;">
<h2 class="leftShim">Base Statistics</h2>
<div style="margin: 0px 15px 15px;">
<table class="table table-striped">
</div>
<br/>
<br/>
</div>

All I want out of this is the table that is <table class="table table-stripe">

But because of my lack of knowledge I'm not sure where to start trimming out the excess or if I should try another approach.

For this example the navigation, search and breadcrumb from the website gets pulled into my worksheet. I've grabbed data before using HTML tags, but only pieces of data and never an entire table and I'm currently left here just shrugging.

Daniel Möller · Accepted Answer · 2013-07-08 20:30:29Z

2

Basically you can get items by tag name., like described in this link: http://www.vbaexpress.com/forum/showthread.php?t=31831

Dim HTMLdoc As HTMLDocument
Dim TDelements As IHTMLElementCollection   

Set HTMLdoc = ieApp.Document
Set TDelements = HTMLdoc.getElementsByTagName("table")

And if you want, you can enumerate through the items:

Dim TDelement As HTMLTableCell
For Each TDelement In TDelements
    'code-code-code-code-code
Next

answered Jul 8, 2013 at 20:30

Daniel Möller

86.8k24 gold badges202 silver badges222 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Pulling a table out of a mess of HTML w/ VBA & Excel

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related