i try to get some information out of a table within a website by macro to an excel table. usually I just use
ie2.ExecWB 17, 0 '// SelectAll
ie2.ExecWB 12, 2 '// Copy selection
and paste it to any excel sheet which is enough for my needs. but the website was changed. the problem is now, that I need to get the information of website cell with the following code:
<tr class="odd"><td><a href="xxxxxxx">
<img border="0" src="letter.png" title="Titel0" /></a><td></td>
<td><img title="VServer Usage" style="background-color:#00dd00" border="0" src="/ce.png" /> <a
href="testtesttest">blablabla</a></td>
<td><a href="http://maps.google.de/" target="_blank">TEST TEST</a></td>
<td><img border="0" src="/damage.png" title="Titel1"/></a></td>
<td></td><td><img border="0" src="/card.png" title="Titel2"/></a></td>
<td></td><td><img border="0" src="/key.png" title="Titel3" /></a></td>
<td><img border="0" src="/immo.png" title="Titel4" /></a></td>
<td><img border="0" src="/locked.png" title="Titel5" /></a></td><td>101</td>
<td>102</td><td>103</td><td>104</td><td>105</td><td>106</td><td>107</td><td>Name</td>
</tr>
// and then it starts with the next line which has the same structure
by that code I need to get the value for the background color (which can be #00dd00 or #000000 or #ff0000 or #0000ff) and paste it to excel cell B5. then the macro need to get the background color value below the one before and paste it to the cell B6 (and so on and so on).
any idea how to realize that?
Here is my first Idea: I parse through the whole sourcecode, look for a certain string and copy the following charakters with that code:
Dim strCountBody As String
Dim lStartPos As Long
Dim lEndPos As Long
Dim TextIWant As String
Dim Textpos As Integer
Dim searchchar As String
searchchar = "VServer Usage"
Application.ScreenUpdating = False
Set WebBrowser1 = CreateObject("InternetExplorer.Application")
WebBrowser1.Visible = True ' zum testen anzeigen
WebBrowser1.Navigate "wwww.www..www"
While WebBrowser1.readyState <> 4
'Warten, bis Seite geladen ist
DoEvents
Wend
strCountBody = WebBrowser1.Document.body.innerHTML
textpos = InStr(5500, strCountBody, searchchar, vbTextCompare)
TextIWant = Mid(strCountBody, Textpos, 25)
Worksheets("Tabelle1").Range("J1").Value = TextIWant
Worksheets("Tabelle1").Range("K1").Value = Textpos
That seems to work. The positions are correct BUT the search result is only: VServer Usage" border="0", which means that the whole part
style="background-color:#00dd00"
is not found and ignored. I also tried it with:
WebBrowser1.Document.body.innerHTML
WebBrowser1.Document.body.outerHTML
WebBrowser1.Document.body.innerText
WebBrowser1.Document.body.outerText
but all with the same result :(