| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
copying all charts as enhanced metafiles
I have a sheet with a number of charts. I want to copy them to another sheet
as a single picture. The following code works, but the picture loses resolution. 'Copies graphs as picture Sheets("Graphs").Select Range("A1:R51").Select Selection.CopyPicture Appearance:=xlScreen, Format:=xlPicture Range("A1").Select 'Pastes graph picture to summary sheet Sheets("Static Summary Sheet").Select Range("A1").Select ActiveSheet.Paste I have replaced the code with: 'Copies graphs as picture Sheets("Graphs").Select ActiveSheet.Shapes.SelectAll Selection.Copy Range("A1").Select 'Pastes graph picture to summary sheet Sheets("Static Summary Sheet").Select Range("D5").Select ActiveSheet.PasteSpecial Format:="Picture (Enhanced Metafile)", Link:=False _ , DisplayAsIcon:=False Selection.Top = 69.75 Selection.Left = 37.5 Now I get an error in the pastespecial line "PasteSpecial method of Worksheet class failed. Any ideas? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
copying all charts as enhanced metafiles
I should add the loss of resolution is in Excel2007 and the error in the
second code is also in Excel2007. Both methods work fine in Excel2003. "Paste charts as enchanced metafile" wrote: I have a sheet with a number of charts. I want to copy them to another sheet as a single picture. The following code works, but the picture loses resolution. 'Copies graphs as picture Sheets("Graphs").Select Range("A1:R51").Select Selection.CopyPicture Appearance:=xlScreen, Format:=xlPicture Range("A1").Select 'Pastes graph picture to summary sheet Sheets("Static Summary Sheet").Select Range("A1").Select ActiveSheet.Paste I have replaced the code with: 'Copies graphs as picture Sheets("Graphs").Select ActiveSheet.Shapes.SelectAll Selection.Copy Range("A1").Select 'Pastes graph picture to summary sheet Sheets("Static Summary Sheet").Select Range("D5").Select ActiveSheet.PasteSpecial Format:="Picture (Enhanced Metafile)", Link:=False _ , DisplayAsIcon:=False Selection.Top = 69.75 Selection.Left = 37.5 Now I get an error in the pastespecial line "PasteSpecial method of Worksheet class failed. Any ideas? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
copying all charts as enhanced metafiles
I added two picture to a worksheet and used your code without any problems.
I'm using excel 2003. Try the same thing. Add two pictures to a blank workbook and see if it works. the code is good. Are both your sheets worksheets? You called one a chart so was it created by adding a chart? "Paste charts as enchanced metafile" wrote: I have a sheet with a number of charts. I want to copy them to another sheet as a single picture. The following code works, but the picture loses resolution. 'Copies graphs as picture Sheets("Graphs").Select Range("A1:R51").Select Selection.CopyPicture Appearance:=xlScreen, Format:=xlPicture Range("A1").Select 'Pastes graph picture to summary sheet Sheets("Static Summary Sheet").Select Range("A1").Select ActiveSheet.Paste I have replaced the code with: 'Copies graphs as picture Sheets("Graphs").Select ActiveSheet.Shapes.SelectAll Selection.Copy Range("A1").Select 'Pastes graph picture to summary sheet Sheets("Static Summary Sheet").Select Range("D5").Select ActiveSheet.PasteSpecial Format:="Picture (Enhanced Metafile)", Link:=False _ , DisplayAsIcon:=False Selection.Top = 69.75 Selection.Left = 37.5 Now I get an error in the pastespecial line "PasteSpecial method of Worksheet class failed. Any ideas? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
copying all charts as enhanced metafiles
CopyPicture doesn't work correctly in Excel 2007, it has been reported.
Just use .Copy and all formats will be copied to the clipboard, then paste with whichever format you want. More here - http://groups.google.co.uk/group/mic...536ac8340331af Regards, Peter T "Paste charts as enchanced metafile" oft.com wrote in message ... I should add the loss of resolution is in Excel2007 and the error in the second code is also in Excel2007. Both methods work fine in Excel2003. "Paste charts as enchanced metafile" wrote: I have a sheet with a number of charts. I want to copy them to another sheet as a single picture. The following code works, but the picture loses resolution. 'Copies graphs as picture Sheets("Graphs").Select Range("A1:R51").Select Selection.CopyPicture Appearance:=xlScreen, Format:=xlPicture Range("A1").Select 'Pastes graph picture to summary sheet Sheets("Static Summary Sheet").Select Range("A1").Select ActiveSheet.Paste I have replaced the code with: 'Copies graphs as picture Sheets("Graphs").Select ActiveSheet.Shapes.SelectAll Selection.Copy Range("A1").Select 'Pastes graph picture to summary sheet Sheets("Static Summary Sheet").Select Range("D5").Select ActiveSheet.PasteSpecial Format:="Picture (Enhanced Metafile)", Link:=False _ , DisplayAsIcon:=False Selection.Top = 69.75 Selection.Left = 37.5 Now I get an error in the pastespecial line "PasteSpecial method of Worksheet class failed. Any ideas? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
copying all charts as enhanced metafiles
Joel,
The code works fine in 2003, not in 2007. "Joel" wrote: I added two picture to a worksheet and used your code without any problems. I'm using excel 2003. Try the same thing. Add two pictures to a blank workbook and see if it works. the code is good. Are both your sheets worksheets? You called one a chart so was it created by adding a chart? "Paste charts as enchanced metafile" wrote: I have a sheet with a number of charts. I want to copy them to another sheet as a single picture. The following code works, but the picture loses resolution. 'Copies graphs as picture Sheets("Graphs").Select Range("A1:R51").Select Selection.CopyPicture Appearance:=xlScreen, Format:=xlPicture Range("A1").Select 'Pastes graph picture to summary sheet Sheets("Static Summary Sheet").Select Range("A1").Select ActiveSheet.Paste I have replaced the code with: 'Copies graphs as picture Sheets("Graphs").Select ActiveSheet.Shapes.SelectAll Selection.Copy Range("A1").Select 'Pastes graph picture to summary sheet Sheets("Static Summary Sheet").Select Range("D5").Select ActiveSheet.PasteSpecial Format:="Picture (Enhanced Metafile)", Link:=False _ , DisplayAsIcon:=False Selection.Top = 69.75 Selection.Left = 37.5 Now I get an error in the pastespecial line "PasteSpecial method of Worksheet class failed. Any ideas? |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
copying all charts as enhanced metafiles
Peter,
..Copy works for a single chart. Any way to select all the charts and paste them all at once? "Peter T" wrote: CopyPicture doesn't work correctly in Excel 2007, it has been reported. Just use .Copy and all formats will be copied to the clipboard, then paste with whichever format you want. More here - http://groups.google.co.uk/group/mic...536ac8340331af Regards, Peter T "Paste charts as enchanced metafile" oft.com wrote in message ... I should add the loss of resolution is in Excel2007 and the error in the second code is also in Excel2007. Both methods work fine in Excel2003. "Paste charts as enchanced metafile" wrote: I have a sheet with a number of charts. I want to copy them to another sheet as a single picture. The following code works, but the picture loses resolution. 'Copies graphs as picture Sheets("Graphs").Select Range("A1:R51").Select Selection.CopyPicture Appearance:=xlScreen, Format:=xlPicture Range("A1").Select 'Pastes graph picture to summary sheet Sheets("Static Summary Sheet").Select Range("A1").Select ActiveSheet.Paste I have replaced the code with: 'Copies graphs as picture Sheets("Graphs").Select ActiveSheet.Shapes.SelectAll Selection.Copy Range("A1").Select 'Pastes graph picture to summary sheet Sheets("Static Summary Sheet").Select Range("D5").Select ActiveSheet.PasteSpecial Format:="Picture (Enhanced Metafile)", Link:=False _ , DisplayAsIcon:=False Selection.Top = 69.75 Selection.Left = 37.5 Now I get an error in the pastespecial line "PasteSpecial method of Worksheet class failed. Any ideas? |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
copying all charts as enhanced metafiles
What a palava!
Sub test() Dim i As Long Dim arr() Dim wsSource As Worksheet, wsDest As Worksheet Set wsSource = Worksheets("Sheet1") Set wsDest = Worksheets("Sheet2") wsSource.Activate With wsSource.ChartObjects ReDim arr(1 To .Count) For i = 1 To UBound(arr) arr(i) = .Item(i).ZOrder Next End With wsSource.DrawingObjects(arr).Select wsSource.DrawingObjects(arr).Copy wsDest.Activate wsDest.Range("B2").Activate wsDest.PasteSpecial Format:="Picture (Enhanced Metafile)" End Sub Lots of select/activate and other stuff in the above I'd normally be advising people not to do - but if needs must for Excel 2007 ! Regards, Peter T "Paste charts as enchanced metafile" oft.com wrote in message ... Peter, .Copy works for a single chart. Any way to select all the charts and paste them all at once? "Peter T" wrote: CopyPicture doesn't work correctly in Excel 2007, it has been reported. Just use .Copy and all formats will be copied to the clipboard, then paste with whichever format you want. More here - http://groups.google.co.uk/group/mic...536ac8340331af Regards, Peter T "Paste charts as enchanced metafile" oft.com wrote in message ... I should add the loss of resolution is in Excel2007 and the error in the second code is also in Excel2007. Both methods work fine in Excel2003. "Paste charts as enchanced metafile" wrote: I have a sheet with a number of charts. I want to copy them to another sheet as a single picture. The following code works, but the picture loses resolution. 'Copies graphs as picture Sheets("Graphs").Select Range("A1:R51").Select Selection.CopyPicture Appearance:=xlScreen, Format:=xlPicture Range("A1").Select 'Pastes graph picture to summary sheet Sheets("Static Summary Sheet").Select Range("A1").Select ActiveSheet.Paste I have replaced the code with: 'Copies graphs as picture Sheets("Graphs").Select ActiveSheet.Shapes.SelectAll Selection.Copy Range("A1").Select 'Pastes graph picture to summary sheet Sheets("Static Summary Sheet").Select Range("D5").Select ActiveSheet.PasteSpecial Format:="Picture (Enhanced Metafile)", Link:=False _ , DisplayAsIcon:=False Selection.Top = 69.75 Selection.Left = 37.5 Now I get an error in the pastespecial line "PasteSpecial method of Worksheet class failed. Any ideas? |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
copying all charts as enhanced metafiles
Thanks for your help.
"Peter T" wrote: What a palava! Sub test() Dim i As Long Dim arr() Dim wsSource As Worksheet, wsDest As Worksheet Set wsSource = Worksheets("Sheet1") Set wsDest = Worksheets("Sheet2") wsSource.Activate With wsSource.ChartObjects ReDim arr(1 To .Count) For i = 1 To UBound(arr) arr(i) = .Item(i).ZOrder Next End With wsSource.DrawingObjects(arr).Select wsSource.DrawingObjects(arr).Copy wsDest.Activate wsDest.Range("B2").Activate wsDest.PasteSpecial Format:="Picture (Enhanced Metafile)" End Sub Lots of select/activate and other stuff in the above I'd normally be advising people not to do - but if needs must for Excel 2007 ! Regards, Peter T "Paste charts as enchanced metafile" oft.com wrote in message ... Peter, .Copy works for a single chart. Any way to select all the charts and paste them all at once? "Peter T" wrote: CopyPicture doesn't work correctly in Excel 2007, it has been reported. Just use .Copy and all formats will be copied to the clipboard, then paste with whichever format you want. More here - http://groups.google.co.uk/group/mic...536ac8340331af Regards, Peter T "Paste charts as enchanced metafile" oft.com wrote in message ... I should add the loss of resolution is in Excel2007 and the error in the second code is also in Excel2007. Both methods work fine in Excel2003. "Paste charts as enchanced metafile" wrote: I have a sheet with a number of charts. I want to copy them to another sheet as a single picture. The following code works, but the picture loses resolution. 'Copies graphs as picture Sheets("Graphs").Select Range("A1:R51").Select Selection.CopyPicture Appearance:=xlScreen, Format:=xlPicture Range("A1").Select 'Pastes graph picture to summary sheet Sheets("Static Summary Sheet").Select Range("A1").Select ActiveSheet.Paste I have replaced the code with: 'Copies graphs as picture Sheets("Graphs").Select ActiveSheet.Shapes.SelectAll Selection.Copy Range("A1").Select 'Pastes graph picture to summary sheet Sheets("Static Summary Sheet").Select Range("D5").Select ActiveSheet.PasteSpecial Format:="Picture (Enhanced Metafile)", Link:=False _ , DisplayAsIcon:=False Selection.Top = 69.75 Selection.Left = 37.5 Now I get an error in the pastespecial line "PasteSpecial method of Worksheet class failed. Any ideas? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel graphs change when pasted into Publisher - Metafiles change? | Charts and Charting in Excel | |||
| SUMPRODUCT - enhanced formula | Excel Discussion (Misc queries) | |||
| Enhanced lottery question | Excel Discussion (Misc queries) | |||
| Enhanced Inputbox | Excel Programming | |||
| enhanced conditional formatting | Excel Discussion (Misc queries) | |||
![Paste charts as enchanced metafile[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
