MS Office 2019标准版：Excel更新PPT图表功能失效求助

Hey there, let’s dig into why your VBA chart update code is failing specifically in Office 2019 Standard. Since it works reliably across other Office versions, the issue is likely tied to version-specific security restrictions, limited feature access, or ChartData object behavior quirks unique to the Standard edition. Here are the most probable causes and actionable fixes to try:

1. Verify ChartData Object Security Permissions #

Office 2019 Standard may enforce stricter security settings that block programmatic access to the embedded chart workbook.

• Steps to check:
  • Open PowerPoint, navigate to File > Options > Trust Center > Trust Center Settings > Macro Settings
  • Ensure the checkbox for "Trust access to the VBA project object model" is enabled
  • Double-check Protected View settings—make sure your source files aren’t opening in Protected View (this mode blocks VBA-driven edits)

2. Simplify Code to Avoid Unnecessary Workbook Operations #

Your original code closes and reopens the chart workbook twice, which can introduce race conditions or trigger version-specific bugs. Here’s an optimized, more robust version of your code:

Sub Chart_copy_gs(ByRef objpresentation As PowerPoint.Presentation, ByVal blattname As String, ByVal slideNr As Integer, ByVal chartName As String)
    Dim ObjSlide As PowerPoint.Slide
    Dim mychart As PowerPoint.Chart
    Dim wb As Excel.Workbook
    Dim WS As Excel.Worksheet
    
    ' Skip slide selection (unnecessary and can cause UI-related issues)
    Set ObjSlide = objpresentation.Slides(slideNr)
    Set mychart = ObjSlide.Shapes(chartName).Chart
    
    ' Add error handling for ChartData access (catches permission blocks)
    On Error Resume Next
    Set wb = mychart.ChartData.Workbook
    On Error GoTo 0
    
    If wb Is Nothing Then
        MsgBox "Failed to access chart data workbook. Check security settings.", vbExclamation
        Exit Sub
    End If
    
    Set WS = wb.Worksheets(1)
    
    ' Update data in a single operation
    WS.Range("A1:F40").ClearContents
    WS.Range("A1:F40").Value = ThisWorkbook.Worksheets(blattname).Range("A45:F82").Value
    
    ' Force immediate chart refresh before closing the workbook
    mychart.Refresh
    
    ' Save and close the embedded workbook
    wb.Close SaveChanges:=True
    
    ' Clean up objects to prevent memory leaks
    Set WS = Nothing
    Set wb = Nothing
    Set mychart = Nothing
    Set ObjSlide = Nothing
End Sub

Key improvements:

• Removed redundant slide/chart re-selection and workbook reopening
• Added error checking to catch permission-related access failures
• Explicitly calls mychart.Refresh to ensure the chart updates before closing the embedded workbook
• Proper object cleanup to avoid lingering memory issues

3. Fix Potential Embedded Workbook Corruption #

In some cases, the embedded Excel workbook tied to the PowerPoint chart can become corrupted in Office 2019 Standard.

• Fix steps:
  1. In PowerPoint, right-click the problematic chart and select Edit Data to open the embedded workbook
  2. Save and close the embedded workbook manually
  3. Run the optimized VBA code again
  4. If the issue persists, delete the chart, recreate it in PowerPoint (matching your original data structure), and update your code to reference the new chart name

4. Ensure Office 2019 Standard is Fully Updated #

Microsoft regularly patches version-specific bugs related to VBA and Office interop. Outdated installations can cause unexpected behavior.

• Check for updates:
  Open any Office app, go to File > Account > Update Options > Update Now

内容的提问来源于stack exchange，提问作者ruedi