|
Sub test()
Dim rng As Range
Dim xml As Range
Dim Emp As Range
Dim Target As Range
Dim pic As String
Dim objWord As Object
Dim strPath$
Dim hs As Integer
Dim mbName, renArr
Dim MyNa As String, seltxt
Set objWord = CreateObject("word.application")
strPath = ThisWorkbook.Path & Application.PathSeparator
Sheets("数据").Select
Set xml = Worksheets("数据").Range("C:C")
On Error Resume Next
Set rng = Application.InputBox("请选择数据" & vbCrLf & vbCrLf & "可按CTRL键选择不连续的人员", "提示", , Type:=8)
MyNa = InputBox("请输入要导出的类型,多项类型中间用顿号分隔", "提示", "妻子、丈夫")
seltxt = Split(MyNa, "、")
If rng Is Nothing Then Exit Sub
If Application.Intersect(xml, rng).Count <> rng.Count Then MsgBox "非法选区,程序退出!", vbInformation + vbOKOnly, "提示": Exit Sub
For Each Emp In rng
If IsEmpty(Emp) Then MsgBox "选择区有空单元格": Exit Sub
Next Emp
With objWord
For Each Target In rng
'With .Documents.Add(Template:=strPath & "模板-可选择操作.doc")
With .Documents.Add(Template:=strPath & "模板" & ".doc")
Application.StatusBar = "正在处理 " & Cells(Target.Row, "c")
.bookmarks("姓名").Range.Text = Cells(Target.Row, "c")
'*************************************************************************
If Trim(Cells(Target.Row, "E")) <> "" Then
renArr = Split(Cells(Target.Row, "E"), vbLf)
For hs = 0 To UBound(renArr)
If InStr(renArr(hs), seltxt(0)) Or InStr(renArr(hs), seltxt(1)) Then
re = Split(renArr(hs), ",")
hs = 0
.Tables(2).Cell(hs + 5, 2).Range.Text = re(0)
.Tables(2).Cell(hs + 5, 3).Range.Text = re(1)
.Tables(2).Cell(hs + 5, 4).Range.Text = Format(re(2), "yyyy.mm")
.Tables(2).Cell(hs + 5, 5).Range.Text = re(3)
.Tables(2).Cell(hs + 5, 6).Range.Text = re(4)
Exit For
End If
Next hs
End If
'*************************************************************************
.SaveAs strPath & Cells(Target.Row, "c") & ".doc", FileFormat:=0
'.SaveAs strPath & Cells(Target.Row, "a") & "." & Cells(Target.Row, "c") & ".doc", FileFormat:=0
.Close True
End With
Next
.Quit
End With
Application.StatusBar = ""
MsgBox "恭喜您!整理完成。", , "提示"
End Sub |
|