如何用VBA宏将背景颜色从HTML导入Excel [英] How to import background color from HTML to Excel with VBA Macro

问题描述

我试图从一个网站的表格中通过宏到Excel表格获取一些信息。通常我只是使用

  ie2.ExecWB 17，0'// SelectAll 
 ie2.ExecWB 12，2'/ /复制选择
  

并将其粘贴到任何满足我需要的Excel表单中。但网站改变了。现在的问题是，我需要使用以下代码获取网站单元格的信息：

 < tr class =奇怪>< td>< a href =xxxxxxx> 
< img border =0src =letter.pngtitle =Titel0/>< / a>< td>< / td> 
< td>< img title =VServer Usagestyle =background-color：＃00dd00border =0src =/ ce.png/>& nbsp;< a 
 href =testtesttest> blablabla< / a>< / td> 
< td>< a href =http://maps.google.de/target =_ blank> TEST TEST< / a>< / td> 
< td>< img border =0src =/ damage.pngtitle =Titel1/>< / a>< / td> 
< td>< / td>< td>< img border =0src =/ card.pngtitle =Titel2/>< / a>< / td> ; 
< td>< / td>< td>< img border =0src =/ key.pngtitle =Titel3/>< / a>< / td> ; 
< td>< img border =0src =/ immo.pngtitle =Titel4/>< / a>< / td> 
将TD>< IMG BORDER = 0 SRC = / locked.png 标题= 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>名称< / TD> 
< / tr> 
 
 //然后它从具有相同结构的下一行开始
  

<通过该代码，我需要获取背景颜色（可以是＃00dd00或＃000000或＃ff0000或＃0000ff）的值并粘贴到excel单元格B5。那么宏需要获得之前的背景颜色值并粘贴到单元格B6（等等）。

任何想法如何实现？

这是我的第一个想法：我解析整个源代码，

  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 
 
 
设置WebBrowser1 = CreateObject（InternetExplorer.Application）
 WebBrowser1.Visible = True'zum testen anzeigen 
 WebBrowser1.Navigatewwww.www..www
虽然WebBrowser1.readyState<> 4 
Warten，双Seite geladen IST 
的DoEvents 
 WEND 
 
 strCountBody = WebBrowser1.Document.body.innerHTML 
 textpos = InStr函数（5500，strCountBody ，searchchar，vbTextCompare）
 
 TextIWant = Mid（strCountBody，Textpos，25）
 
工作表（Tabelle1）。Range（J1）。Value = TextIWant 
工作表（Tabelle1）。范围（K1）。Value = Textpos 
  

上班。位置是正确的，但搜索结果只是：VServer用法border =0，表示整个部分 style =background-color：＃00dd00

未找到并被忽略。 ：

  WebBrowser1.Document.body.innerHTML 
 WebBrowser1.Document.body.outerHTML 
 WebBrowser1.Document .body.innerText 
 WebBrowser1.Document.body.outerText 
  

但所有结果都一样：

解决方案

类似于（未测试）：

  Dim tbl，rw 
 
对于ie.document.getElementsByTagName（table）中的每个tbl 
对于tbl.body.rows中的每个rw 
 
 debug.print rw.cells（2）.getElementsByTagName（img）（1）.style.backgroundColor 
 
 Next rw 
 Next tbl 
  

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" />&nbsp;<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 :(

解决方案

Something like (untested):

Dim tbl, rw

For Each tbl in ie.document.getElementsByTagName("table")
    For Each rw in tbl.body.rows

       debug.print rw.cells(2).getElementsByTagName("img")(1).style.backgroundColor

    Next rw
Next tbl

这篇关于如何用VBA宏将背景颜色从HTML导入Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！