|
word文件就是一个简单的模板,每节10段,需要在每节的第7段插入筛选后的非空表格。
表格形式为:基础数据表("处理"),筛选结果表("选择")
表大致样式为:
信息1 | 信息2 | 信息3 | 信息4 | 信息5 | 信息6 | 信息 7 | 筛选条件 | | | | | | | 结果1 | | | | | | | 结果2 | | | | | | | 结果3 | | | | | | | ... | | | | | | | ... | | | | | | | ... | | | | | | | ... | | | | | | | 共20行 | | | | | | | 目前代码为:(问题,表格可以排序,但word文件另存为后不显示粘贴内容,不清楚是粘贴部分出问题还是保存部分有问题,异或是屏幕刷新的问题?)
- Sub 生成()
- On Error Resume Next
- Dim ws As Worksheet, arr
- Set ws = Worksheets("选择")
- arr = Worksheets("选择").Range("B31:B" & ws.Range("B65536").End(xlUp).Row)
- Dim dpath, Filename As String
- Dim wdapp As Object
- Set wdapp = CreateObject("Word.Application") '打开word
- wdapp.Visible = True
- dpath = ThisWorkbook.Path
- Filename = Dir(dpath & "\正面.doc")
- wdapp.Documents.Open (dpath & "" & Filename)
- Dim s As Section
- Dim i As Integer
- i = 1
- wdapp.Application.Activate
- With ActiveDocument '调测
- For Each s In .Sections
- If s.Range.Paragraphs.Count < 2 Then Exit For
- If s.Range.Paragraphs.Count > 6 Then
- Excel.Application.Sheets("选择").Activate '激活excel
- If i <= UBound(arr) Then
- Application.ScreenUpdating = False
- [K4] = arr(i, 1)
- Sheets("处理").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
- CriteriaRange:=Range("K3:K4"), CopyToRange:=Range("A2:G20"), Unique:= _
- False
-
- ActiveWindow.SmallScroll Down:=3
- Range("B3:G20").Select
- ActiveWorkbook.Worksheets("选择").Sort.SortFields.Clear
- ActiveWorkbook.Worksheets("选择").Sort.SortFields.Add Key:=Range( _
- "B3:B20"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
- xlSortNormal
-
- With ActiveWorkbook.Worksheets("选择").Sort
- .SetRange Range("B3:G20")
- .Header = xlGuess
- .MatchCase = False
- .Orientation = xlTopToBottom
- .SortMethod = xlPinYin
- .Apply
- End With
- Sheets("选择").Range("B1:G" & Sheets("选择").Range("G65536").End(xlUp).Row).Copy
-
- '回到word粘贴.
- s.Range.Paragraphs(7).Range.Paste
- i = i + 1
- Application.ScreenUpdating = True
- End If
- Else
- Exit For
- End If
- Next
- End With
- wdapp.Application.Activate '保存word文件
- wdapp.Saved = True
- wdapp.ActiveDocument.SaveAs "D:\插入后文件.doc"
- wdapp.Application.Quit
- Set wdapp = Nothing
- End Sub
复制代码
|
|