vba 如何从多行/列 Excel 电子表格生成格式化的 Word 报告
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13018627/
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 do I generate a formatted Word report from multi-row/column Excel spreadsheet
提问by user1766409
I'm trying to auto-build a formatted Word report from an Excel template used by multiple teams. For example, if i have the following Excel structure:
我正在尝试从多个团队使用的 Excel 模板自动构建格式化的 Word 报告。例如，如果我有以下 Excel 结构：
......A.... |.....B.... |....C...
1 Name| Height| Weight
2 Jason | 74 | 170
3 Greg | 70 | 160
4 Sam | 71 | 200
......A.... |.....B.... |....C...
1 姓名| 身高| 重量
2 杰森 | 74 | 170
3 格雷格 | 70 | 160
4 山姆 | 71 | 200
and I want to pull out that data and format into a Word file with the following format:
我想将该数据和格式提取到具有以下格式的 Word 文件中：
2.1 Jason
Height: 74
Weigh: 170
2.1 杰森
身高：74
体重：170
2.2 Greg
Height: 70
Weight: 160
2.2 格雷格
身高：70
体重：160
2.3 Sam
Height: 71
Weight: 200
2.3 山姆
身高：71
体重：200
Is there a quick way to do that with VBA and be able to iterate through as many rows as may exist in any particular Excel file? (could vary from a few to many hundreds) The real excel file contains about a dozen columns where for each record (row) the data needs to be pulled out and formatted using standard template (font size/color, indent, alignment, etc...) but i'd love to just get the extract to work and I can play with the formatting later.
有没有一种快速的方法可以用 VBA 做到这一点，并且能够遍历任何特定 Excel 文件中可能存在的尽可能多的行？（可能从几个到几百个不等）真正的 excel 文件包含大约十二列，其中对于每条记录（行），需要使用标准模板（字体大小/颜色、缩进、对齐等）提取数据并进行格式化。 ..）但我很想让提取物工作，我可以稍后使用格式。
For reference, I've tried to research known solutions, but most are centered on named bookmarks and relatively static content vs. interating through a dynamic number of rows and parsing the same data for each.
作为参考，我试图研究已知的解决方案，但大多数都集中在命名书签和相对静态的内容上，而不是通过动态行数进行交互并为每个行解析相同的数据。
回答by Scott Holtzman
In case you do end up using VBA, you can use the below code starting from a word document. Make sure to have the Reference for Microsoft Excel X.X Object Library checked in under Tools > References in the VBE.
如果您最终使用 VBA，您可以使用以下代码从 Word 文档开始。确保在 VBE 中的工具 > 引用下签入了 Microsoft Excel XX 对象库的引用。
Just so you know, the part where it puts the strings into Word could probably be written better. Word is my weakest of all MS Office products in terms of knowledge.
您知道，将字符串放入 Word 的部分可能会写得更好。就知识而言，Word 是我所有 MS Office 产品中最弱的。
Sub XLtoWord()
Dim xlApp As Excel.Application
'Set xlApp = CreateObject("Excel.Application")
Set xlApp = GetObject(, "Excel.Application") '-> assumes XL is open, if not use CreateObject
Dim wkb As Excel.Workbook
Set wkb = xlApp.Workbooks("Book5.xlsm") '-> assumes xl is open, if not use .Workbooks.Open(filename)
Dim wks As Excel.Worksheet
Set wks = wkb.Sheets(1) '-> assumes data is in sheet 1
With wks
Dim lngRow As Long
lngRow = .Range("A" & .Rows.Count).End(xlUp).Row
Dim cel As Excel.Range
Dim i As Integer
i = 1
For Each cel In .Range("A2:A" & lngRow) 'assumes data is filled from top left cell of A1 including headers
strLabel = "2." & i & " " & cel.Text
strHeight = "Height " & cel.Offset(, 1).Text
strWeight = "Weight " & cel.Offset(, 2).Text
Dim myDoc As Word.Document
Set myDoc = ThisDocument
myDoc.Range.InsertParagraphAfter
myDoc.Range.InsertAfter strLabel & Chr(11) & strHeight & Chr(11) & strWeight
i = i + 1
Next
End With
End Sub
