Solved

excel vba copying chart objects to a new workbook

Posted on 2011-03-09
6
927 Views
Last Modified: 2012-08-13
hi

I have a source workbook , call it WorkbookA, with multiple sheets that have chartobjects. I am copying over all the sheets to a WorkbookB. However, on doing so, I find that the charts in the copied over chartobjects in WorkbookB refer to data set in  WorkbookA.

How can I change this so that the charts in WorkbookB refer to data sets in WorkbookB?

I tried to change the Formula so that it refers to WorkBookB, but on doing so it gives a Run-Time error '1004' : Application-defined or object-defined error.

After copying over all the sheets from WorkbookA.xlsm to WorkbookB.xls, do the following


Set NewBook = Application.Workbooks.Open("WorkbookB.xls")
For Each sSheet In NewBook.Sheets
        With sSheet
                For Each chtTemp In .ChartObjects
                    For Each objSeries In chtTemp.Chart.SeriesCollection
                        objSeries.Formula = Replace(objSeries.Formula, "WorkbookA.xlsm", "WorkbookB.xls")
                    Next
                Next
        End With
    Next sSheet
   
    NewBook.Save
    NewBook.Close
0
Comment
Question by:LuckyLucks
  • 3
  • 2
6 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35086312
Here is a trick to it without writing too much code.

Rename the sheets which has a chart to say "Lucky1","Lucky2" etc...

Then in the code,

1) Add a new workbook.
2) Move (don't copy) the chart sheet and the source datasheet to the new workbook for example

Sheets("Lucky1").Move After:=Workbooks("NewWorkbook").Sheets(3)

3) Similarly move all sheets from the 1st one to the new book. Ensure that you handle errors. And then close the 1st workbook without saving so the saved version still contains the moved, i.e., removed, sheets

This will ensure that the new workbook's chart refer to the actual sheets.

Let me know if you get stuck.

Sid
0
 

Author Comment

by:LuckyLucks
ID: 35086734
moving is not acceptable. I need to keep the original for a reason. also note that the original WorkBookA is a macro-enabled file while the second WorkbookB is not a macro-enabled file.

Do you know what the error means? I don't need a alternate solution that is totally operating on a different workflow.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35086904
>>>moving is not acceptable. I need to keep the original for a reason.

Your original file will remain untouched and that is why at the end of the code we will not save the original. If you upload your files, then maybe I can give you a sample code?

Sid
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 6

Expert Comment

by:Faisal Khan
ID: 35087344
Try to copy your data sheets first and then chart object sheets. doing so, copied chart would not refer to previous workbook
0
 

Accepted Solution

by:
LuckyLucks earned 0 total points
ID: 35088911
found a similar question by brettdj that was great:

brettdj:

If you are copying the data range as well then you can just relink the destination workbook to itself (from the source workbook) using ChangeLink in VBA

This gets the Chart pointing to the data in the destination folder

Cheers

Dave
0
 

Author Closing Comment

by:LuckyLucks
ID: 35126442
original answer provided by brettdj
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now