Macro to save external word/ppt documents as pdf

[vicoyh]

Hi,

I am trying to make the following steps work but Step 5 (macro) simply does not work.
Step 1 I have a worksheet where a user is able to enter data in one tab,
Step 2 which is stored in another tab by way of macros,
Step 3 which populates cells in another tab,
Step 4 the cells will then populate the footers of several word/ppt documents acting as a unique watermark in a seperate hidden folder.
Step 5 When the user hits save in excel, save the word/ppt documents as PDFs in a folder which has the file name similar to the entered data
I am clueless how to create a macro to save ppt documents as PDFs and also a macro to generate a folder with file name similar to the entered data

My macro code is as such (For the macro text in red, I get the error Run-time error '5' Invalid procedure call or argument):

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim oWord As Object
Dim oDoc As Object
Set oWord = CreateObject("Word.Application")
Set oDoc = oWord.Documents.Open("T:\Watermarked document\1.doc")
oDoc.ExportAsFixedFormat OutputFileName:="T:\1. doc", _
ExportFormat:=wdExportFormatPDF
oWord.Quit
Set oWord = Nothing


Thank you very much for helping!

Best Regards,
Victor

 

[Andrew Poulsom]

Welcome to MrExcel.

Firstly, the output file name needs a pdf extension (not doc). Secondly, you can't use Word constants when late binding, so try changing wdExportFormatPDF to 17 (the value of that constant).

 

[vicoyh]

Thank you very much, Andrew.

I tried and it generates the pdf for me. However I get a run-time error 13 for this line:
Set oDoc = oWord.Documents.Open("T:\Watermarked document\1.doc")

Any idea how to resolve this?

Thank you very much for helping.

 

[Andrew Poulsom]

How have you declared oDoc?

 

[vicoyh]

Hi Andrew,

Thanks for helping. My code is as such:

' Step 1
Dim objWord
Set objWord = CreateObject("Word.Application")
' Hidden window!
objWord.Visible = False
' Step 2
Dim objDoc
Set objDoc = objWord.Documents.Open("T:\Business Management\PSG Tracking\Z Test Folder\NCE\Watermarked document\1.doc")
objDoc.ExportAsFixedFormat OutputFileName:="T:\Business Management\PSG Tracking\Z Test Folder\NCE\Watermarked document\1.pdf", _
ExportFormat:=17

Where have I gone wrong?

 

[Andrew Poulsom]

Does it work with?

Dim objDoc As Object

 

[vicoyh]

This time I get Error 424 object required.

 

[Andrew Poulsom]

On which line?

 

[vicoyh]

On this line: Set objDoc = objWord.Documents.Open("T:\Business Management\PSG Tracking\Z Test Folder\NCE\Watermarked document\1.doc")

 

[Andrew Poulsom]

I don't see how that can be. Please post your current code.