编辑嵌入式Excel OLE对象中表的列名称

 

如果你运行下面的代码，你会得到一个非常有趣的结果（只运行PowerPoint，在运行之前关闭所有Excel实例）： 

'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

嵌入式对象已成功创建，并且表中存在指定的数据。 但是，当您单击嵌入的对象时，会出现以下错误：

没有足够的内存可用于阅读工作表。

不能再以任何其他方式访问此对象，并且在关闭/打开文档并重新启动时，对象的损坏特性仍然存在。 我已验证此问题发生在除我已测试过的所有系统上（PowerPoint / Excel 2016，Windows 7 X64）。

题

所以我的问题是，任何人都可以重现这一点，如果有的话，为什么会发生这种情况？ 如果将行“单元格（1，1）”更改为“单元格（2，1）”，则没有问题，就好像编辑表格头部会导致某种特殊行为，这与编辑行不同其他细胞。

研究

  • 实际上并没有写出有关这方面的文章，大部分内容都与这个特定问题无关。

  • 这篇文章声称，这是一个安装了太多字体的问题（> 600）。 我测试了这个，我只有241个安装...

  • 有很多没有答案的帖子（这个，这个，这个，以及这个）并没有分配到那里。

  • 有些帖子是完全不相关的，再次没有分配到那里。

  • 我已经在MS Word中测试了相同的代码，似乎工作正常，问题似乎与PowerPoint分离

  • 我试过在代码（破碎的对象）中做一个版本，另一个是手工（工作对象），保存它们并比较二进制输出（只有嵌入对象）。 这听起来很酷，但它并没有给我更多的见解。 我无法单独使用Excel打开嵌入对象，因为对象似乎以专有格式存储。 二进制的中央区域显示不同，但我不知道如何或为什么。 到目前为止，我还没有找到一种方法将其解码为人类可读的信息。

  • 在经过了很长的延迟并且有适当的归因之后，我已经将此发布到微软论坛。 也许有人在那里有一些洞察力。 我将积极维护这两个职位。 如果我100％相信这是一个错误，我甚至可以考虑在这里开放一个问题。

  • 您可以通过永不关闭OLEObject来完全避免此问题，这会在2010年造成问题，尤其是与相关图表行为结合使用时，会显示孤立的Excel窗口。 不是一个好的用户体验。 我想我可以在后台打开一个隐藏的Excel窗口，然后在我完成嵌入式事物的工作时终止...

  • 我运行的版本是：Microsoft Office 365 ProPlus：版本1705（Build 8201.3103即点即用），但我也看到了Microsoft Office Standard 2010版本14.0.7015.1000（32位）上的问题。 这张桌子的问题似乎与其他任何版本的办公室都一样，但我不知道这是否会影响2010年以前的Office版本？

    • 更新1

    我用图表尝试了同样的事情： 

    '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

    如果更改标题行值，则不能再访问嵌入对象（“单元格（1，2）”），如果更改另一个值（“单元格（2，1）”），它将正常运行。 我认为这是同样的问题，我无法在运行此代码后打开图表数据。 如果我尝试以编程方式访问它，我得到以下错误：

    运行时错误'-2147467259（80004005）'：

    对象'ChartData'的方法'工作簿'失败

    虽然只是2016年的一个问题，但我在2010年尝试了一些稍微不同的事情，但没有看到任何问题。

    更新2

    我终于明白为什么我不能在另一个系统上重现这个问题。 此问题仅在进行更改后关闭所有excel实例时才会显示。 这意味着，如果您在运行此代码时打开了一个单独的（无关的）excel窗口，则不会看到问题。

    只有在PowerPoint单独运行时才能复制此问题，而不打开任何其他Excel电子表格。

    我可以在Windows10-64 / Excel2013-64上一致地重现您的问题。 这是一个错误，我们只能试图检查到底发生了什么问题。

    当通过VBA更改表头时， ListColumn顽固地拒绝更新其名称。 无论您是更改标题单元格还是显式列出ListColumn的名称，都会发生这种情况！ 它只会在您编辑工作簿并手动更改单元格时更新，而不是从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

    结果很明显： ListObject表被损坏，因为它内部保存了列名（即Column1 ），它在标题中找不到（标题为fewewq ）。 这导致观察到的错误，不幸的是显示的错误信息并不总是准确的。

    当Excel实例已经运行时，行为会改变，并且ListColumn的名称会更新。 在编辑头文件时，似乎更新表格内部数据的“组件”在PowerPoint VBA中编辑时“未加载”。 只有当：

  • 用PPT 手动编辑工作簿

  • 您有一个Excel实例正在运行

    • 常见的因素是加载了一些编辑器组件，并且该编辑器是在编辑标题时更新内部表格数据的编辑器。

    你在答案中发现的一个很好的解决方法是在操作之前打开一个xlApp，然后关闭它，这与这些观察结果是一致的。

    重要的是， Chart对象（ 更新1中 ）发生的“其他”问题确实与您正确假设的问题（“我认为它是同样的问题”）是同一个问题。 创建的图表链接到工作表中的ListObject表，并且该表的第一行具有其标题。 因此，当您更改标题中的单元格时， ListColumn的名称不会更新，从而导致相同的损坏问题。

    更新：另一个轻量级的解决方法

    在评论提出了关于打开之前的Excel应用程序的解决方法的疑虑后，我试图找到一个“更轻”的解决方法并找到了一个。

    深信这个问题是由于未能更新表格中ListColumn的名称ListColumn ，我找到了一种“强制它”更新其名称的方法。 解决方法包括两个步骤：

    将表格的范围向右扩展一列，然后立即将其缩回到原始范围。

    这个操作只是迫使表重新计算它的列名，就是这样！ 现在表格的列名是正确的，问题就消失了。 

    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

    完成此操作后，您可以验证嵌入的工作表是否可编辑，然后您可以关闭然后打开演示文稿，并且不会发现任何问题。 我希望这有帮助 ：）

    我发现了一个非常“真棒”的解决方法，至少对于表格来说： 

    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

    图表版本： 

    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

    当然，我对答案并不满意，因为它只是避免了这个问题，而不是解决根本原因。 我仍然想更好地了解导致此行为的原因。 尽管如此，在真正的VBA中，这可能是唯一可行的选择。

    它可以正常工作（不添加Excel参考和Excel常量的实际值，在Win7x64上使用PowerPoint 2010x86）： 

    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

    只是不知道为什么你设置对象，但不使用它们。

    链接地址: http://www.djcxy.com/p/45683.html

    上一篇: Editting Column Name of Table in Embedded Excel OLE Object

    下一篇: How to use VBA in PowerPoint to open an embedded OLE object