Excel VBA HTML-Tabelle auslesen

Hallo,
ich möchte eine in einer Email eingebundenen HTML-Tabelle auslesen und in einen Array speichern.
Hierzu habe ich bereits den gesamten Inhalt der Email als string in einer Variable gespeichert.
Nun habe ich aber das Problem die Tabelle und die Informationen daraus herauszufiltern.

Ich benötige die Inhalte der Zellen eigentlich 1:1 in einem Array - bei XML ging das leichter.

 
<table width="1965" style="width:1179pt;margin-left:1.9pt;">  
	<col width="116" style="width:69.8pt;"><col width="805" style="width:483.1pt;"><col width="123" style="width:73.8pt;"><col width="143" style="width:86.1pt;"><col width="131" style="width:78.8pt;"><col width="129" style="width:77.8pt;"><col width="39" style="width:23.8pt;"><col width="476" style="width:285.8pt;">  
	<tbody>
	<tr height="25" style="height:15pt;">  
		<td><font face="Calibri" size="2"><span style="font-size:11pt;">Stand</span></font></td>  
		<td><font face="Calibri" size="2"><span style="font-size:11pt;">28.08.14</span></font></td>  
		<td><font face="Calibri" size="2"><span style="font-size:11pt;"></span></font></td>  
		<td><font face="Calibri" size="2"><span style="font-size:11pt;"></span></font></td>  
		<td><font face="Calibri" size="2"><span style="font-size:11pt;"></span></font></td>  
		<td><font face="Calibri" size="2"><span style="font-size:11pt;"></span></font></td>  
		<td><font face="Calibri" size="2"><span style="font-size:11pt;"></span></font></td>  
		<td><font face="Calibri" size="2"><span style="font-size:11pt;"></span></font></td>  
	</tr>
	<tr height="25" style="height:15pt;">  
		<td style="background-color:#BFBFBF;"><font face="Calibri" size="2"><span style="font-size:11pt;background-color:#BFBFBF;">Fahrzeug</span></font></td>  
		<td style="background-color:#BFBFBF;"><font face="Calibri" size="2"><span style="font-size:11pt;background-color:#BFBFBF;">Kommentar</span></font></td>  
		<td style="background-color:#BFBFBF;"><font face="Calibri" size="2"><span style="font-size:11pt;background-color:#BFBFBF;">Typ</span></font></td>  
		<td style="background-color:#BFBFBF;"><font face="Calibri" size="2"><span style="font-size:11pt;background-color:#BFBFBF;">Verantwortlich</span></font></td>  
		<td style="background-color:#BFBFBF;"><font face="Calibri" size="2"><span style="font-size:11pt;background-color:#BFBFBF;">Status</span></font></td>  
		<td style="background-color:#BFBFBF;"><font face="Calibri" size="2"><span style="font-size:11pt;background-color:#BFBFBF;">Zieltermin</span></font></td>  
		<td style="background-color:#BFBFBF;"><font face="Calibri" size="2"><span style="font-size:11pt;background-color:#BFBFBF;">ID</span></font></td>  
		<td style="background-color:#BFBFBF;"><font face="Calibri" size="2"><span style="font-size:11pt;background-color:#BFBFBF;">Anmerkung</span></font></td>  
	</tr>
	<tr height="25" style="height:15pt;">  
		<td><font face="Calibri" size="2"><span style="font-size:11pt;">Testauto1</span></font></td>  
		<td><font face="Calibri" size="2" color="red"><span style="font-size:11pt;">Kabel reparieren</span></font></td>  
		<td><font face="Calibri" size="2"><span style="font-size:11pt;">Maßnahme</span></font></td>  
		<td><font face="Calibri" size="2"><span style="font-size:11pt;">Mustermann</span></font></td>  
		<td><font face="Calibri" size="2"><span style="font-size:11pt;">offen</span></font></td>  
		<td><font face="Calibri" size="2"><span style="font-size:11pt;">19.09.14</span></font></td>  
		<td><font face="Calibri" size="2"><span style="font-size:11pt;">80</span></font></td>  
		<td><font face="Calibri" size="2"><span style="font-size:11pt;">&nbsp;</span></font></td>  
	</tr>
	<tr height="25" style="height:15pt;">  
		<td><font face="Calibri" size="2"><span style="font-size:11pt;">Testauto2</span></font></td>  
		<td><font face="Calibri" size="2" color="red"><span style="font-size:11pt;">Kabel reparieren</span></font></td>  
		<td><font face="Calibri" size="2"><span style="font-size:11pt;">Maßnahme</span></font></td>  
		<td><font face="Calibri" size="2"><span style="font-size:11pt;">Mustermann</span></font></td>  
		<td><font face="Calibri" size="2"><span style="font-size:11pt;">offen</span></font></td>  
		<td><font face="Calibri" size="2"><span style="font-size:11pt;">19.09.14</span></font></td>  
		<td><font face="Calibri" size="2"><span style="font-size:11pt;">81</span></font></td>  
		<td><font face="Calibri" size="2"><span style="font-size:11pt;">&nbsp;</span></font></td>  
	</tr>
