Powerpoint VBA - Editting Column Name of Table in Embedded Excel OLE Object

Question

If you run the following code you get quite an interesting result(with only PowerPoint running, close all instances of Excel before running):

'Optional - Include the "Microsoft Excel 16.0 Object Library"
Option Explicit

Public Sub test()    
    Dim oslide As slide
    Set oslide = ActivePresentation.Slides.add(1, ppLayoutBlank)

    Dim oshape As Shape 
    Set oshape = oslide.Shapes.AddOLEObject(30, 30, 50, 50, "Excel.Sheet")

    oshape.OLEFormat.Object.Sheets(1).ListObjects.add(1) 'xlSrcRange
    oshape.OLEFormat.Object.Sheets(1).Cells(1, 1) = "fewewq"

    oshape.OLEFormat.Object.Close
End Sub

The embedded object is created successfully and table is present with the specified data. However, when you click on the embedded object you get the following error:

There isn't enough memory available to read Worksheet.

This object can no longer be accessed any other way, and the corrupted nature of the object persists on close/open of the document and restart. I have verified this issue occurs on all but one systems I've tested in on (PowerPoint/Excel 2016, Windows 7 X64).

Question

So my question is, can anyone else reproduce this, and if so why is this happening? If you change the line "Cells(1, 1)" to "Cells(2, 1)" there is no issue, it appears as though editing the head of the table causes some sort of special behavior that's different than editing the rows or other cells.

Research

• There really isn't allot written about this, most of the stuff is not related to this particular issue.

• This post claims that it's an issue with too many font's installed(>600). I tested this, I only have 241 installed...

• There is a whole lots of posts with no answers(this, this, this, and this) not allot to go on there.

• There are some posts that are completely unrelated, again not allot to go on there.

• I've tested the same code in MS Word, seems to work fine, issue seems isolated to PowerPoint

• I've tried doing one version in code(broken object), and another by hand(working object), saving them and comparing the binary output(of only the embedded objects). This sounds cool, but it doesn't grant me any greater insight. I can't open with Excel the embedded objects separately as the objects seems to be stored in a proprietary format. The central region of binary appears different, but I'm not sure how or why. So far I have not discovered a way to decode this into a human readable information.

• After a significant delay and with proper attribution, I have cross posted this to the Microsoft forums. Maybe someone has some insight over there. I will actively maintain both posts. If I were 100% convinced this was a bug I might even consider opening an issue here.

• You can completely avoid this issue by not ever closing the OLEObject, this causes problems in 2010, especially when combined with the associated chart behavior, you get orphaned excel windows displayed. Not a good user experience. I guess I could open a hidden excel window in the background and then terminate when I'm done working on embedded things...

• I am running version: Microsoft Office 365 ProPlus: Version 1705 (Build 8201.3103 Click-to-Run), but I've also seen the issue on Microsoft Office Standard 2010, Version 14.0.7015.1000 (32-bit). The issue with the table seems to be the same on every other version of office, though I wonder if this affects pre-2010 versions of office?

Update 1

I tried the same thing with charts:

'Include the "Microsoft Excel 16.0 Object Library"
Option Explicit

Sub test()
    Dim sld As slide
    Dim shp As Shape
    Dim pptWorkbook As Workbook

    Set sld = ActivePresentation.Slides.Add(1, ppLayoutBlank)
    Set shp = sld.Shapes.AddChart
    Set pptWorkbook = shp.Chart.ChartData.Workbook
    pptWorkbook.Close SaveChanges:=True

    Set pptWorkbook = shp.Chart.ChartData.Workbook
    pptWorkbook.Sheets(1).Cells(1, 2) = "fewewq"

    Application.ActivePresentation.Save
    pptWorkbook.Close SaveChanges:=True
End Sub

If you change the header row value you can no longer access the embedded object("Cells(1, 2)"), if you change another value("Cells(2, 1)") it runs fine. I assume it's the same problem, I can't open the chart data after this code is run. If I try to access it programmatically I get the following error:

Run-time error '-2147467259 (80004005)':

Method 'Workbook' of object 'ChartData' failed

Only an issue on 2016 though, I tried something slightly different for 2010 and didn't see any issues.

Update 2

I finally figured out why I couldn't reproduce this issue on another system. This issue only appears when all instances of excel are closed after the change is made. That means if you have a separate (unrelated) excel window open when you run this code, you will not see the issue.

This issue can only be reproduced when PowerPoint is running alone, without any other Excel spreadsheets open.

Answer 1

I could reproduce your issue consistently on Windows10-64/Excel2013-64. It's a bug, we can only try to inspect what exactly is going wrong.

When changing the table's header through VBA, the ListColumn obstinately refuses to update its name. This occurs whether you changing the header cell or explicitly the ListColumn's Name! It only updates if you edit the Workbook and change the cell by hand, but not from VBA:

