none
Code to create Word Document from Excel VBA errors on Mac RRS feed

  • Question

  • I have Excel VBA code that creates a Word Document using the contents of the worksheet. It works fine on a PC, but on a Mac it gives an Out of Memory error while creating the Object Variable for the Word Application. Here is the code:

    Sub XL2Word()
        Dim wdApp As Word.Application
        Dim wdDoc As Word.Document
        Dim ws As Excel.Worksheet
        Dim HdrText As String
        Dim HdrRange As Word.Range
        
        Application.ScreenUpdating = False
        Set wdApp = New Word.Application 'Throws an error in Mac
        Set wdDoc = wdApp.Documents.Add
        
        Set ws = ActiveSheet
        With wdApp
            .Visible = True
            With wdDoc.Range
                .InsertAfter vbCr
                ws.UsedRange.Copy
                .Characters.Last.Paste
                With .Tables(.Tables.Count).Range.ParagraphFormat
                    .LineSpacingRule = wdLineSpaceSingle
                    .SpaceBefore = 0
                    .SpaceAfter = 0
                End With
                wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range.InsertParagraphAfter
                If Not ws.Name = Worksheets(Worksheets.Count).Name Then .InsertAfter Chr(12)
            End With
        End With
        
        Set HdrRange = wdDoc.Sections.Item(1).Headers(wdHeaderFooterPrimary).Range
        HdrText = "This is where the header goes"
        HdrRange.Text = HdrText
        
        With wdDoc.Sections(1)
            .Footers(wdHeaderFooterPrimary).PageNumbers.Add _
            PageNumberAlignment:=wdAlignPageNumberLeft, _
            FirstPage:=True
        End With
        
        Set wdDoc = Nothing: Set wdApp = Nothing: Set ws = Nothing
    End Sub

    Tuesday, February 25, 2020 6:46 PM

Answers

  • There have been problems in this general area for some time. The trouble is that some systems seem to experience these problems and others don't. I just tried your code here (Mac Excel 16.34, Mac OS Catalina) and had the same problem. Word starts, but then I see the error. Trying to use

    Set wdApp = CreateObject("Word.Application")

    or

    Set wdApp = GetObject(,"Word.Application")

    do not work either.

    IMO one thing you should do is use the "smiley" feature in Excel to report this problem, referencing this thread. As things stand, the other main alternative would be to try to use AppleScript to perform this function.

    I had a similar problem in Mac Word a while back. Originally, I did a completely fresh installation of Mac OS and Office 365 (once by installing from the Office 365 site, and once by installing from the App Store). No solution. So in my view, this problem is entirely Microsoft's to investigate and either fix or publish the instructions necessary to make it work.

    Unfortunately, that is only likely to happen if people on the dev. team at Microsoft can easily reproduce the error. My belief - and that is all it is - is that they have unwittingly made some change on their machines that means they do not encounter this error.

    In the latest update to *Word*, something did change - previously, I couldn't return a reference to an Excel object via either New Excel.Application, CreateObject or GetObject. Now what happens is that CreateObject("Excel.Application") returns an Object reference, but to a Workbook object rather than the Application object that it should return. I can work with that for now, but unfortunately it doesn't help with the Excel problem that you have.


    Peter Jamieson

    • Marked as answer by todtown Wednesday, February 26, 2020 1:20 PM
    Wednesday, February 26, 2020 10:57 AM

All replies

  • There have been problems in this general area for some time. The trouble is that some systems seem to experience these problems and others don't. I just tried your code here (Mac Excel 16.34, Mac OS Catalina) and had the same problem. Word starts, but then I see the error. Trying to use

    Set wdApp = CreateObject("Word.Application")

    or

    Set wdApp = GetObject(,"Word.Application")

    do not work either.

    IMO one thing you should do is use the "smiley" feature in Excel to report this problem, referencing this thread. As things stand, the other main alternative would be to try to use AppleScript to perform this function.

    I had a similar problem in Mac Word a while back. Originally, I did a completely fresh installation of Mac OS and Office 365 (once by installing from the Office 365 site, and once by installing from the App Store). No solution. So in my view, this problem is entirely Microsoft's to investigate and either fix or publish the instructions necessary to make it work.

    Unfortunately, that is only likely to happen if people on the dev. team at Microsoft can easily reproduce the error. My belief - and that is all it is - is that they have unwittingly made some change on their machines that means they do not encounter this error.

    In the latest update to *Word*, something did change - previously, I couldn't return a reference to an Excel object via either New Excel.Application, CreateObject or GetObject. Now what happens is that CreateObject("Excel.Application") returns an Object reference, but to a Workbook object rather than the Application object that it should return. I can work with that for now, but unfortunately it doesn't help with the Excel problem that you have.


    Peter Jamieson

    • Marked as answer by todtown Wednesday, February 26, 2020 1:20 PM
    Wednesday, February 26, 2020 10:57 AM
  • Thanks for the in depth answer, Peter. 
    Wednesday, February 26, 2020 1:21 PM