</tbody>
</table>

Könnt ihr mir hierbei etwas auf die Sprünge helfen?
Das Ergebnis sollte wie folgt aussehen:

 
arr(0, 0) = Fahrzeug
arr(0, 1) = Kommentar
arr(0, 2) = Typ
arr(0, 3) = Verantwortlich
arr(0, 4) = Status
arr(0, 5) = Zieltermin
arr(0, 6) = ID
arr(0, 7) = Anmerkung

arr(1, 0) = Testauto1
arr(1, 1) = Kabel reparieren
arr(1, 2) = Maßnahme
arr(1, 3) = Mustermann
arr(1, 4) = offen
arr(1, 5) = 19.09.14
arr(1, 6) = 80
arr(1, 7) = ""  

arr(2, 0) = Testauto2
arr(2, 1) = Kabel reparieren
arr(2, 2) = Maßnahme
arr(2, 3) = Mustermann
arr(2, 4) = offen
arr(2, 5) = 19.09.14
arr(2, 6) = 81
arr(2, 7) = ""  

danke Feder

Please also mark the comments that contributed to the solution of the article

Content-Key: 248591

Url: https://administrator.de/contentid/248591

Printed on: April 20, 2024 at 02:04 o'clock

2 Comments

Latest comment

Hallo Feder,
in diesem Beispiel ist dein HTML-Schnippsel in der Variablen strContent hinterlegt. Die erste Zeile der Tabelle wird wie gewünscht nicht mit ins Array übernommen

Dim oDom As Object, arr as Variant
Set oDom = CreateObject("htmlfile")  
oDom.body.innerHTML = strContent
    
With oDom.getElementsByTagName("table")(0)  
   ReDim arr(0 To .Rows.Length - 2, 0 To .Rows(1).Cells.Length - 1)
   For r = 1 To .Rows.Length - 1
       For c = 0 To .Rows(r).Cells.Length - 1
            arr(r - 1, c) = .Rows(r).Cells(c).innerText
       Next
   Next
End With

In arr befinden sich dann deine gewünschten Daten.

Grüße Uwe

Zur Info: So läuft das nur in VBA für VBS müsste man es so abändern.

VBS-Variante

Dim oDom, arr
Set oDom = CreateObject("htmlfile")  
oDom.write strContent
oDom.Close

With oDom.getElementsByTagName("table")(0)  
   ReDim arr(0 To .Rows.Length - 2, 0 To .Rows(1).Cells.Length - 1)
   For r = 1 To .Rows.Length - 1
       For c = 0 To .Rows(r).Cells.Length - 1
            arr(r - 1, c) = .Rows(r).Cells(c).innerText
       Next
   Next
End With

Hallo Uwe,

genau das ist das was mir weiterhilft! Vielen Dank!

gruß feder

German solved Question VB for Applications Development

Hotly discussed

Check of ZFW Firewallgleixnerd - 3 Comments

How to set up and configure a Linux GRE tunnelAlexWisha - 3 Comments

WIREGUARD VPN ON UDM PRO BEHIND FRITZBOX - HANDSHAKE DID NOT COMPLETEjstricker - 3 Comments

End of Support dates for Office 2016, 2019 Apps und Productivity ServersDani - 1 Comment