使用 vba 自动生成带有从 Excel 工作表导入的数据的 Microsoft Word 文档，使用变量过滤实际插入的数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/35116302/
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
Using vba to automate a Microsoft Word document with data imported from an Excel sheet, using a variable to filter what data to actually insert
提问by dinocore
I'm trying to populate a Word document with information from Excel. The table on excel looks really simple.
我正在尝试使用 Excel 中的信息填充 Word 文档。excel上的表格看起来很简单。
QUESTION |YES| DATA
Prewritten question 1 | X | Prewritten data 1
Prewritten question 2 | X | Prewritten data 2
Prewritten question 3 | X | Prewritten data 3
How would I then automate populating a new word document with the data with the variable X under YES, only pasting the prewritten data held in the data column?
然后我将如何使用变量 X 下的数据自动填充新的 Word 文档，只粘贴数据列中保存的预写数据？
I almost got this working on Word just using the step by step mail merger feature , but while I was able to populate the Word document, the Prewritten data would paste, but each pasted selection from the data column would take its own page on Word, even if it was a short 3 word phrase.
我几乎只使用分步邮件合并功能就可以在 Word 上工作，但是虽然我能够填充 Word 文档，但预写的数据会粘贴，但数据列中的每个粘贴选择都会在 Word 上占据自己的页面，即使它是一个简短的 3 个词短语。
回答by Cindy Meister
The simplest way for you would be without code, using a Word field.
最简单的方法是不用代码，使用 Word 字段。
- In Excel, select the range of data and assign it a name (Range name)
- In Word Insert/Text/Quick Parts/Field
- from the list, choose Database. An Insert Database button will appear, click that.
- In the dialog box, Step 1, navigate to the location of the Workbook and select it. You'll be prompted for the connection protocol (OLEDB is fine). Select the Range Name from the list.
- Choose "Query Options" in Step 2. Tab "Select Fields": In the list on the right, select the fields you do NOT want and click "Remove". Tab "Filter Records": Choose the "Yes" column and set Comparison "Equal to" and type X in the third box.
- In Step 3 click "Insert Data"; be sure to activate "Insert Data as Field" checkbox to get an active link back to the Workbook.
- 在 Excel 中，选择数据范围并为其指定名称（范围名称）
- 在 Word 中插入/文本/快速部件/字段
- 从列表中选择数据库。将出现一个插入数据库按钮，单击该按钮。
- 在对话框中，步骤 1，导航到工作簿的位置并选择它。系统会提示您输入连接协议（OLEDB 很好）。从列表中选择范围名称。
- 在步骤 2 中选择“查询选项”。选项卡“选择字段”：在右侧的列表中，选择不需要的字段，然后单击“删除”。选项卡“过滤记录”：选择“是”列并设置比较“等于”并在第三个框中键入 X。
- 在第 3 步中点击“插入数据”；请务必激活“将数据作为字段插入”复选框以获取返回工作簿的活动链接。
This will insert a Database field in the Word document with syntax something like the following. Press Alt+F9 to toggle between field result and field display views. If you've done any work with data connections some of it will look familiar to you. In any case, you should recognize the file path near the beginning and the SQL select statement at the end.
这将在 Word 文档中插入一个数据库字段，其语法类似于以下内容。按 Alt+F9 在字段结果视图和字段显示视图之间切换。如果您对数据连接做过任何工作，那么其中的一些对您来说会很熟悉。在任何情况下，您都应该识别开头附近的文件路径和结尾处的 SQL select 语句。
{ DATABASE \d "C:\Test\ExcelDataRange.xlsx"
\c "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;
Data Source=C:\Test\ExcelDataRange.xlsx;Mode=Read;
Extended Properties=\"HDR=YES;IMEX=1;\";
Jet OLEDB:System database=\"\";Jet OLEDB:Registry Path=\"\";
Jet OLEDB:Engine Type=35;Jet OLEDB:Database Locking Mode=0;
Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;
Jet OLEDB:New Database Password=\"\";Jet OLEDB:Create System Database=False;
Jet OLEDB:Encrypt Database=False;
Jet OLEDB:Don't Copy Locale on Compact=False;
Jet OLEDB:Compact Without Replica Repair=False;
Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;
Jet OLEDB:Bypass UserInfo Validation=False"
\s "SELECT `Data` FROM `Test` WHERE ((`Yes` = 'X'))" }
If the data in the workbook changes, click in the table and press F9 to update the field. (If you add rows to the range be sure to adjust the Range Name to include them all!)
如果工作簿中的数据发生更改，请在表中单击并按 F9 以更新该字段。（如果您向范围添加行，请务必调整范围名称以将它们全部包含在内！）
