Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying Charts: CODE NOT ROBUST
Can anyone help.
The code I'm using is: Dim sheet Dim Chart 'loop through all the embedde charts in a particular sheet For Each Chart In sheet.ChartObjects 'activate the chart and move it to it's own sheet Chart.Activate ActiveChart.Location Whe=xlLocationAsNewSheet, Name:=mySheetName 'now copy the chart worksheet to a different workbook ActiveSheet.Copy Workbooks(myFileName).Sheets(Workbooks(myFileName) .Sheets.Count) 'in thisworkbook move the chart back to being an embedded chart object ThisWorkbook.Activate ActiveChart.Location Whe=xlLocationAsObject, Name:=sheet.Name Next Chart I've used the above procedure because I just want the charts to be embedded objects in the main spreadsheet rather than having a load of Chartsheets aswell as worksheets. Problem with the above is that it seems to throw an unknown error, and then when I go into the code window and go for Debug and then step through the macro all seems ok: but when I come to exit the spreadsheet and save changes a nasty Dr Watson occurs - and all Excel is lost. So I believe the above is doing something particularly nasty to Excel. Any help greatly appreciated Jason. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying Charts: CODE NOT ROBUST
Here is a possible workaround:
dim sht as Worksheet Dim cobj as ChartObject Dim cobj1 as ChartObject set sht = Activesheet For Each cObj In sheet.ChartObjects sht.copy ' makes a new workbook with all chart Objects ' now delete all but the one you want for each cobj1 in activesheet.ChartObjects if cobj1.name < cObj.Name then cobj1.Delete end if next ActiveWorkbook.SaveAs "C:\Myfolder\" & cobj.Name & ".xls" ActiveWorkbook.Close Savechanges:=False Next -- Regards, Tom Ogilvy "WhytheQ" wrote: Can anyone help. The code I'm using is: Dim sheet Dim Chart 'loop through all the embedde charts in a particular sheet For Each Chart In sheet.ChartObjects 'activate the chart and move it to it's own sheet Chart.Activate ActiveChart.Location Whe=xlLocationAsNewSheet, Name:=mySheetName 'now copy the chart worksheet to a different workbook ActiveSheet.Copy Workbooks(myFileName).Sheets(Workbooks(myFileName) .Sheets.Count) 'in thisworkbook move the chart back to being an embedded chart object ThisWorkbook.Activate ActiveChart.Location Whe=xlLocationAsObject, Name:=sheet.Name Next Chart I've used the above procedure because I just want the charts to be embedded objects in the main spreadsheet rather than having a load of Chartsheets aswell as worksheets. Problem with the above is that it seems to throw an unknown error, and then when I go into the code window and go for Debug and then step through the macro all seems ok: but when I come to exit the spreadsheet and save changes a nasty Dr Watson occurs - and all Excel is lost. So I believe the above is doing something particularly nasty to Excel. Any help greatly appreciated Jason. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying Charts: CODE NOT ROBUST
cheers Tom
Jason Tom Ogilvy wrote: Here is a possible workaround: dim sht as Worksheet Dim cobj as ChartObject Dim cobj1 as ChartObject set sht = Activesheet For Each cObj In sheet.ChartObjects sht.copy ' makes a new workbook with all chart Objects ' now delete all but the one you want for each cobj1 in activesheet.ChartObjects if cobj1.name < cObj.Name then cobj1.Delete end if next ActiveWorkbook.SaveAs "C:\Myfolder\" & cobj.Name & ".xls" ActiveWorkbook.Close Savechanges:=False Next -- Regards, Tom Ogilvy "WhytheQ" wrote: Can anyone help. The code I'm using is: Dim sheet Dim Chart 'loop through all the embedde charts in a particular sheet For Each Chart In sheet.ChartObjects 'activate the chart and move it to it's own sheet Chart.Activate ActiveChart.Location Whe=xlLocationAsNewSheet, Name:=mySheetName 'now copy the chart worksheet to a different workbook ActiveSheet.Copy Workbooks(myFileName).Sheets(Workbooks(myFileName) .Sheets.Count) 'in thisworkbook move the chart back to being an embedded chart object ThisWorkbook.Activate ActiveChart.Location Whe=xlLocationAsObject, Name:=sheet.Name Next Chart I've used the above procedure because I just want the charts to be embedded objects in the main spreadsheet rather than having a load of Chartsheets aswell as worksheets. Problem with the above is that it seems to throw an unknown error, and then when I go into the code window and go for Debug and then step through the macro all seems ok: but when I come to exit the spreadsheet and save changes a nasty Dr Watson occurs - and all Excel is lost. So I believe the above is doing something particularly nasty to Excel. Any help greatly appreciated Jason. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying Charts | Charts and Charting in Excel | |||
copying vba code to a standard code module | Excel Discussion (Misc queries) | |||
Are there functions that perform robust statistics in Excel? | Excel Worksheet Functions | |||
Can someone help me make this code more robust? | Excel Programming | |||
Sheet Code Module: copying code to | Excel Programming |