Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ChartObjects
I am writing a VB routine to copy a chart from 20 workbooks to a new single
worksheet in a new workbook. I will also update the charts in the new workbook perodically. The following will be called 20 times with a different Oneof20 and ChartLoc. Sub CopyGraph (Oneof20 As String, ChartLoc As String) ' ' Open workbook Workbooks.Open Filename:= Oneof20 Windows(EachFile).Activate Sheets("XYZ").Select ' 'Copy new chart ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.ChartArea.Select ActiveChart.ChartArea.Copy ActiveWindow.Visible = False ' ' paste new chart Windows("ALL.xls").Activate Sheets("20Charts").Select Range(ChartLoc).Select ActiveSheet.Paste ' ' change chart title ActiveSheet.ChartObjects(ChartName).Activate .. .. .. How can I determine what ChartName is? If I do the "copy the 20 charts" a 2nd time (say a month later), I will have to delete the original 20 charts first. How do I get the names of those 20 charts so I can reference them to delete? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ChartObjects
Scott,
Your procedure is attempting to activate a chart that will already be active. When you paste the chart it becomes the active item. So if you want to get the name of the chart you just need to use ActiveChart.Name. You could use a blank sheet to list the chart names Sheet1.Range("A65536").End(xlUp).Offset(1, 0) = ActiveChart.Name and then reference this list when you want to delete the charts Sheets("20charts").Activate For Each chartname In ActiveSheet.ChartObjects Set rng = Sheet1.Range("A2", Range("A65536").End(xlUp)) MsgBox (rng.Address & " " & chartname.Name) Set found = rng.Find(chartname.Name) If Not found Is Nothing Then chartname.Delete Next chartname Where Sheet1 is the sheet you write the chartnames to. Hope this helps you out. Mike "Scott" wrote: I am writing a VB routine to copy a chart from 20 workbooks to a new single worksheet in a new workbook. I will also update the charts in the new workbook perodically. The following will be called 20 times with a different Oneof20 and ChartLoc. Sub CopyGraph (Oneof20 As String, ChartLoc As String) ' ' Open workbook Workbooks.Open Filename:= Oneof20 Windows(EachFile).Activate Sheets("XYZ").Select ' 'Copy new chart ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.ChartArea.Select ActiveChart.ChartArea.Copy ActiveWindow.Visible = False ' ' paste new chart Windows("ALL.xls").Activate Sheets("20Charts").Select Range(ChartLoc).Select ActiveSheet.Paste ' ' change chart title ActiveSheet.ChartObjects(ChartName).Activate . . . How can I determine what ChartName is? If I do the "copy the 20 charts" a 2nd time (say a month later), I will have to delete the original 20 charts first. How do I get the names of those 20 charts so I can reference them to delete? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problems with ChartObjects | Excel Programming | |||
Looping Through Charts / ChartObjects | Excel Programming | |||
delete chartobjects! | Excel Programming | |||
Not allow selection of chartobjects ? | Charts and Charting in Excel | |||
ChartObjects | Excel Programming |