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

jegrana2

New Member
Joined
Jan 18, 2010
Messages
3
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
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

ZVI

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,658
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

New Member
Joined
Jan 18, 2010
Messages
3
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

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,658
Glad the problem is solved. Welcome to MrExcel board by the way!

To post the code use these tags:
Code:
    Copy your code here
See more details here

Best Regards
 

Watch MrExcel Video

Forum statistics

Threads
1,095,376
Messages
5,444,103
Members
405,267
Latest member
bolton70

This Week's Hot Topics

Top