如何使用 vba 读取粘贴在 Outlook 邮件正文中的表格？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8061350/
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
How to read table pasted in outlook message body using vba?
提问by kinkajou
I need to read table of data as in picure using vba. I used Msg.Bodyto read the body text but actually i need to find first row as header and rest as data field and update DBMS table accordingly.So is it possible to read the table as I would in excel?
我需要使用 vba 读取图片中的数据表。我使用Msg.Body读取正文，但实际上我需要找到第一行作为标题，其余作为数据字段并相应地更新 DBMS 表。那么是否可以像在 excel 中那样读取该表？
回答by JimmyPena
This sample procedure should help. I recreated your table in Excel, pasted it into an Outlook email and sent it to myself. Then I used this procedure to read the "cell" values.
此示例程序应该会有所帮助。我在 Excel 中重新创建了您的表格，将其粘贴到 Outlook 电子邮件中并发送给我自己。然后我使用这个程序来读取“单元格”值。
Sub GetLines()
Dim msg As Outlook.mailItem
Dim rows As Variant
Dim numberofColumns As Long
Dim numberofRows As Long
Dim headerValues As Variant
Dim headerRow() As String
Dim data() As String
Dim i As Long, j As Long
' get currently selected email
Set msg = ActiveExplorer.Selection.item(1)
' tokenize each line of the email
rows = Split(msg.Body, vbCrLf)
' calculate array size
numberofColumns = Len(rows(0)) - Len(Replace(rows(0), Chr(9), ""))
numberofRows = UBound(rows) + 1
' put header row into array
ReDim headerRow(1 To numberofColumns)
headerValues = Split(rows(0), Chr(9))
For i = 1 To numberofColumns
headerRow(i) = Trim$(headerValues(i - 1))
Next i
' calculate data array size
numberofRows = numberofRows - 1
' put data into array
ReDim data(1 To numberofRows, 1 To numberofColumns)
For i = 1 To numberofRows
For j = 1 To numberofColumns
data(i, j) = Trim$(Split(rows(i), Chr(9))(j - 1))
Next j
Next i
End Sub
First we tokenize each line of the email into an array. We calculate the array size, then create an array to hold just the first line of the table (the "header").
首先，我们将电子邮件的每一行标记为一个数组。我们计算数组大小，然后创建一个数组来保存表格的第一行（“标题”）。
Then we subtract one from the row count because we are going to skip the header row. We then loop through each row, split it and loop through its values, assigning them to our 2D array as we go.
然后我们从行数中减去一，因为我们将跳过标题行。然后我们遍历每一行，拆分它并遍历它的值，在我们进行时将它们分配给我们的 2D 数组。
In the end, the variable "headerRow" can be iterated to retrieve the field values you want to use for your DBMS. The variable "data" contains only the values corresponding to each field. So headerRow(1) and data(n,1) should correspond to the values in the first column of your table.
最后，可以迭代变量“headerRow”以检索要用于 DBMS 的字段值。变量“data”仅包含与每个字段对应的值。因此 headerRow(1) 和 data(n,1) 应该对应于表第一列中的值。
