Error converting a Word 2010 document to pdf with Excel 2010 VBA

[jegrana2]

Hello everyone. I have a macro that takes data from Excel 2010 and pastes it on a Word 2010 document, then saves the Word document and works fine. I also want to export the Word document to pdf and that is the problem. The code I have shows below. I have in Excel VBA a variable to call Word set as wdApp . When I use wdApp.ActiveDocument.ExportAsFixedFormat etc. I get runtime error 424. If I omit wdApp only then I get runtime error 4. Can someone check this code and give me some light on what am I doing wrong? Thank you so much in advance.

Private Sub cmdCreate_Click()
Dim strPermit, strPermitPath, strSaveAs, strSaveAsPDF As String
Dim lRow As Long
msgPrint = "Do you want to print now" & vbCrLf & "Permit ZP" & Sheets("Permit").Range("I13").Value & "?"
' Path where file will be saved at
strPermitPath = Sheets("Data").Range("ZPath").Text
Application.DisplayAlerts = False
Sheets("Permit").Select
Sheets("Permit").Unprotect Password:="ch20750"
' Name to save new document
strPermit = Right(Sheets("Permit").Range("I13").Value, 3) & "_" & Replace(Sheets("Permit").Range("B7").Text, " ", "_")
strSaveAs = strPermitPath & strPermit & ".docx"
' Name to save as pdf
strSaveAsPDF = strPermitPath & strPermit & ".pdf"
On Error Resume Next
Set wdApp = GetObject(, "Word Application")
' Check if Word already running
If Err.Number <> 0 Then 'Word isn't already running
Set wdApp = CreateObject("Word.Application.8")
End If
On Error GoTo 0
wdApp.Visible = True
Sheets("Permit").Select
Range("B1:J42").Copy
' Tell Word to create a new document
Set wdDoc = wdApp.Documents.Open("P:\Community Dev\Zoning Permits & Applications\Zoning _
Permits\Permit_Master_Template.docx")
' Tell Word to paste the contents of the clipboard into the new document
wdApp.Selection.Paste
' Save the new document with a sequential file name
wdApp.ActiveDocument.SaveAs Filename:=strSaveAs
If MsgBox(msgPrint, vbYesNo, "City of Dunnellon - Zoning Permits") = vbYes Then
' Application.Dialogs(xlDialogPrinterSetup).Show 'Select printer
wdDoc.PrintOut
End If
' Save the document as pdf
wdApp.ActiveDocument.ExportAsFixedFormat OutputFileName:=strSaveAsPDF _
, ExportFormat:=wdExportFormatPDF, OpenAfterExport:=False, OptimizeFor:= _
wdExportOptimizeForPrint, Range:=wdExportAllDocument, From:=1, To:=1, _
Item:=wdExportDocumentContent, IncludeDocProps:=True, KeepIRM:=True, _
CreateBookmarks:=wdExportCreateNoBookmarks, DocStructureTags:=True, _
BitmapMissingFonts:=True, UseISO19005_1:=False
' Close this new word document
wdApp.ActiveDocument.Close
' Close the Word application
wdApp.Quit
Unload frmPermit
With Worksheets("Permit")
.Range("B21").Value = ""
.Range("C21").Value = ""
.Range("C26").Value = ""
.Range("C28").Value = ""
.Range("C30").Value = ""
.Range("C32").Value = ""
.Range("C34").Value = ""
End With
Sheets("Permit").Protect Password:="ch20750"
Sheets("Zoning Permits").Select
Sheets("Zoning Permits").Unprotect Password:="ch20750"
On Error Resume Next
lRow = Application.WorksheetFunction.Match(Range("A8").Value, Sheets("Zoning Permits").Range("A11:A149"), 1)
lRow = lRow + 10
If lRow > 10 Then
Cells(lRow, 5).Select
ActiveCell.Value = "Issued"
End If
Range("A8").Select
Sheets("Zoning Permits").Protect Password:="ch20750"
Application.DisplayAlerts = True
End Sub

 

[ZVI]

Hi,
The constants of word application are not defined in the code.
Try to add these constants to the code:
Const wdExportFormatPDF = 17
Const wdExportOptimizeForPrint = 0
Const wdExportAllDocument = 0
Const wdExportDocumentContent = 0
Const wdExportCreateNoBookmarks = 0

 

[jegrana2]

ZVI, thank you so very much. That silly mistake was the cause of all my problems. It's working like charm. By the way, my apologies for not being able to show the code with the correct indentation. I tried couple time adding spaces at the beginning of each line but when I posted it came back straight as you could see. I need to learn how to copy/paste correctly any code in this forum. Instructions/tips always welcome!

 

[ZVI]

Glad the problem is solved. Welcome to MrExcel board by the way!

To post the code use these tags:

    Copy your code here

See more details here

Best Regards

 

[jegrana2]

Thank you.