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

 

[vicoyh]

It is this (Error 424 occurs at line highlighted in red):

Sub Export()
Dim objWord
Set objWord = CreateObject("Word.Application")
' Hidden window!
objWord.Visible = False
Dim objDoc As Object
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\1.pdf", _
ExportFormat:=17
End Sub


Also can this code be used to copy and paste many doc files from the source folder to as pdf in the destination folder. Kindly teach me if this is possible.

 

[Andrew Poulsom]

I can't reproduce your error when opening a valid document, sorry. Of course I don't have your folder setup. Have you tried setting Visible to True?

 

[vicoyh]

Thank you very much Andrew. I now have a great understanding of what I am doing.

I managed to fix it using your method but I chose to set Visible to False as the user did not like seeing so many word documents pop up.

I simply used On Error Resume Next to get rid of the errors.

Because my word document is linked to excel, I get a lot of word prompts asking me whether I want to save. Is it possible to insert a macro to stop these annoying save pop ups?

 

[Andrew Poulsom]

What code are you using to close the word document?

 

[vicoyh]

Hi Andrew,

I am using the following code below to close the word document:
Dim objWord2
Set objWord2 = CreateObject("Word.Application")
' Hidden window!
objWord2.Visible = False
Dim objDoc2
Set objDoc2 = objWord2.Documents.Open("T:\\Watermarked document\2.doc")
objDoc2.ExportAsFixedFormat OutputFileName:="T:\2.pdf", _
ExportFormat:=17
objWord2.Quit SaveChanges:=False
Set objWord2 = Nothing

 

[Andrew Poulsom]

I don't know why that wouldn't suppress the save popups, sorry.

 

[vicoyh]

I must have done something wrong on my part because now I tried and it works perfect.