VB Copy embedded Graphs

[PhatPhil]

I am trying to create a generic tool to analyse any Excel file so that the user can select what worksheets need to be copied to a new excel file. The tool is designed to remove all macro and formulas. By Copy and PasteSpecial Values and Formats I am close to achieving my goal. The issue I have is embedded graphs in worksheets are not copied.

How do I copy Embedded Grpahs with VB.

[Andy Pope]

First copy the range including those cells covered by the chart.
Paste to the new sheet.
The re-copy and pastespecial the values.

The chartobject will still reference the original cells so you either need to update the chart data references or replace them with static array values.

If you create a copy of the worksheet the chart references will update automatically. You could then copy/pastespecial the cell contents

[PhatPhil]

Andy thanks for responding.

There is where I am getting confused, I am copy the whole Worksheet which I would presume would include the range associated with the graph. As I am designing a generic tool the graph could be any where on the worksheet.

[Andy Pope]

are you copying the sheet or all the cells?

It maybe helpful if you posted a workbook example and code.

You can also check the setting for "copy, cut, sort objects with cells." via Tools > Options > Edit.

Please Login or Register  to view this content.

[PhatPhil]

I missed the trick with your post. I was using Paste Special To Paste the Values and then the Formats which gave me the worksheet but excluding the graph. I am now using 'Activesheet Paste' which is now transfering all the data and then Pastespecial Values which is removing all the formulas.

The issue I have now is although the graph is present, the source data is linked to donating excel file not the new one I created which you mentioned would be a problem in your first post.

You mentioned copying the worksheet, I am using the code 'cells select' and 'selection copy' to copy the original is there some better VB code which does the copying.

[Andy Pope]

This will create a copy worksheet at the end of the workbook.

Please Login or Register  to view this content.

[PhatPhil]

Andy thanks for your help, I have tried to incorporate your suggestions but have now broke it completely. I would be grateful if you could analyse the VB code (Process.CreateNewFile) to see where I am going wrong.


DumbDown.xls

[Andy Pope]

You need to explain how I should use it and where it breaks.

I will check back tomorrow as I'm going offline now.

[PhatPhil]

The CreateFile VB Code - Creates a new file but pastes text into the sheet not a copy of the sheet from the original workbook and the PasteSpecial fails.

[PhatPhil]

I have mangaed to fix the VB code, the file is attached for any one who is interested in fllowing this thread.

DumbDown.xls

Andy you mentioned in an earlier thread that the graph's source data would refer to the original excel file, I presume I will have to manually change the source. You mentioned static arrays where and how do you use them.

[Andy Pope]

The clarification on use was not much help. When I run the code it displays the message "No Sheets Identified."

See here who to replace range references with static values.
http://peltiertech.com/Excel/ChartsH...ChartData.html

[PhatPhil]

Andy

You need to select at least one sheet once the list of sheets appears otherwise it thinks there is nothing to do. The count in Cell E5 needs to be greater than zero.

It may take me a while to understand the static array but thanks for the link.

The next question is :- the routine is also bring across macros which reside on the sheet, is there a way to remove these.

[Andy Pope]

see here for code to manipulate code
http://www.cpearson.com/excel/vbe.aspx

[PhatPhil]

Andy Thanks for your ongoing support.

I have added the 'DeleteAllVBACode()' from the link you gave me, this didn't work at first becuase the VB for Applications Extensibility wasn't referenced.

The code now runs but doesn't delete the macros in the file I am creating..

I have added the excel file for reference, the code DeleteAllVBACode is called up from VB Code CreateNewFile

DumbDown.xls

The code is quantum leap forward in my VB code programming.

Phil

[Andy Pope]

Works for me on a test file.

[PhatPhil]

Andy,

When I am at home using Excel 2010 (processing an Excel 97-2003 file)

I get the message

Error 1004 Programmatic Access To Visual Basic Project is not trusted.

When I am at work using Excel 2000 (this is where this file needs to be employed) the code runs but leaves any macros embedded in a worksheet . Is this a limitation of Excel 2000

Thanks
Phil

[PhatPhil]

Andy,

I am using Excel 2010 at home and when processing a file (even if an Excel 97-2003) after a change in settings in the macro trust centre, it works fine.

At work we are still using Excel 2000 and there is where I need the program to work. The program works but does not remove embedded macros attached to the worksheet.

Thanks Phil

[PhatPhil]

Andy,

At work we automatically call up an extra excel file which carries macros to deliver inhouse
functions. These macros I was seeing as attached to the file I was processing.

Now I have realised my mistake you are correct the program does actually work correctly.

Thanks for your support - much appreciated.