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
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,205
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top