How to lock clipboard or avoid using it in MS Excel?

Question

I'm writing a program under Excel that generate a lot of spreadsheets by copying / pasting some data from one worksheet to another (example: using a "Layout" worksheet with some header / footer cells which will be copied/pasted to the generated worksheets).

My problem is that, some times (not every time), when running my "generation process", Excel generate this error (sorry this is an English translation from my french Excel error) :

Error 1004 : The 'Paste' method of the '_Worksheet' object has failed

So I'm assuming that there is a problem with the Clipboard (with other software on my computer which probably used the clipboard at the same time :/)

I firstly try to find a way to copy/paste my cells (and other stuff) without using the clipboard, with code like that :

ThisWorkbook.Sheets("Layout").Range("A1").Copy Destination:=ThisWorkbook.Sheets("Test").Range("A1")

or that

ThisWorkbook.Sheets("Test").Range("A1") = ThisWorkbook.Sheets("Layout").Range("A1")

But it seems that we can only copy text or formula, not all the stuff (border, color,...) and also not Chart object (I have ones)!

So I try to find a way to lock/unlock the clipboard during the copy/paste. I found this code to do that:

Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long
Declare Function CloseClipboard Lib "user32" () As Long
Declare Function EmptyClipboard Lib "user32" () As Long

Public Sub Lockk()
    If OpenClipboard(0) = 0 Then
        MsgBox "cannot open clipboard."
    ElseIf EmptyClipboard() = 0 Then
        MsgBox "cannot clear clipboard."
    End If
End Sub

Public Sub Unlockk()
    CloseClipboard
End Sub

It seems working when copying cells: I can lock the clipboard under excel, go to another software (notepad for example), and can't copy paste some data into this software; go back to excel and I can copy/paste data (manually or with a macro).

But:

It seems that pasting a cell will unlock the clipboard (I can lock, go to notepad, notepad has not access to the clipboard, go back to excel, copy/paste a cell, go back to notepad, and then the notepad can access to the clipboard; and I have not unlock explicitly the clipboard). That is not really a problem for me.
After locking the clipboard, we can't copy/past a Chart object (manually or with a macro). With a macro, I get exactly the same error as before).

So, is someone as an idea about how to lock/unlock the clipboard to copy chart object? Or to copy them without using the clipboard?

Edit:

The code used to copy/paste the graph object:

Utils_Clipboard.Lockk
ThisWorkbook.Sheets("Layout").ChartObjects("CHART_TEMPLATE").Copy
DoEvents
worksheet_p.Paste Destination:=where_p
Utils_Clipboard.Unlockk

where worksheet_p is a Worksheet object, adn where_p is a range. Note that without the first and last lines (Lockk the clipboard), it's working fine (except some time).

You need to give time for the copy to happen. See this link http://stackoverflow.com/questions/14257574/excel-vba-run-time-error-1004-copying-images/14258846#14258846 Try adding `doevents` after copying — Siddharth Rout, Jan 16 '13 at 16:13
As said by evoandy in comment of the proposed solution: "The debugger stops on the Paste line so putting DoEvents after it won't do anything." Moreover, I've trie to copy/paste manually (ctrl c / ctrl v) and I think my computer as a lot of time to copy data... (and the graph I've tested is empty). So I've tried your doevent and... nothing :/ — Alexxx, Jan 16 '13 at 16:29
Using `Copy` and `Insert` should preserve formatting, if not cant you omit the destination in `Copy` and use `PasteSpecial`? or is this something you've tried — NickSlash, Jan 16 '13 at 16:30
`OpenClipboard` locks the clipboard against editing, not against access — SeanC, Jan 16 '13 at 16:30
alexx, that is an old comment.... please read that post again... "Try adding doevents after copying" not after "pasting" — Siddharth Rout, Jan 16 '13 at 16:30
Ok, but I've tried to put the "doevent", but the "bug" is still here... — Alexxx, Jan 16 '13 at 16:32
Can you share the exact code that you are using for copying and pasting? — Siddharth Rout, Jan 16 '13 at 16:33
Sorry but I am slightly confused. Why do you need to lock/unlock the clipboard? Whenever you copy something, clipboard is used. You cannot avoid that. If you use `DoEvents` right after copy as you have done above, you shouldn't be getting any errors... — Siddharth Rout, Jan 16 '13 at 17:14
As an "hard-core" alternative, manually apply all the relevant settings of the cell in a `subCopy(Source as Range,Target as Range)` sub where you apply all the relevant properties (value, formula, formats) manually. Not the neatest solution but might solve the issue. — Peter Albert, Jan 16 '13 at 17:33
Second thought: just use your error handler - and resume operation at the copying! So if the error occurs, you simply copy and paste it again! — Peter Albert, Jan 16 '13 at 17:34
@Siddharth Rout : as said, I probably have an other software on my computer which use (and lock?) the clipboard at the same time; and so, I've get an error. So I want to lock it for my personnal use in my vba program. Or to avoid using it but MS Excel vba is not disgned to do that. — Alexxx, Jan 17 '13 at 09:32
Peter Albert : Yes I think I will do that (copy again and again until I will not have error). Or create a method which do all the copy stuff manually (do the job of MS engineer which do not do that!), but it seems really complicated to manage all the cases (cells, merges cells,...) and especially I don't think we can duplicate a chart object without using the Copy/Paste function (and so using the clipboard). — Alexxx, Jan 17 '13 at 09:35
Ok... I've found a partial solution, we can Duplicate a ChartObject (see http://msdn.microsoft.com/en-us/library/office/ff840956.aspx). And it's working between my Clipboard.Lock and Clipboard.Unlock. But, it's duplicate the new chartobject is placed in front of the source one. The seoncd part of the solution is the answer of: How to move this new chart obbject (without using Cut/Paste)? — Alexxx, Jan 17 '13 at 10:24

