CopyPicture only works second time

A

AllanS

I have a strange problem with Excel 2003...
I have made a button which copies the charts of the active sheet to the
clipboard using the CopyPicture method, but it doesn't work the first
time!!

The code looks like this:
Sub copyGraphs()
Dim i As Integer
Dim graphs() As Variant
Dim test As Variant
Dim asRange As Variant
Dim numShapes As Integer
Dim numAutoShapes As Integer

With ActiveSheet.Shapes
numShapes = .Count
If numShapes > 1 Then
numAutoShapes = 0
ReDim graphs(1 To numShapes)
For i = 1 To numShapes
If .Item(i).Type = msoChart Then
numAutoShapes = numAutoShapes + 1
grafer(numAutoShapes) = .Item(i).Name
End If
Next
If numAutoShapes > 1 Then
ReDim Preserve graphs(1 To numAutoShapes)
Set asRange = .Range(graphs)
asRange.Select
Selection.CopyPicture Appearance:=xlScreen,
Format:=xlPicture
End If
End If
End With
End Sub

When I test it, I do the following:
1) I open the workbook, activates a sheet with several Chart objects
and push the 'Copy to clipboard' button
2) I start Word and choose Edit -> Paste
That gives only a small icon instead of the chart objects.
3) In Excel I push the 'Copy to clipboard' button again.
4) In Word I choose Edit -> Paste
I now get the correct graphs as they are supposed to...

What am I doing wrong??
 
T

Tom Ogilvy

I am not sure your test is definitive.

After you press the button the first time, immediately go to a new worksheet
and do Edit=>Paste

If you get your graphs, then it isn't your macro. Perhaps whatever steps
you are following clears the clipboard.
 
A

AllanS

I have been able to narrow the problem a bit.
I have found out that I need to have the "destination" application
started first !?
If I start my workbook and press 'Copy to clipboard' and THEN start
word, and use Edit->Paste it does NOT work.
If I start Word first, then start my workbook and press 'Copy to
clipboard' and use Edit->Paste in Word then it DOES work.

And it has to be the "destination" application. I tried having my Excel
code start Word in the background, and that solved the problem, if I
copy to Word, but it doesn't work if I copy to PowerPoint...

Does anyone have a reasonable explanation for this behaviour?

Regards
Allan
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top