excel - VBA将word文档从excel保存为pdf

Question

我见过很多人问这个问题，但我无法让它发挥作用。

背景 - 我正在创建一个邮件合并，从 excel 到 WORD 模板，然后我需要将其保存为 PDF。我的 VBA 代码在 excel 中 - 理想情况下需要留在那里。邮件合并正在工作，按预期创建和保存 word 文档，但我无法另存为 PDF。

我创建邮件合并的代码：

'Declare Some Variable
Dim FTR As String
Dim cDir As String
Dim r As Long
Dim ThisFileName As String
Dim bCreatedWordInstance As Boolean


'Set Up WORD object
Dim objWord As Object
Dim objDoc As Object

On Error Resume Next

    bCreatedWordInstance = False
    Set objWord = GetObject(, "Word.Application")

    If objWord Is Nothing Then
        Err.Clear
        Set objWord = CreateObject("Word.Application")
        bCreatedWordInstance = True
    End If
    If objWord Is Nothing Then
        MsgBox "Could not start Word"
        Err.Clear
        On Error GoTo 0
        Exit Sub
    End If
    objWord.Visible = True

    'Open Word Template
    Set objDoc = objWord.Documents.Open("C:\MailMergeTest\MAILMERGE TEMPLATE.dotx")

    objWord.Activate


' Let Word trap the errors
On Error GoTo 0

' Set to True if you want to see the Word Doc flash past during construction
objWord.Visible = False


' Setup filenames
Const WTempName = "MAILMERGE TEMPLATE.dotx" '
Dim NewFileName As String


' Setup directories
cDir = ActiveWorkbook.Path + "\" 'Change if appropriate
ThisFileName = ThisWorkbook.Name




lastrow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
'Merge the data
With objDoc
With .MailMerge
.OpenDataSource Name:=cDir + ThisFileName, sqlstatement:="SELECT *  FROM `Sheet1$`"   ' Set this as required


For r = 1 To lastrow
    If Cells(r, 12).Value = "FTR Generated Already" Then GoTo nextrow
    FTR = Sheets("Sheet1").Cells(r, 3).Value

        .Destination = wdSendToNewDocument
        .SuppressBlankLines = True
            With .DataSource
                .FirstRecord = r - 1
                .LastRecord = r - 1
                .ActiveRecord = r - 1
            End With
        .Execute Pause:=False

    ' Save new file
    NewFileName = "MAILMERGE TEST - " & FTR & ".docx"
    NewFileNamePDF = "MAILMERGE TEST - " & FTR & ".pdf"
    objWord.ActiveDocument.SaveAs cDir + NewFileName

    ' ***** SAVE AS PDF CODE HERE



 GoTo nextrow


0:
    Set objWord = Nothing
    Cells(r, 12).Value = "FTR Generated Already"

nextrow:

        Next r
    End With
    End With
    ' Close the New Mail Merged Document
            If bCreatedWordInstance Then
                objWord.Quit
            End If

    ' Close the Mail Merge Main Document
    objDoc.Close savechanges:=wdDoNotSaveChanges
    Set objDoc = Nothing

我尝试了以下方法：

objDoc.ExportAsFixedFormat OutputFileName:=cDir & NewFileNamePDF, ExportFormat:=wdExportFormatPDF

...这会出现一条错误消息，说“无效的过程调用或参数”。

也试过：

objWord.ActiveDocument.SaveAs2 cDir + NewFileNamePDF, FileFormat:=wdFormatPDF

.. 导出文件，但无法以 PDF 格式打开。

任何人都可以在这里帮忙吗？

预先感谢您的建议

李

标签： excelvbapdfms-wordmailmerge