CODEWITHSUNDEEP
excelvbapowerpointole
David Rogers
David Rogers

Reputation: 2643

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

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:

enter image description here

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

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.

Upvotes: 5

Views: 1749

Answers (3)

A.S.H
A.S.H

Reputation: 29332

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 :)

Upvotes: 1

David Rogers
David Rogers

Reputation: 2643

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.

Upvotes: 1

PatricK
PatricK

Reputation: 6433

It works fine with below (without adding Excel reference and actual value of the Excel constants, using PowerPoint 2010x86 on Win7x64):

Option Explicit

Sub test()
    Dim sld As Slide
    Dim shp As Shape

    Set sld = ActivePresentation.Slides.Add(1, ppLayoutBlank)
    Set shp = sld.Shapes.AddOLEObject(30, 30, 50, 50, "Excel.Sheet")

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

    shp.OLEFormat.Object.Close

End Sub

Just not sure why you Set objects but not using them.

Upvotes: 0

Related Questions