Save embedded Word Doc as PDF

Question

SCENARIO

A word document is embedded in Excel 2011 file. I need to save it as a pdf.

Had it been Excel 2010 then it wouldn't have been a problem as MS-Office in Win Pcs support OLE automation.

WHAT HAVE I TRIED?

This is the code that I tried in Excel 2010 which 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 in MAC. Not that I didn't try this in MAC... I did :-/ (Basic human nature I guess?). It failed as expected. :)

For Excel 2011, I tried this. It works but doesn't create a pdf nor does it give any error message. I tried debugging it but no 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. So I tested with Applescript as well. Here I am trying to convert a word document directly to pdf. If I get this part then I can take a small detour 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 the runtime error on MacScript(scriptToRun) so I am sure that my Applescript is failing.

SNAPSHOT

Applescript Error

QUESTION

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

Answer 1

Well, I will be damned!

Thanks Pradeep for your suggestion. Seems like the application that you were referring to is obsolete with new MAC versions. So I searched the MAC Store and found another application called SMILE.

I tested the original script in in SMILE. There was nothing wrong with it 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 which I was testing earlier and to my surprise, it worked this time without me making any change to the original code!!! So I am stumped on what could be the problem... Did Smile install something which made the script work in Excel? Guess I will never find out.

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: FOUND THE ERROR

On closer inspection, I found that my original script had an extra line. I was setting the PDF path twice. Can be seen in the snapshot.