使用 VBA 动态创建 HTML 表格行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/41886251/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Dynamically create HTML table rows with VBA
提问by Kristen
I am using VBA to create a table in an outlook email. I have figured out how to generate the table, but my problem is that I need to dynamically adjust the number of rows in the table. For some emails, there will be two rows of data, for others there will be three, etc.
我正在使用 VBA 在 Outlook 电子邮件中创建一个表。我已经弄清楚如何生成表格，但我的问题是我需要动态调整表格中的行数。对于某些电子邮件，将有两行数据，对于其他电子邮件，将有三行，依此类推。
In the code below rowstocontactis a Collection. I know I want to loop through the Collectionand add a row for each item in the collection, but I can't figure out how to insert a loop within the html code that is creating the table.
在下面的代码中rowstocontact是一个Collection. 我知道我想遍历Collection并为集合中的每个项目添加一行，但我不知道如何在创建表的 html 代码中插入一个循环。
Any help is greatly appreciated!! Thanks.
任何帮助是极大的赞赏！！谢谢。
bodytext = "<head><style>table, th, td {border: 1px solid gray; border-collapse:" & _
"collapse;}</style></head><body>" & _
"<table style=""width:60%""><tr>" & _
"<th bgcolor=""#bdf0ff"">Reviewee</th>" & _
"<th bgcolor=""#bdf0ff"">Manager(s)</th>" & _
"<th bgcolor=""#bdf0ff"">Project code</th>" & _
"<th bgcolor=""#bdf0ff"">Requested</th>" & _
"<th bgcolor=""#bdf0ff"">Type</th>" & _
"<th bgcolor=""#bdf0ff"">Due</th></tr><tr>" & _
"<td ""col width=10%"">" & Range("D" & rowtocontact(1)) & "</td>" & _
"<td ""col width=10%"">" & Range("L" & rowtocontact(1)) & "</td>" & _
"<td ""col width=10%"">" & Range("M" & rowtocontact(1)) & "</td>" & _
"<td ""col width=10%"">" & Range("AJ" & rowtocontact(1)) & "</td>" & _
"<td ""col width=10%"">" & Range("V" & rowtocontact(1)) & "</td>" & _
"<td ""col width=10%"">" & Range("AK" & rowtocontact(1)) & "</td>" & _
"<td ""col width=10%"">" & Range("AK" & rowtocontact(1)) & "</td>" & _
"</tr></Table></body>"
回答by Robin Mackenzie
You need to split the HTML into 3 parts and have a string variable for each:
您需要将 HTML 分成 3 部分，并为每个部分设置一个字符串变量：
- Everything before the rows
- The rows
- Everything after the rows
- 行之前的所有内容
- 行
- 行之后的所有内容
In the second section of code you can iterate through the collection (of row references) and dynamically build the table by adding <tr>...</tr>blocks for as many items as there are in your Collection.
在代码的第二部分中，您可以遍历（行引用的）集合并通过<tr>...</tr>为Collection.
After setting the string for 'everything after the rows' you then concatenate all three parts together to get the final HTML string.
在为“行之后的所有内容”设置字符串后，您将所有三个部分连接在一起以获得最终的 HTML 字符串。
Here is the sample code - note I added a worksheet reference (ws) as a best practice and also dropped off the final <td>per row as it seemed like a duplicate of the value on column AKfor which you did not have a header. You can adjust as suits anyway:
这是示例代码 - 请注意，我添加了一个工作表引用 ( ws) 作为最佳实践，并且还删除了最后的<td>每行，因为它看起来像是AK您没有标题的列上的值的重复项。无论如何，您可以根据需要进行调整：
Option Explicit
Sub CreateEmailHtml()
Dim ws As Worksheet
Dim coll As New Collection
Dim lngCounter As Long
Dim strBeforeRows As String
Dim strRows As String
Dim strAfterRows As String
Dim strAll As String
' get a worksheet reference
Set ws = Sheet1
' test collection
coll.Add 2
coll.Add 4
coll.Add 6
' HTML before rows
strBeforeRows = "<head><style>table, th, td {border: 1px solid gray; border-collapse:" & _
"collapse;}</style></head><body>" & _
"<table style=""width:60%""><tr>" & _
"<th bgcolor=""#bdf0ff"">Reviewee</th>" & _
"<th bgcolor=""#bdf0ff"">Manager(s)</th>" & _
"<th bgcolor=""#bdf0ff"">Project code</th>" & _
"<th bgcolor=""#bdf0ff"">Requested</th>" & _
"<th bgcolor=""#bdf0ff"">Type</th>" & _
"<th bgcolor=""#bdf0ff"">Due</th></tr>"
' iterate collection
strRows = ""
For lngCounter = 1 To coll.Count
strRows = strRows & "<tr>"
strRows = strRows & "<td ""col width=10%"">" & ws.Range("D" & coll(lngCounter)).Value & "</td>"
strRows = strRows & "<td ""col width=10%"">" & ws.Range("L" & coll(lngCounter)).Value & "</td>"
strRows = strRows & "<td ""col width=10%"">" & ws.Range("M" & coll(lngCounter)).Value & "</td>"
strRows = strRows & "<td ""col width=10%"">" & ws.Range("AJ" & coll(lngCounter)).Value & "</td>"
strRows = strRows & "<td ""col width=10%"">" & ws.Range("V" & coll(lngCounter)).Value & "</td>"
strRows = strRows & "<td ""col width=10%"">" & ws.Range("AK" & coll(lngCounter)).Value & "</td>"
strRows = strRows & "</tr>"
Next lngCounter
' HTML after rows
strAfterRows = "</table></body>"
' final HTML - concatenate the 3 string variables
strAll = strBeforeRows & strRows & strAfterRows
Debug.Print strAll
End Sub
So, given this sample data:
因此，鉴于此示例数据：
You get this HTML as an output - it is formatted nicely through use of the Tidybutton in the stack snippet editor for readability:
你得到这个 HTML 作为输出 - 它通过使用Tidy堆栈片段编辑器中的按钮很好地格式化以提高可读性：
<head>
<style>
table,
th,
td {
border: 1px solid gray;
border-collapse: collapse;
}
</style>
</head>
<body>
<table style="width:60%">
<tr>
<th bgcolor="#bdf0ff">Reviewee</th>
<th bgcolor="#bdf0ff">Manager(s)</th>
<th bgcolor="#bdf0ff">Project code</th>
<th bgcolor="#bdf0ff">Requested</th>
<th bgcolor="#bdf0ff">Type</th>
<th bgcolor="#bdf0ff">Due</th>
</tr>
<tr>
<td "col width=10%">foo2</td>
<td "col width=10%">bar2</td>
<td "col width=10%">baz2</td>
<td "col width=10%">quux2</td>
<td "col width=10%">qux2</td>
<td "col width=10%">quuux2</td>
</tr>
<tr>
<td "col width=10%">foo2</td>
<td "col width=10%">bar2</td>
<td "col width=10%">baz2</td>
<td "col width=10%">quux2</td>
<td "col width=10%">qux2</td>
<td "col width=10%">quuux2</td>
</tr>
<tr>
<td "col width=10%">foo6</td>
<td "col width=10%">bar6</td>
<td "col width=10%">baz6</td>
<td "col width=10%">quux6</td>
<td "col width=10%">qux6</td>
<td "col width=10%">quuux6</td>
</tr>
</table>
</body>HTH
HTH
回答by Dumitru Daniel
I fixed the issue with a workaround by resizing the range to include 1 cell outside the table, to an unformatted zone:
我通过调整范围大小以将表格外的 1 个单元格包含到未格式化的区域来解决此问题：
With Log_03.Range("VBA_rng_email")
Set RNG = Union(.Resize(iRow), .Offset(.Rows.Count).Resize(1))
End With
strbody = strbody & RangetoHTML(RNG) & "<br>"
- Where iRow is the number if rows from the table i want to grab
- iRow 是我想从表中获取的行数
![vba [SheetName][PivotTableName] 中已有数据”。要替换吗？](/res/img/loading.gif)