Problem copying table data as a picture onto chartsheet

[theLuggage]

I'm having a strange problem. I'm creating a special chart which has a pie
chart on it as well as some tabular data that is formatted the way we want it
on a worksheet, then pasted onto the chartsheet as a picture. (The pie chart
aspect is working fine. I'm just having a problem with the tabular data.)

When I copy the tabular data manually by selecting the data on the
worksheet, then going to the Home tab and choosing the Paste menu dropdown,
then "As Picture", then "Copy as picture...". In the resulting dialog box, I
choose "As shown on screen" and "Bitmap". Then I switch to the chartsheet
and paste the picture onto the sheet (I just hit ctrl-v).

The tablular data looks great.

Then I used the macro recorder to record those actions and here's the code I
get:
Sheets("ScratchArea").Select
Range("A22:B29").Select
Selection.CopyPicture Appearance:=xlScreen, Format:=xlBitmap
Sheets("Figure 21C").Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.Paste


When I run this code, all I get is a blank picture box on the Chartsheet.
The tabular data is not there inside the picture box.

I've tried different variations on the recorded code and they all give me
the same result. Here's one of those variations:

Sheets("ScratchArea").Select
Range("A22:B29").Select
Selection.Copy
ActiveWorkbook.Sheets("Figure 21C").Select
ActiveChart.ChartArea.Select
ActiveChart.Pictures.Paste.Select


Any help would be most appreciated. I can send sample worksheets if needed.

 

[joel]

this works in 2003


With Sheets("ScratchArea")
..Range("A22:B29").CopyPicture Appearance:=xlScreen, _
Format:=xlBitmap

End With

With Sheets("Figure 21C")

..ChartObjects("Chart 1").Activate
ActiveChart.PlotArea.Select
ActiveChart.Paste

End With

 

[Jie Wang [MSFT]]

Hi,

You can also try this (I wrote an run it without any problems on Excel
2007):

Sheets("ScratchArea").Select
Range("A22:B29").Select

Selection.CopyPicture Appearance:=xlScreen, Format:=xlPicture

Sheets("Figure 21C").Select
ActiveChart.ChartArea.Select
ActiveChart.Pictures.Paste.Select

Please let me know how it goes.

Thanks,
Jie Wang

Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

Note: MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 2 business days is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions. Issues of this
nature are best handled working with a dedicated Microsoft Support Engineer
by contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[theLuggage]

Thanks for your suggestion. I was away over the holidays, hence the delay in
my response.

When I try your code, I get this error when it executes the
"ActiveChart.Pictures.Paste.Select" code:


Error number: 1004
Error description: Unable to get the Paste property of the Pictures class

 

[theLuggage]

Thanks for the suggestion.

I still get the blank box when I run this in Excel 2007.

 

[joel]

My code should work if the sheet names and chart object name is correct
Yo need to activate the chartobject like I did below then select th
plot area.

With Sheets("ScratchArea")
.Range("A22:B29").CopyPicture Appearance:=xlScreen, _
Format:=xlBitmap

End With

With Sheets("Figure 21C")

.ChartObjects("Chart 1").Activate
ActiveChart.PlotArea.Select
ActiveChart.Paste

End Wit

 

[theLuggage]

Your code works, in the sense that it executes without giving any errors,
but I still just get a blank picture box with nothing in it.

It looks to me like the problem is with the copy itself, rather than the
paste because I ran the code up to the point where it does the .CopyPicture,
then stopped it.

Then I switched to Word 2007 and did a Ctrl-V just to see what was placed on
the clipboard, and all I get is an empty picture box.

This is really weird. I wonder if this is a service pack issue? Perhaps I
have (or don't have) some Office update that you do (or don't) have.

 

[theLuggage]

I changed the code from:
"ActiveChart.Pictures.Paste.Select"

to:

"ActiveSheet.Pictures.Paste.Select"

And it worked fine.

It looks like the trick is to use "Format:=xlPicture" instead of xlBitmap.

Thanks for the help.

 

[Jie Wang [MSFT]]

Alright, when I tested the code, I used a chart sheet so my code ended up
with "ActiveChart".

Good to know it's working now.

Have a nice day!

Jie Wang

Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

Note: MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 2 business days is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions. Issues of this
nature are best handled working with a dedicated Microsoft Support Engineer
by contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.