Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Charts still linked to old book.
Excel 97. Macro copies data and a dozen charts to a new workbook.
Charts still linked to old book. Can I change book and sheet to the new sheet automatically? Search and replace? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Charts still linked to old book.
Normal Search & replace will let you replace the workbook references in
formulae, but not in the chart series. You need to do it in the macro code - but I'm assuming that's not a problem for you since you've posted in an Excel Programming newsgroup and you've said you've got a macro already. I'm using Excel 2003. VBA probably hasn't changed a lot since 97, but there's a chance my code won't work. This is a complete sub - I suggest you grab the bits you want and add to your existing code. You also need to change the filenames (just under the dim statements) to match yours. If you already have variables for these in your code, then replace my oldBook and newBook variables with the variables you've already got. I'm not sure, but I think if there are formulae in the chart titles then they do not get "fixed", but it should work on the chart series. ------------------------------------------- Sub RemoveLinks() ' ' RemoveLinks Macro ' Cringing Dragon 21/10/2007 ' Dim oSheet As Worksheet Dim oChart As Object Dim oSeries As Series Dim newBook As String Dim oldBook As String ' Replace these with YOUR workbook names newBook = ActiveWorkbook.Name oldBook = "Book2" ' Turn error checking off so it doesn't stop if any of the values can't be changed On Error Resume Next ' This loop checks all worksheets For Each oSheet In Workbooks(newBook).Worksheets ' Remove links in cell formulae oSheet.Cells.Replace What:="[" & oldBook & "]", Replacement:="", _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False ' This loop checks all embedded charts on the sheet For Each oChart In oSheet.ChartObjects ' This loop checks all series in the chart For Each oSeries In oChart.Chart.SeriesCollection oSeries.Formula = Replace(oSeries.Formula, "[" & oldBook & "]", "") Next oSeries Next oChart Next oSheet ' This loop checks all chart sheets For Each oChart In Workbooks(newBook).Charts ' This loop checks all series in the chart For Each oSeries In oChart.SeriesCollection oSeries.Formula = Replace(oSeries.Formula, "[" & oldBook & "]", "") Next oSeries Next oChart ' Turn normal error handling back on On Error GoTo 0 End Sub ------------------------------------------- "Fan924" wrote: Excel 97. Macro copies data and a dozen charts to a new workbook. Charts still linked to old book. Can I change book and sheet to the new sheet automatically? Search and replace? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Charts still linked to old book.
Thanks. 97 does not like the command "Replace". Sub or function not
defined. Is ther an excel 97 equivalent? anyone? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Charts still linked to old book.
fan924,
Try Application.Worksheetfunction.Substitute 'use the parameters as they work in the worksheet Substitute function hth, Doug "Fan924" wrote in message ups.com... Thanks. 97 does not like the command "Replace". Sub or function not defined. Is ther an excel 97 equivalent? anyone? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Charts still linked to old book.
If it's only the Replace function that gives errors - ie the lines like:
oSeries.Formula = Replace(oSeries.Formula, "[" & oldBook & "]", "") then try using WorksheetFunction.Replace instead: oSeries.Formula = WorksheetFunction.Replace(oSeries.Formula, "[" & oldBook & "]", "") But don't make that change on the line that uses the Replace Method: oSheet.Cells.Replace.... "Fan924" wrote: Thanks. 97 does not like the command "Replace". Sub or function not defined. Is ther an excel 97 equivalent? anyone? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Charts still linked to old book.
Sorry, Doug beat me to it and his solution's better. I think the syntax for
worksheetfunction.replace is slightly different, so mine wouldn't work anyway. "Cringing Dragon" wrote: If it's only the Replace function that gives errors - ie the lines like: oSeries.Formula = Replace(oSeries.Formula, "[" & oldBook & "]", "") then try using WorksheetFunction.Replace instead: oSeries.Formula = WorksheetFunction.Replace(oSeries.Formula, "[" & oldBook & "]", "") But don't make that change on the line that uses the Replace Method: oSheet.Cells.Replace.... "Fan924" wrote: Thanks. 97 does not like the command "Replace". Sub or function not defined. Is ther an excel 97 equivalent? anyone? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Charts still linked to old book.
A better way to change your links would be:
ActiveWorkbook.ChangeLink _ Name:="OldBookName.xls", _ NewName:="NewBookName.xls", _ Type:=xlExcelLinks as an example. If you want to use application.Substitute in that code to actually come up with the name of the link, that is fine as well. However, I suspect the best solution would be something like this bk1.Worksheets(Array("Data1","Chart1","Chart2")).c opy After:=workbooks("Mybook.xls").Worksheets(1) by copying all the sheets as a group, the won't create links referring back to the old workbook (as long as all cells referenced are in the group of sheets copied. -- Regards, Tom Ogilvy "Fan924" wrote: Thanks. 97 does not like the command "Replace". Sub or function not defined. Is ther an excel 97 equivalent? anyone? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Charts still linked to old book.
Just to note both suggested methods will require data is copied to identical
SheetName!Cell-Ref locations in the new workbook. Also there are other potential links in charts besides series formulas. Regards, Peter T "Fan924" wrote in message ups.com... Excel 97. Macro copies data and a dozen charts to a new workbook. Charts still linked to old book. Can I change book and sheet to the new sheet automatically? Search and replace? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Linked excel charts to Word doc | Excel Discussion (Misc queries) | |||
Printing linked charts | Charts and Charting in Excel | |||
How do I extract a filename from a formula linked to another book | Excel Discussion (Misc queries) | |||
set up an index linked to work book tabs | Excel Worksheet Functions | |||
Excel Charts Linked to Spreadsheets | Charts and Charting in Excel |