Excel Mac VBA Export to Word

[AdamPickard]

Hello everyone, I have come across a problem that has not been resolved elsewhere.

Like most on the forum, I am not a coder and all of my VBA is made from adjusting code that other people have created. I recently updated my Mac from High Sierra to Big Sur 11.6. I was using Microsoft Office Mac 2011 but I had to upgrade this as Mac 2011 no longer works on more recently operating systems. The current version of office is Microsoft Office 2021 version 16.54.

The code selects a range of cells in Excel, copys them, opens up Microsoft Word, pastes them then converts the table to text. Here is the code that works perfectly fine on Microsoft Office Mac 2011:

Sub ExportToWord2()
Dim wdApp As Object, wdDoc As Object, wdRng As Object
On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then
Set wdApp = CreateObject("Word.Application")
End If
On Error GoTo 0
Set wdDoc = wdApp.Documents.Add
Sheets("TextForReport").Range("B39:B450").Copy
Set wdRng = wdDoc.Range
With wdRng
.Collapse Direction:=0 'wdCollapseEnd
.Paste
.End = .Tables(1).Range.End + 1
.Tables(1).ConvertToText 1 'wdSeparateByTabs
With .ParagraphFormat
.SpaceAfter = 0
.SpaceBefore = 0
End With
End With

wdApp.Visible = True
End Sub

Through running the debug line by line the issue seems to be at the '.paste' part. I do not get an error code as Excel just completely crashes and I have to Force Quit.

Does anyone know how to fix this? I will be eternally grateful if anyone can help!

Many thanks,

Adam

 

[AdamPickard]

Further update. I found this thread that has some similarities with my issue:

Mac VBA help to open Word from Excel

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...

www.mrexcel.com

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

I tried using this code instead (with modifying the copy part to Sheets("TextForReport").Range("B39:B450").Copy). I then ran the code line by line and it still again crashes at .paste

 

[Jon Peltier]

Mac Office 2011 ran its apps without placing them in a sandbox. Since then 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, 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.

 

[AdamPickard]

Hello Jon,

Thank you very much for taking the time to read through this! I did indeed come across all of the things you mentioned. I have managed to make a little progress that you might find interesting (and might lead you to be able to help me find a solution).

If I run the code and leave it for 1-2 minutes, it will actually copy and paste all of the data from Excel to Word. This message pops up: 'Microsoft Excel is waiting for another application to complete an OLE action.'

I just need to know why it takes 1-2 minutes to load and what I can do to fix this? The code literally ran instantly on MS Office 2011.

Any ideas?

Many thanks,

Adam

 

[Jon Peltier]

I know it ran instantly in Mac Office 2011, in fact, it ran faster than in Windows Office 2013.

I spent a lot of time trying to get this to work in Mac Office 2016 and later. Sometimes it would work, but usually it just waited a long long time (waiting for another application...). I think this is one of the few places where Mac Office and especially VBA is just never going to be on par with Windows.

 

[AdamPickard]

That is indeed a shame! I thought I had found a potential solution with DDE but after searching for the option in Mac Excel I realised that it does not exist on Mac! Here is what I found:

https://answers.microsoft.com/en-us/msoffice/forum/all/dde-in-mac-os-excel/cb13f95a-d22b-410e-a597-3b6b18048c9b

I shall accept defeat that I cannot resolve my issue!

 

[Jim Gordon]

I am able to reproduce the problem. I reported this issue to Microsoft today.