VBA code to open word from excel

[lukemeyer]

I have not taken VBA for 10 years and am very rusty. I am not sure if this is possible, but I figure I would ask.


I am trying to write a macro in excel that does 3 things.

1. Opens a word file C:\Users\xxxx\Desktop\test.docx
2. Does a save as, and saves the file as a pdf in word.
If I can make this so it an easy programmable location that would be awesome.
3. It then closes the word without saving it.

If anyone knows please let me know if this is possible and example code would be great.


I have written it so it opens the file so far.
Code is posted below

Set wordapp = CreateObject("word.Application")

wordapp.documents.Open "C:\Users\xxxx\Desktop\test.docx"
wordapp.Visible = True

 

[sykes]

I'm not an expert by any means, but one way you could do this would be to first make your word doc a macro-enabled one (I called mine "test.docm"), then put your "save as" routine in the document's _open event.
Then you could open the document with your existing code, and the save as .pdf would be done from the Word doc. Seems to work for me:

Workbook code:

Sub tst()

Set wordapp = CreateObject("word.Application")
wordapp.documents.Open "C:\Users\XXXX\Desktop\test.docm"
wordapp.Visible = True
End Sub

Word doc code:

Private Sub Document_Open()


    ChangeFileOpenDirectory "C:\Users\XXXXX\Desktop\"
    ActiveDocument.ExportAsFixedFormat OutputFileName:= _
        "C:\Users\XXXXX\Desktop\zzzz.pdf", ExportFormat:=wdExportFormatPDF, _
        OpenAfterExport:=True, OptimizeFor:=wdExportOptimizeForPrint, Range:= _
        wdExportAllDocument, From:=1, To:=1, Item:=wdExportDocumentContent, _
        IncludeDocProps:=True, KeepIRM:=True, CreateBookmarks:= _
        wdExportCreateNoBookmarks, DocStructureTags:=True, BitmapMissingFonts:= _
        True, UseISO19005_1:=False

End Sub

Obviously, substitute the XXXXX for the real paths.

I used the macro recorder to get the Word code, so this could probably be cleaned up considerably.
Hopefully this will get you on the right track, at least.

Good luck.....

 

[lukemeyer]

Thank you. That makes sense. I was trying to do it all from excel, but this makes more sense. Thank you again for your help.

 

[sykes]

Pleasure. Glad to be of help.