利用ExcelVBA生成固定文本格式的PDF报告

'sheet1.cells()是我们所引用数据的单元格位置，如姓名在sheet1表的C12，所以是sheet1.cells(12,3) Sheets(SteName).Shapes.Range(Array("TextBox 2")).TextFrame2.TextRange.Replace

FindWhat:="%Name%", ReplaceWhat:=Sheet1.Cells(12, 3), MatchCase:=msoFalse

Sheets(SteName).Shapes.Range(Array("TextBox 2")).TextFrame2.TextRange.Replace

FindWhat:="%Date%", ReplaceWhat:=Sheet1.Cells(13, 3), MatchCase:=msoFalse

Sheets(SteName).Shapes.Range(Array("TextBox 2")).TextFrame2.TextRange.Replace

FindWhat:="%Words%", ReplaceWhat:=Sheet1.Cells(14, 3), MatchCase:=msoFalse

Dim FileName01, FileName02 As String

Dim FileNum02 As Integer

Dim FileNum01 As Boolean

FileNum01 = True

FileNum02 = 1

'PDF文件要保存的位置，这里默认在D盘的生成PDF文件夹里

FileName01 = "D:\生成PDF"

FileName02 = FileName01 & "\" & PatName & FileNum02 & ".pdf"

If Dir(FileName01) = "" Then

MkDir FileName01

End If

Do While FileNum01

If Dir(FileName02) <> "" Then

FileNum02 = FileNum02 + 1

FileName02 = FileName01 & "\" & PatName & FileNum02 & ".pdf"

Else: FileNum01 = False

End If

Loop

Sheets(SteName).ExportAsFixedFormat xlTypePDF, Filename:=FileName02, OpenAfterPublish:=True

'删除工作表的副本

Sheets(SteName).Delete

Application.DisplayAlerts = True

Application.ScreenUpdating = True

End Sub

三、注意

Excel必须是2013版本以上才支持，wps不行。VBA的代码水平不需要很高，按照上面注释修改就可以了。如下是效果