Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting,microsoft.public.excel.programming
|
|||
|
|||
Copy chart to clipboard - VBA
Hi again,
I'm trying to use VBA to: - copy a chart in a chart sheet - paste it as an embedded chart in a new sheet - resize (and format) the chart - copy the embedded chart to the clipboard - delete the new sheet This is the code I've come up with so far: Sub CopyChart() ActiveChart.ChartArea.Select ActiveChart.ChartArea.Copy ActiveWorkbook.Sheets.Add ActiveSheet.Paste ActiveSheet.ChartObjects("Chart 1").Activate With ActiveChart.Parent .Height = 252.75 .Width = 342.75 ActiveChart.PlotArea.Height = 205 ActiveChart.PlotArea.Width = 340 ActiveChart.ChartArea.Copy Application.DisplayAlerts = False ActiveSheet.Delete Application.DisplayAlerts = True End With End Sub The problem is: when the ActiveSheet is deleted, the copied chart is no longer available on the clipboard. I can use the CopyPicture method in stead, but then there will be a different size when pasted in another application. -- Fredrik E. Nilsen |
#2
Posted to microsoft.public.excel.charting,microsoft.public.excel.programming
|
|||
|
|||
Copy chart to clipboard - VBA
Hi Fredrik E. Nilsen;
You need to put the chart into a different workbook. It's blanking out because Excel automatically links the data. When you put it in a seperate workbook if I undersand you correctly it will solve your problem Thanks, -- DownThePaint Where points are scored and games are won! "Fredrik E. Nilsen" wrote: Hi again, I'm trying to use VBA to: - copy a chart in a chart sheet - paste it as an embedded chart in a new sheet - resize (and format) the chart - copy the embedded chart to the clipboard - delete the new sheet This is the code I've come up with so far: Sub CopyChart() ActiveChart.ChartArea.Select ActiveChart.ChartArea.Copy ActiveWorkbook.Sheets.Add ActiveSheet.Paste ActiveSheet.ChartObjects("Chart 1").Activate With ActiveChart.Parent .Height = 252.75 .Width = 342.75 ActiveChart.PlotArea.Height = 205 ActiveChart.PlotArea.Width = 340 ActiveChart.ChartArea.Copy Application.DisplayAlerts = False ActiveSheet.Delete Application.DisplayAlerts = True End With End Sub The problem is: when the ActiveSheet is deleted, the copied chart is no longer available on the clipboard. I can use the CopyPicture method in stead, but then there will be a different size when pasted in another application. -- Fredrik E. Nilsen |
#3
Posted to microsoft.public.excel.charting,microsoft.public.excel.programming
|
|||
|
|||
Copy chart to clipboard - VBA
Hi Fredrick,
can use the CopyPicture method in stead, but then there will be a different size when pasted in another application. I just tried that and it worked fine for me after deleting the chart then pasting into an image processor, having first running this recorded macro - ActiveChart.CopyPicture Appearance:=xlScreen, _ Size:=xlScreen, Format:=xlBitmap If the other application is Word it may well resize. Perhaps try different permutations of options when you do Shift Edit CopyPicture... You might also look into the Export method Regards, Peter "Fredrik E. Nilsen" wrote in message ... Hi again, I'm trying to use VBA to: - copy a chart in a chart sheet - paste it as an embedded chart in a new sheet - resize (and format) the chart - copy the embedded chart to the clipboard - delete the new sheet This is the code I've come up with so far: Sub CopyChart() ActiveChart.ChartArea.Select ActiveChart.ChartArea.Copy ActiveWorkbook.Sheets.Add ActiveSheet.Paste ActiveSheet.ChartObjects("Chart 1").Activate With ActiveChart.Parent .Height = 252.75 .Width = 342.75 ActiveChart.PlotArea.Height = 205 ActiveChart.PlotArea.Width = 340 ActiveChart.ChartArea.Copy Application.DisplayAlerts = False ActiveSheet.Delete Application.DisplayAlerts = True End With End Sub The problem is: when the ActiveSheet is deleted, the copied chart is no longer available on the clipboard. I can use the CopyPicture method in stead, but then there will be a different size when pasted in another application. -- Fredrik E. Nilsen |
#4
Posted to microsoft.public.excel.charting,microsoft.public.excel.programming
|
|||
|
|||
Copy chart to clipboard - VBA
On Sat, 21 Apr 2007 14:04:15 +0100, "Peter T" <peter_t@discussions
wrote: Hi Fredrick, can use the CopyPicture method in stead, but then there will be a different size when pasted in another application. I just tried that and it worked fine for me after deleting the chart then pasting into an image processor, having first running this recorded macro - ActiveChart.CopyPicture Appearance:=xlScreen, _ Size:=xlScreen, Format:=xlBitmap If the other application is Word it may well resize. Perhaps try different permutations of options when you do Shift Edit CopyPicture... You might also look into the Export method Thanks for your reply Peter. Your macro would work as you describe, but with this, the chart will not have the size and formatting the users want. When pasted in Word, all charts should ideally have the same size and the same formatting. With CopyPicture, it takes the size of the screen (xlScreen). Thats why I have to copy the chart sheet, paste it as an embedded chart into a new sheet, resize and format it, copy the new chart to the clipboard, delete the new sheet with the new chart. Now, I want to be able to paste the chart into Word or PowerPoint, but when the new sheet is deleted, so is the chart on the clipboard. -- Fredrik E. Nilsen |
#5
Posted to microsoft.public.excel.charting,microsoft.public.excel.programming
|
|||
|
|||
Copy chart to clipboard - VBA
Size and format the chart appropriately before copying it, so you don't have
to do any formatting in the target application. Pick a size for the chart (I'll use 2.5" by 3" in my description that follows). With the chart on its own sheet (a chart sheet), change the margins so the chart fits the size you designate. The paper is 8.5" tall by 11" wide, so you need two margins 3" wide at the top and bottom, and two margins 4" wide at the left and right. Now when copied and pasted the chart will be the correct size. When you copy a chart as a chart, the chart itself is not copied. Instead, Office merely keeps track of the reference of the chart. Office then includes either the reference (if linked), or it actually copies the chart, when you paste. So if you delete the chart, the reference is also deleted. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Fredrik E. Nilsen" wrote in message ... On Sat, 21 Apr 2007 14:04:15 +0100, "Peter T" <peter_t@discussions wrote: Hi Fredrick, can use the CopyPicture method in stead, but then there will be a different size when pasted in another application. I just tried that and it worked fine for me after deleting the chart then pasting into an image processor, having first running this recorded macro - ActiveChart.CopyPicture Appearance:=xlScreen, _ Size:=xlScreen, Format:=xlBitmap If the other application is Word it may well resize. Perhaps try different permutations of options when you do Shift Edit CopyPicture... You might also look into the Export method Thanks for your reply Peter. Your macro would work as you describe, but with this, the chart will not have the size and formatting the users want. When pasted in Word, all charts should ideally have the same size and the same formatting. With CopyPicture, it takes the size of the screen (xlScreen). Thats why I have to copy the chart sheet, paste it as an embedded chart into a new sheet, resize and format it, copy the new chart to the clipboard, delete the new sheet with the new chart. Now, I want to be able to paste the chart into Word or PowerPoint, but when the new sheet is deleted, so is the chart on the clipboard. -- Fredrik E. Nilsen |
#6
Posted to microsoft.public.excel.charting,microsoft.public.excel.programming
|
|||
|
|||
Copy chart to clipboard - VBA
On Sat, 21 Apr 2007 20:10:14 -0400, "Jon Peltier"
wrote: Size and format the chart appropriately before copying it, so you don't have to do any formatting in the target application. Pick a size for the chart (I'll use 2.5" by 3" in my description that follows). With the chart on its own sheet (a chart sheet), change the margins so the chart fits the size you designate. The paper is 8.5" tall by 11" wide, so you need two margins 3" wide at the top and bottom, and two margins 4" wide at the left and right. Now when copied and pasted the chart will be the correct size. When you copy a chart as a chart, the chart itself is not copied. Instead, Office merely keeps track of the reference of the chart. Office then includes either the reference (if linked), or it actually copies the chart, when you paste. So if you delete the chart, the reference is also deleted. Thanks Jon, I hadn't thought of that approach. Two problems come to mind: This will only work if the paper size always is the same. I can, of course, change the paper size to for example A4 but then I have to revert it to the original size after the chart is copied. Is there a way in VBA to reset the margins after the chart is copied? -- Fredrik E. Nilsen |
#7
Posted to microsoft.public.excel.charting,microsoft.public.excel.programming
|
|||
|
|||
Copy chart to clipboard - VBA
"Fredrik E. Nilsen" wrote in message ... On Sat, 21 Apr 2007 20:10:14 -0400, "Jon Peltier" wrote: Size and format the chart appropriately before copying it, so you don't have to do any formatting in the target application. Pick a size for the chart (I'll use 2.5" by 3" in my description that follows). With the chart on its own sheet (a chart sheet), change the margins so the chart fits the size you designate. The paper is 8.5" tall by 11" wide, so you need two margins 3" wide at the top and bottom, and two margins 4" wide at the left and right. Now when copied and pasted the chart will be the correct size. When you copy a chart as a chart, the chart itself is not copied. Instead, Office merely keeps track of the reference of the chart. Office then includes either the reference (if linked), or it actually copies the chart, when you paste. So if you delete the chart, the reference is also deleted. Thanks Jon, I hadn't thought of that approach. Two problems come to mind: This will only work if the paper size always is the same. I can, of course, change the paper size to for example A4 but then I have to revert it to the original size after the chart is copied. Is there a way in VBA to reset the margins after the chart is copied? -- Fredrik E. Nilsen |
#8
Posted to microsoft.public.excel.charting,microsoft.public.excel.programming
|
|||
|
|||
Copy chart to clipboard - VBA
Is there a way in VBA to reset the margins after the chart is copied?
If the whole issue is you want to delete the chart, after copying to the clipboard but before pasting to Word, why do you need to be concerned with resetting the margins. Regards, Peter T "Fredrik E. Nilsen" wrote in message ... On Sat, 21 Apr 2007 20:10:14 -0400, "Jon Peltier" wrote: Size and format the chart appropriately before copying it, so you don't have to do any formatting in the target application. Pick a size for the chart (I'll use 2.5" by 3" in my description that follows). With the chart on its own sheet (a chart sheet), change the margins so the chart fits the size you designate. The paper is 8.5" tall by 11" wide, so you need two margins 3" wide at the top and bottom, and two margins 4" wide at the left and right. Now when copied and pasted the chart will be the correct size. When you copy a chart as a chart, the chart itself is not copied. Instead, Office merely keeps track of the reference of the chart. Office then includes either the reference (if linked), or it actually copies the chart, when you paste. So if you delete the chart, the reference is also deleted. Thanks Jon, I hadn't thought of that approach. Two problems come to mind: This will only work if the paper size always is the same. I can, of course, change the paper size to for example A4 but then I have to revert it to the original size after the chart is copied. Is there a way in VBA to reset the margins after the chart is copied? -- Fredrik E. Nilsen |
#9
Posted to microsoft.public.excel.charting,microsoft.public.excel.programming
|
|||
|
|||
Copy chart to clipboard - VBA
"Fredrik E. Nilsen" wrote in message
... On Sat, 21 Apr 2007 14:04:15 +0100, "Peter T" <peter_t@discussions wrote: Hi Fredrick, can use the CopyPicture method in stead, but then there will be a different size when pasted in another application. I just tried that and it worked fine for me after deleting the chart then pasting into an image processor, having first running this recorded macro - ActiveChart.CopyPicture Appearance:=xlScreen, _ Size:=xlScreen, Format:=xlBitmap If the other application is Word it may well resize. Perhaps try different permutations of options when you do Shift Edit CopyPicture... You might also look into the Export method Thanks for your reply Peter. Your macro would work as you describe, but with this, the chart will not have the size and formatting the users want. When pasted in Word, all charts should ideally have the same size and the same formatting. With CopyPicture, it takes the size of the screen (xlScreen). Thats why I have to copy the chart sheet, paste it as an embedded chart into a new sheet, resize and format it, copy the new chart to the clipboard, delete the new sheet with the new chart. Now, I want to be able to paste the chart into Word or PowerPoint, but when the new sheet is deleted, so is the chart on the clipboard. -- Fredrik E. Nilsen The clipboard stores a wide variety of information as 'formats'. If you delete the chart while it's in the clipboard all the OLE info is lost. Try copying a cell with data that has a format, eg a colour. Paste to a cell in another wb works as expected. Delete the original source and paste again and only the value as text will remain in the clipboard, even if the source was a formula. I quickly tried the copypicture a method again and found sometimes it would paste into Word depending on the copypicture options, even after deleting the chart (chartobject). For other Office app's if pasting an image, it's probably better as Windows Meta file. Perhaps it might be worth looking at Stephen Bullen's PastePicture.zip http://www.oaltd.co.uk/Excel/Default.htm Use the API to copy to the clipboard in desired format. Regards, Peter T |
#10
Posted to microsoft.public.excel.charting,microsoft.public.excel.programming
|
|||
|
|||
Copy chart to clipboard - VBA
People worry about having multiple copies of information, and they want a
simple way to switch formats around. It is far easier to have two copies of the information (e.g., of a chart), and format each copy for its specific role. If both are linked to the same source data, they will accurately show the proper data. If your target paper size changes, you need a differently formatted chart. Why not have a different chart for each size paper (or for each size graphic on any piece of paper), and let your copy routine decide which one to use? Simpler doesn't necessarily mean smaller or using fewer sheets or whatever. Simpler means saving yourself time and energy. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Fredrik E. Nilsen" wrote in message ... On Sat, 21 Apr 2007 20:10:14 -0400, "Jon Peltier" wrote: Size and format the chart appropriately before copying it, so you don't have to do any formatting in the target application. Pick a size for the chart (I'll use 2.5" by 3" in my description that follows). With the chart on its own sheet (a chart sheet), change the margins so the chart fits the size you designate. The paper is 8.5" tall by 11" wide, so you need two margins 3" wide at the top and bottom, and two margins 4" wide at the left and right. Now when copied and pasted the chart will be the correct size. When you copy a chart as a chart, the chart itself is not copied. Instead, Office merely keeps track of the reference of the chart. Office then includes either the reference (if linked), or it actually copies the chart, when you paste. So if you delete the chart, the reference is also deleted. Thanks Jon, I hadn't thought of that approach. Two problems come to mind: This will only work if the paper size always is the same. I can, of course, change the paper size to for example A4 but then I have to revert it to the original size after the chart is copied. Is there a way in VBA to reset the margins after the chart is copied? -- Fredrik E. Nilsen |
#11
Posted to microsoft.public.excel.charting,microsoft.public.excel.programming
|
|||
|
|||
Copy chart to clipboard - VBA
On Sun, 22 Apr 2007 10:57:27 -0400, "Jon Peltier"
wrote: People worry about having multiple copies of information, and they want a simple way to switch formats around. It is far easier to have two copies of the information (e.g., of a chart), and format each copy for its specific role. If both are linked to the same source data, they will accurately show the proper data. If your target paper size changes, you need a differently formatted chart. Why not have a different chart for each size paper (or for each size graphic on any piece of paper), and let your copy routine decide which one to use? Simpler doesn't necessarily mean smaller or using fewer sheets or whatever. Simpler means saving yourself time and energy. Yes, I agree with you on all your points, and I hope to educate the users on these issues at a later point. But: at this moment the users are not under my control so I have to stick with the approach they think they want. :) -- mvh Fredrik E. Nilsen |
#12
Posted to microsoft.public.excel.charting,microsoft.public.excel.programming
|
|||
|
|||
Copy chart to clipboard - VBA
Stupid users :)
- Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Fredrik E. Nilsen" wrote in message ... On Sun, 22 Apr 2007 10:57:27 -0400, "Jon Peltier" wrote: People worry about having multiple copies of information, and they want a simple way to switch formats around. It is far easier to have two copies of the information (e.g., of a chart), and format each copy for its specific role. If both are linked to the same source data, they will accurately show the proper data. If your target paper size changes, you need a differently formatted chart. Why not have a different chart for each size paper (or for each size graphic on any piece of paper), and let your copy routine decide which one to use? Simpler doesn't necessarily mean smaller or using fewer sheets or whatever. Simpler means saving yourself time and energy. Yes, I agree with you on all your points, and I hope to educate the users on these issues at a later point. But: at this moment the users are not under my control so I have to stick with the approach they think they want. :) -- mvh Fredrik E. Nilsen |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy chart to clipboard - VBA | Charts and Charting in Excel | |||
Transfer clipboard from Task pane clipboard(office?) content to Excel (windows?) clipboard? | Excel Programming | |||
Copy from Word on clipboard | Excel Programming | |||
Copy from clipboard? | Excel Programming | |||
Can't copy ActiveSheet to clipboard | Excel Programming |