记录某次利用word邮件功能由excel数据中批量生成奖状遇到的问题及解决措施

良月澪二

已于 2022-03-01 00:07:34 修改

阅读量1.5k

点赞数 4

分类专栏： ========Others======== 文章标签：经验分享 vbs vbscript word excel

于 2022-03-01 00:02:23 首次发布

本文链接：https://blog.csdn.net/yandaoqiusheng/article/details/123191051

版权

========Others======== 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

学校的一次活动结束之后，部门下发了一个任务：由一个excel中的数据生成奖状
在这里插入图片描述
excel中的数据为以上形式，根据排名分为一二三等奖，要给每组分发奖状，奖状为以下格式

奖状中一共有三处不同的地方：

团队名称不同，要求“团队”前写上队伍中五个人的名字且带空格
奖状编号不同
获得的奖不同，对应着下面的称号也不同

如果人工操作，除了需要一个文件一个文件编辑，还有一个很麻烦的地方，那就是团队成员无法直接复制，表格中的数据带有年级+学号，而奖状中只需要姓名。

这种由数据批量编辑的做法很容易想到word的邮件功能，正好我之前用的不多，就拿来练练手。然后我马上遇到了第一个问题

word邮件功能无法打开数据源

在这里插入图片描述
其实这个解决办法很简单，大部分的原因都是因为文件格式不对。邮件合并功能不支持excel的高级版本，也就是.xlsx对应的07版往上，将excel另存为.xls就解决了
如果还有问题，那基本上就是格式的问题了，邮件要求第一行的字段名称对应下面的字段内容，格式错了之后再修改成功是很难的，我建议把对你有用的数据单独复制下来，粘贴到一个新建的xls文件中，这样能就解决问题

打开数据源之后插入合并域，这里的操作很简单，在你想要的位置插入对应的字段就好了
在这里插入图片描述
但在这之前还有之前提出的一个问题没有解决，奖状中要求只要人名，但数据是带有年级和学号的，直接插入就会出现这种情况

杂乱的数据中提取姓名

考虑通过替换的方式修改excel中的数据，可以观察到每个数据的格式是这样的：“年级-学号-姓名”，这里说来话长，如果数据都是这个格式，改起来并不难，但是这些数据是不严格筛查格式的个人填表，我们在表中一次次筛查后发现了以下几种情况：

“-”格式错误，数据中有这几种“-”的替换写法：空格、半角非中断空格( )、中文“-”、全角“-”
不写“级”或多写，比如“20级-”写为“2020级-”或“20-”
写成“年级-姓名-学号”，这个比较严重
不写“-”，即“年级学号姓名”，这个也很严重
在年级后面跟上专业，比如“21级计算机-”或“21计算机-”
上面几种问题的组合，比如写成“年级-姓名-学号”，而且不写“-”，“级”就会和姓名连起来

单纯用excel的替换肯定是解决不了这些问题的，用正则表达式比较理想。一番尝试与修改过后，我们制定出了一套针对于这些数据的提取方案，其中excel替换和正则表达式结合使用（正则表达式使用该工具）
在这里插入图片描述

顺序必须不能错：

将下面这些符号替换为“-”：空格半角非中断空格( ) 中文短杠全角短杠
正则替换正则表达式：\d\d级
正则替换正则表达式：[\x{4e00}-\x{9fa5}]{2,5} 子模式序列号：-$0-$1
正则匹配正则表达式：(-[\x{4e00}-\x{9fa5}]{2,5}-)|(-[\x{4e00}-\x{9fa5}]{2,5})
excel通配符替换“*–”（这里是双杠）为空格
excel替换“-”为空格

分别的作用为：

讲语法错误纠正，统一改为“-”
先去掉“级”，避免级和姓名连起来无法区分的情况
给没有在分割处写“-”的加上“-”，方便之后处理
只留下姓名和两旁的“-”（还有一小部分处理不到，下面一步来处理）
处理上一步无法处理的，即“计算机-”这种，这种到这里已经被处理为“计算机–姓名”，用通配符删掉“–”之前的即可
删掉所有的“-”

到这里就可以纯净提取出所有的名字了，插入合并域并点击“查看合并数据”后就能得到如下效果，上下翻就能看到做的每一张
在这里插入图片描述
然后我们需要导出这每一张奖状，点击“合并到不同新文档”，将这些文件放到一个新建的文件夹中

之后又出现了一个严重的问题

word邮件合并后，每个单独的文件最后都会多出一页

在这里插入图片描述
我并没有找到一个简单的处理方法批量删除文档最后一页，WPS的批处理中没有找到这个功能，所以尝试用word中的宏来处理
由于宏也无法直接批量运行，所以需要一个vbs文件来操作宏批量运行，运行时office的Word配置比较完全，容易创建进程，所以在Word中录制宏，之后再讨论如何使用WPS
录制宏需要先开启“开发工具”这一栏，具体方法可以在网上找
Word中录制宏没法记录鼠标操作，所以删除第二页必须只用键盘操作，几番尝试后我找到了一个比较好的方法：

PgDn（跳转到最后一行）
End（光标到最后一列）
Del（删除右方即删除第二页）

所以点三下键盘即可删除第二页，之后点一下保存，再结束录制
也可以直接代码写一个宏，要注意宏的名字要符合变量命名，数字不能开头，尽量用英文，我这个起了个名叫“g4”

Sub g4()
    Selection.MoveDown Unit:=wdScreen, Count:=1
    Selection.EndKey Unit:=wdLine
    Selection.Delete Unit:=wdCharacter, Count:=1
    ActiveDocument.Save
End Sub

然后就是

如何批处理运行宏了

在需要操作的文件夹里新建一个.vbs文件
在这里插入图片描述

编辑并输入以下内容，有两处需要修改的地方

Option Explicit
Const c_strRootFolder = "C:\Users\lyle\Desktop\a\b" `这里输入文件夹路径，外面要加""
On Error Resume Next
Dim oApp, oDoc
Dim fso, oFolder, oFile
Set fso = CreateObject("Scripting.FileSystemObject")
Set oFolder = fso.GetFolder(c_strRootFolder)
Set oApp = CreateObject("Word.Application")
oApp.Visible = True

For Each oFile In oFolder.Files
    If (UCase(Right(oFile.Path, 4)) = ".DOC") Or (UCase(Right(oFile.Path, 5)) = ".DOCX") Then
        Set oDoc = oApp.Documents.Open(oFile.Path)
        oApp.Run "g4" `这里写宏的名称，外面要加""
        oDoc.Close True
    End If
Next

oApp.Quit False
Set oFolder = Nothing
Set fso = Nothing
Set oApp = Nothing

编辑完成之后点击，就会看到一个文件一个文件的闪出，操作没问题的话就成功了