Save embedded Word Doc as PDF

Question

SCENARIO

The text document is embedded in the Excel 2011 file. I need to save it in pdf format.

If it were Excel 2010, that would not be a problem, since MS-Office in Win Pcs supports OLE automation.

WHAT DO I EXCLUDE?

This is the code I tried in Excel 2010 that works.

Option Explicit Sub Sample() Application.ScreenUpdating = False Dim shp As Shape Dim objWord As Object Dim objOLE As OLEObject Set shp = Sheets("Sheet1").Shapes("Object 1") shp.OLEFormat.Activate Set objOLE = shp.OLEFormat.Object Set objWord = objOLE.Object objWord.ExportAsFixedFormat OutputFileName:= _ "C:\Users\Siddharth Rout\Desktop\Sid.pdf", ExportFormat:= _ 17, OpenAfterExport:=True, OptimizeFor:= _ 0, Range:=0, From:=1, To:=1, _ Item:=0, IncludeDocProps:=True, KeepIRM:=True, _ CreateBookmarks:=0, DocStructureTags:=True, _ BitmapMissingFonts:=True, UseISO19005_1:=False objWord.Application.Quit Set objWord = Nothing Set shp = Nothing Set objOLE = Nothing Application.ScreenUpdating = True End Sub 

Obviously, I cannot use the same thing in MAC. Not that I have not tried this on the MAC ... I did: - / (Basic human nature, I think?). It failed as expected. :)

In Excel 2011, I tried this. It works, but does not create a PDF file, and does not give any error messages. I tried to debug it, but without joy.

 '~~> Reference set to MS Word Object Library Option Explicit Sub Sample() Dim oWord As Word.Application, oDoc As Word.Document Application.ScreenUpdating = False Sheets("Sheet1").Shapes.Range(Array("Object 1")).Select Selection.Verb Verb:=xlPrimary Set oWord = GetObject(, "word.application") For Each oDoc In oWord.Documents Debug.Print oDoc.FullName & ".pdf" oDoc.SaveAs Filename:=oDoc.FullName & ".pdf", FileFormat:=wdFormatPDF oDoc.Close savechanges:=False Next oDoc oWord.Quit Set oworddoc = Nothing Set oWord = Nothing Application.ScreenUpdating = True End Sub 

I believe this can also be done using AppleScript. Therefore, I also tested Applescript. Here I am trying to convert a text document directly to pdf. If I get this part, then I can take a little code in my code :)

 Sub tester() Dim scriptToRun As String scriptToRun = "set pdfSavePath to " & Chr(34) & "Users:siddharth:Documents:Sid.pdf" & Chr(34) & Chr(13) scriptToRun = scriptToRun & "set theDocFile to choose file with prompt " & Chr(34) & "Please select a Word document file:" & Chr(34) & Chr(13) scriptToRun = scriptToRun & "tell application " & Chr(34) & "Microsoft Word" & Chr(34) & Chr(13) scriptToRun = scriptToRun & "open theDocFile" & Chr(13) scriptToRun = scriptToRun & "set theActiveDoc to the active document" & Chr(13) scriptToRun = scriptToRun & "save as theActiveDoc file format format PDF file name pdfSavePath" & Chr(13) scriptToRun = scriptToRun & "end tell" & Chr(13) Debug.Print scriptToRun 'Result = MacScript(scriptToRun) 'MsgBox Result End Sub 

However, I get a MacScript(scriptToRun) , so I am sure my Applescript is not working.

Snapshot

Applescript Error

Question

How to save embedded Word doc in Excel 2011? I am open to VBA and Applescript.

Answer 1

Well, I'll be damned!

Thank you for your suggestion. It looks like the application you were talking about is deprecated with newer versions of MAC. So I searched the MAC Store and found another application called SMILE .

I tested the original script in SMILE. There is nothing wrong with that, and it worked perfectly.

 set pdfSavePath to "Users:siddharth:Documents:Sid.pdf" set theDocFile to choose file with prompt "Please select a Word document file:" tell application "Microsoft Word" open theDocFile set theActiveDoc to the active document save as theActiveDoc file format format PDF file name pdfSavePath end tell 

So, I tried the code that I tested before, and to my surprise, it worked this time without me to make any changes to the source code !!! So, I'm at a standstill about what might be the problem ... Did Smile install something that made the script work in Excel? I guess I'll never know.

 Option Explicit Sub tester() Dim scriptToRun As String scriptToRun = "set pdfSavePath to " & Chr(34) & "Users:siddharth:Documents:Sid.pdf" & Chr(34) & Chr(13) scriptToRun = scriptToRun & "set theDocFile to choose file with prompt " & Chr(34) & "Please select a Word document file:" & Chr(34) & Chr(13) scriptToRun = scriptToRun & "tell application " & Chr(34) & "Microsoft Word" & Chr(34) & Chr(13) scriptToRun = scriptToRun & "open theDocFile" & Chr(13) scriptToRun = scriptToRun & "set theActiveDoc to the active document" & Chr(13) scriptToRun = scriptToRun & "save as theActiveDoc file format format PDF file name pdfSavePath" & Chr(13) scriptToRun = scriptToRun & "end tell" & Chr(13) Debug.Print scriptToRun Result = MacScript(scriptToRun) MsgBox Result End Sub 

SMILE SNAPSHOT

EDIT: FIND AN ERROR

Upon closer inspection, I found that my original script had an extra line. I set the PDF path twice. Can be seen in the picture.