Public Sub Test()
    Dim oslide As Slide
    Set oslide = ActivePresentation.Slides.Add(1, ppLayoutBlank)

    Dim oshape As Shape
    Set oshape = oslide.Shapes.AddOLEObject(30, 30, 250, 250, "Excel.Sheet")

    With oshape.OLEFormat.Object
      .Sheets(1).ListObjects.Add 1, .Sheets(1).Range("B2:D5") ' <-- put it anywhere


      .Sheets(1).ListObjects(1).ListColumns(1).Name = "fewewq" ' <-- whether like this
      '.Sheets(1).Range("B2").Value = "fewewq"                 ' <-- or like this

      Debug.Print .Sheets(1).ListObjects(1).ListColumns(1).Range.Cells(1).Value 'fewewq

      Debug.Print .Sheets(1).ListObjects(1).ListColumns(1).Name
      ''''''''''''''' Still prints Column1 ! ''''''''''''''''''

      .Close
    End With
End Sub

The result is obvious: the ListObject table gets corrupted, because it has internally saved column names (i.e. Column1) that it does not find in the header (header is fewewq). This leads to the observed bug, the displayed error messages are not always accurate, unfortunately.

When an Excel instance is already running, the behavior changes and the ListColumn's name does update. It seems that the "component" that updates the table's internal data when it's header is edited, is "not loaded" when editing inside PowerPoint VBA. Only when either:

• You edit the workbook in-place in PPT, by hand

• You have an Excel instance running

The common factor is that there's some editor component loaded, and this editor is the one that updates the internal table's data when the header is edited.

The good workaround that you found in your answer, which is to open an xlApp prior to the action, then close it after, is consistent with these observations.

Importantly, the "other" problem that happens with the Chart object (in Update 1) is indeed the same problem as you correctly assumed ("I assume it's the same problem"). The created chart is linked to a ListObject table in the worksheet, and that table has its header on the first row. So when you change a cell in the header, the name of the ListColumn doesn't update, leading to the same corruptness problem.

UPDATE: another, lightweight workaround

After the comments raised concerns related to the workaround of opening a prior Excel app, I tried to find a "lighter" workaround and found one.

Convinced that the issue is due to the failure to update the ListColumns name in the table, I found a way to "force it" update its names. The workaround consist in two steps:

Expand the table's range one column to the right, then immediately shrink it back to the original range.

This operation simply forces the table to re-calculate its columns names, and that's it! Now the table's column names are correct and the issue disappeared.

Public Sub Workaround()
  Dim oslide As Slide: Set oslide = ActivePresentation.Slides.Add(1, ppLayoutBlank)
  Dim oshape As Shape: Set oshape = oslide.Shapes.AddOLEObject(30, 30, 250, 250, "Excel.Sheet")

  With oshape.OLEFormat.Object.Sheets(1)
    .ListObjects.Add 1 ' xlRange
    .Range("A1").Value = "fewewq"

   ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
   ' Expand the table one column to the right than shrink it back
   '
    With .ListObjects(1)
      .Resize .Range.Resize(, .Range.Columns.Count + 1)
      .Resize .Range.Resize(, .Range.Columns.Count - 1)
    End With
   '
   ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  End With

  With oshape.OLEFormat.Object
    Debug.Print .Sheets(1).ListObjects(1).ListColumns(1).Range.Cells(1).Value ' fewewq
    Debug.Print .Sheets(1).ListObjects(1).ListColumns(1).Name ' Now prints fewewq !
    .Close
  End With
End Sub

After this operation, you can verify that the embedded worksheet is editable, and you can close then open the presentation and you will not find any issues. I hope this helps :)

Answer 2

I found a really "awesome" workaround, at least for the tables:

Public Sub CreateTable()

    'Create a dummy excel object in the background(run this before working with OLE objects)
    Dim xlApp As Object
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Workbooks.Add

    Dim slide As slide: Set slide = ActivePresentation.Slides.Add(1, ppLayoutBlank)
    Dim shp As Shape: Set shp = slide.Shapes.AddOLEObject(30, 30, 50, 50, "Excel.Sheet")

    shp.OLEFormat.Object.Sheets(1).ListObjects.Add (1) 'xlSrcRange
    shp.OLEFormat.Object.Sheets(1).Cells(1, 1) = "fewewq"

    shp.OLEFormat.Object.Close

    'Kill it when the work is done
    xlApp.Application.Quit

End Sub

Chart Version:

Public Sub CreateChart()

    'Create a dummy excel object in the background(run this before working with OLE objects)
    Dim xlApp As Object
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Workbooks.Add

    Dim sld As slide
    Dim shp As Shape
    Dim pptWorkbook As Workbook

    Set sld = ActivePresentation.Slides.Add(1, ppLayoutBlank)
    Set shp = sld.Shapes.AddChart

    Set pptWorkbook = shp.Chart.ChartData.Workbook
    pptWorkbook.Close SaveChanges:=True

    'Use the Activate code to open the worksheet, typically only need for 2010
    xlApp.Application.Wait Now + TimeValue("0:00:01")
    shp.Chart.ChartData.Activate
    shp.Chart.ChartData.Workbook.Windows(1).Visible = False

    Set pptWorkbook = shp.Chart.ChartData.Workbook
    pptWorkbook.Sheets(1).Cells(1, 2) = "fewewq"

    Application.ActivePresentation.Save

    'Added a wait condition before closing the document, not sure why this works...
    Excel.Application.Wait Now + TimeValue("0:00:01")
    pptWorkbook.Close SaveChanges:=True

    'Kill it when the work is done
    xlApp.Application.Quit

End Sub

Of course, I'm not satisfied with the answer as it simply avoids the issue rather than addressing the root cause. I would still like to get a better understand of what is causing this behavior. Failing that though, in true VBA fashion, this may be the only viable option.