Mac VBA help to open Word from Excel

[Daniellel]

Hi,

I have some basic code that in Mac opens a word doc from excel and then copies some data and pastes it into word... this was working fine last week with no issues... this week there were times it would not open word properly and then the data would not copy over even though the code said "Completed"... today it will not run at all... i am getting the error "'5863': 'Application' is not a method for the code line "oWord.Application" in the paste to word section... please can someone look at my code and see what is wrong? i added the Applciation.wait and DoEvents to try to get it to open correctly but its not working...

Sub CopyToWord()

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''Create Word Doc
Dim oWord As Object
Application.Wait (10)
DoEvents
Set oWord = CreateObject(Class:=("Word.application"))
Application.Wait (10)
DoEvents
oWord.Visible = True
oWord.Activate
Application.Wait (10)
DoEvents
Dim oDoc
Application.Wait (10)
DoEvents
Set oDoc = oWord.Documents.Add

'Copy To Word
    Sheet23.Select
    Range("Print_Area21").CopyPicture
   oWord.Application
    oWord.Application.Selection.Paste.Special Link:=False, DataType:=15, _
        DisplayAsIcon:=False
    oWord.Selection.TypeParagraph

  
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''End Message
MsgBox ("Compied to Word")

End Sub

 

[smozgur]

this was working fine last week with no issues

I would be surprised. Especially with oWord.Application line below CopyPicture.

Try the following code below:

Sub CopyToWord()
Dim oWord As Object
Dim oDoc As Object
    ' First testing if there is a running instance of
    ' the Word application by choosing the error handling
    On Error Resume Next
    Set oWord = GetObject(Class:=("Word.application"))
    ' If error then it means there is no running instance
    ' create one
    If Err Then
        Set oWord = CreateObject(Class:=("Word.application"))
    End If
    ' Disable error handling - means generate error if any after this point 
    On Error GoTo 0
    
    ' Wait until the Word application is loaded
    Do While oWord Is Nothing
        ' Doevents will let managed code interruption if endless loop
        ' just in case..
        DoEvents
    Loop
    
    ' Word application is loaded, make it visible
    oWord.Visible = True
    ' Create a new Word document
    Set oDoc = oWord.Documents.Add
    
    ' Copy the requested range in Sheet23 as picture
    Sheet23.Range("Print_Area21").CopyPicture
    ' Copy the clipboard content into the new Word document
    ' and jump to the next paragraph
    With oWord.Selection
        .Paste
        .TypeParagraph
    End With
    
    MsgBox ("Copied to Word")

End Sub

 

[Jon Peltier]

What version of Mac Excel?

Mac Office 2011 used to run its apps without placing them in a sandbox.

More recently Apple requires apps to run in a sandbox so that one app can't take down the system.

When Microsoft designed the next version of Mac Office (2016, but only v. 15.0), rather than running all of Office in one sandbox, they run each app in its own sandbox. So Excel can't reach out of its sandbox into the Word sandbox, and so you can't use VBA to automate Word or PowerPoint.

When I build this functionality for clients lately, I tell them that it only works in Windows and not Macs