Alexxx · Accepted Answer · 2013-01-17T15:23:39.243

Ok I've found a solution (maybe not the best one?)

I can use my Utils_Clipboard.Lockk and Utils_Clipboard.Unlockk to ensure that the clipboard will not be used by another software when duplicating (copy/paste) cell, merged cells,...

But it seems that, when the clipboard is locked, we can't copy/paste chart object (manualy by pressing ctrl+c and ctrl+v keys; or automatically in vba with the metod Copy and Past of objects).

My solution for chart object is to use the functions Duplicate (http://msdn.microsoft.com/en-us/library/office/ff840956.aspx) and Move (http://msdn.microsoft.com/en-us/library/office/ff840583.aspx) like this (where worksheet_p is the Worksheet where I want to put the chartobject) :

Utils_Clipboard.Lockk
Dim newchart_l As Shape
Set newchart_l = ThisWorkbook.Sheets("Layout").ChartObjects("CHART_TEMPLATE").Duplicate
newchart_l.Chart.Location xlLocationAsObject, worksheet_p.Name
Utils_Clipboard.Unlockk

Note that the duplicated object is a Shape not a ChartObject (which will make an error when executing the code if we type as a ChartObject).

So ok it's working (and I think there is no need to lock the clipboard here), but the chart object is not where I want (at the correct top/left coordinates on worksheet_p). To do that, I found that we must move the ChartArea (the parent of the duplicated object), but we can't manipulate the "newchart_l" directly (it seems that Excel don't update all its internal variables after the call of Duplicate, why???). So my solution is to firstly retrieve the new duplicate chart object, with this :

Dim ChartObject_m As Chart
Set ChartObject_m = worksheet_p.ChartObjects(worksheet_p.ChartObjects.Count).Chart

And then move the chartarea of that object (where 'where_p' is the range/cell where I want to put my charobject) :

ChartObject_m.ChartArea.Left = where_p.Left
ChartObject_m.ChartArea.Top = where_p.Top

Et voila!

junix · Answer 2 · 2015-02-21T08:19:03.603

Inspired by your solution I have a improved piece of code that I wanted to share. The improvement is, that it does not rely on assumptions like "The order of charts in the ChartObjects-Collection reflects the order of insertion":

Private Function copyGraph(source As ChartObject, pos As Range) As ChartObject
    ' Copies a given graph to the given position and returns the resulting 
    ' ChartObject. The destination position is expected to be a cell in the desired 
    ' target worksheet. The resulting ChartObject will be aligned to the Top/Left-
    ' Border of the given cell. 

    Dim dup As Object
    Dim dstChart As Chart

    ' First just duplicate the graph. This operation leaves it on the original 
    ' worksheet.
    Set dup = source.Duplicate

    ' In case the duplication failed, ... 
    If (Not dup.HasChart) Then
        ' ... we remove the duplicated object and leave the copy function.
        ' This yields a Nothing-reference as return value to signal the error
        ' to the caller.
        dup.Delete
        set copyGraph = Nothing
        Exit Function
    End If

    ' Then we move the graph to the target worksheet passed as parameter. This
    ' gives us the new link to the moved chart.
    '
    ' Excel displays some weired behavior when using the reference returned by ChartObject.Duplicate.
    ' Namely it yields sporadic 1004 runtime errors without further specification. However it seems,
    ' that activating the chart and calling location for ActiveChart gets around this problem.
    '
    ' Therefor the original code:
    ' Set dstChart = dup.Chart.Location(xlLocationAsObject, pos.Parent.Name)
    ' has been replaced with the following
    dup.Chart.parent.Activate
    Set dstChart = ActiveChart.Location(xlLocationAsObject, pos.Parent.Name)

    ' As we relocated the chart as an object, the parent of the chart object is
    ' an instance of ChartObject. Hence we use it as the return value.
    Set copyGraph = dstChart.parent

    ' Finally we move the graph to the requested position passed by the pos 
    ' parameter.
    With copyGraph
        .Top = pos.Top
        .Left = pos.Left
    End With
End Function

I hope this helps other users looking for a simple solution on this issue.

How to lock clipboard or avoid using it in MS Excel?

2 Answers2