通过VBA复制粘贴图表时出错 [英] Error when copy pasting a chart through VBA

问题描述

我一直在使用此代码跨多个范围和图表进行复制.但是，随着我代码的增长，它似乎崩溃了，用谷歌搜索了这个问题，我认为这是由于图表/范围未正确复制到剪贴板缓存或从剪贴板缓存复制而引起的.有办法避免这种错误吗?

I have been using this code to copy across a number of ranges and charts. However as my code has grown it appears to fall over, having googled around the issue i think it is caused by the chart/range not correctly being copied to/from the clipboard cache. Is there a way to avoid this error?

错误-运行时错误'-2147188160(80048248)':Shapes.PasteSpecial:无效的请求.剪贴板为空或包含了可能无法粘贴到此处的数据"

Error - " Run-time error '-2147188160 (80048248)': Shapes.PasteSpecial :Invalid request. Clipboard is empty or contains data which may not be pasted here"

Public Sub CopyPasteHeadcountTopGraph()
    If PPT Is Nothing Then Exit Sub
    If PPT_pres Is Nothing Then Exit Sub

    Dim rng As Range
    Dim mySlide As Object
    Dim myShape As Object
    Dim cht As Chart

    Set mySlide = PPT_pres.Slides(6)

    With mySlide
    .Select
    Set cht = ThisWorkbook.Worksheets("Headcount").ChartObjects("HcChart").Chart

       cht.CopyPicture Appearance:=xlScreen, Format:=xlPicture, Size:=xlScreen
       .Shapes.Paste.Select 'ERROR HERE

        '''''''''''''''''''''''''''''''''
        'Paste as Chart and break link. '
        '''''''''''''''''''''''''''''''''
        'cht.ChartArea.Copy
        '.Shapes.Paste.Select


    'With .Shapes("HcChart")
        '.LinkFormat.BreakLink
    'End With

        PPT_pres.Windows(1).Selection.ShapeRange.Left = 35
        PPT_pres.Windows(1).Selection.ShapeRange.Top = 110
        PPT_pres.Windows(1).Selection.ShapeRange.Width = 655
        PPT_pres.Windows(1).Selection.ShapeRange.Height = 300

        End With

    'Clear The Clipboard
    Application.CutCopyMode = False
    Application.Wait (Now + TimeValue("00:00:01"))

End Sub

推荐答案

通常，当VBA仍未准备好要处理时，它们便会开始处理这些对象.当VBA尝试粘贴时，即使复制对象也可能无法完成(即，整个对象未完全提交到剪贴板).

Often VBA starts working on objects when these objects are still not ready to be worked on. Even copying an object may not be finished (i.e., the whole object is not totally committed to the clipboard) when VBA tries to paste.

我发现将某些操作放在单独的过程中可能足以使VBA等待一个后台进程完成，然后再启动下一个进程.

I've found that putting certain operations into a separate procedure may be enough to make VBA to wait for one background process to finish before kicking off the next.

例如，在下面的代码中，我已将粘贴"移出了主过程.这样一来，VBA会等到粘贴之前完成复制，再等粘贴完成之后再放置粘贴的图表.

For example, in the code below, I've moved the Paste out of the main procedure. This makes VBA wait until the copy is done before pasting, and also until the paste is done before positioning the pasted chart.

实际上，我经常具有三个独立的功能，这些功能由主要子功能调用:复制图表，粘贴图表和放置图表.

In fact, I often have three separate functions which are called by the main sub: copying the chart, pasting the chart, and positioning the chart.

Public Sub CopyPasteHeadcountTopGraph()
    If PPT Is Nothing Then Exit Sub
    If PPT_pres Is Nothing Then Exit Sub

    Dim rng As Range
    Dim mySlide As Object
    Dim myShape As Object
    Dim cht As Chart

    Set mySlide = PPT_pres.Slides(6)

    With mySlide
    .Select
    Set cht = ThisWorkbook.Worksheets("Headcount").ChartObjects("HcChart").Chart

       cht.CopyPicture Appearance:=xlScreen, Format:=xlPicture, Size:=xlScreen

       '''''''''''''''''''''''''''''''''''
       '' .Shapes.Paste.Select 'ERROR HERE
       '''''''''''''''''''''''''''''''''''

       PasteChartIntoSlide mySlide

        PPT_pres.Windows(1).Selection.ShapeRange.Left = 35
        PPT_pres.Windows(1).Selection.ShapeRange.Top = 110
        PPT_pres.Windows(1).Selection.ShapeRange.Width = 655
        PPT_pres.Windows(1).Selection.ShapeRange.Height = 300

        End With

    'Clear The Clipboard
    Application.CutCopyMode = False
    Application.Wait (Now + TimeValue("00:00:01"))

End Sub

Function PasteChartIntoSlide(theSlide As Object) As Object
    theSlide.Shapes.Paste.Select
End Function

这篇关于通过VBA复制粘贴图表时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！