Chart order selection in active worksheet issue when exporting all charts to PPT

[colinduncan]

Hi there, I am having an issue with the loop order through my active worksheet when exporting several charts to powerpoint. The code does select and export all of the charts to slides as is expected, however I shifted one of the charts further down my sheet, yet when the code runs, it still picks up this chart in the original order in which it was placed. Basically, it was the second chart on the sheet, and hence copied to Slide 2, and now that it is further down the sheet, is still copying to slide 2. All of the Charts are named uniquely, and it is not an alphabetical problem. This is a bit of a problem as i have a lot of formatting i need to do in terms of final number and order of slides, and I dont want to have to name specific charts to specific slides as this becomes a nightmare. As of now, this is all a result of having to perform workarounds to transfer graphics initiated in Access data, which then exports to excel for analysis, then to ppt to achieve emf format before finally been sent to word for the report.
Is there somewhere that a chart index can be edited in terms of defining order in which these charts are copied??

[AlphaFrog]

I don't know if you can change the index numbers (I don't think you can), but you can define the order you loop through the indices.

Please Login or Register  to view this content.

[colinduncan]

Hi there Alpha. Just tried this - "...For Each cht In ActiveSheet.ChartObjects(Array(1, 2, 3, 4, 5, 6, 7))..." and "...For Each cht In ActiveSheet.ChartObjects(Array(1, 3, 4, 5, 2, 6, 7))..." and "...For Each cht In ActiveSheet.ChartObjects(Array("name1", "name", etc))..." and all give the same order in slides.

[AlphaFrog]

Hi there Alpha. Just tried this - "...For Each cht In ActiveSheet.ChartObjects(Array(1, 2, 3, 4, 5, 6, 7))..." and "...For Each cht In ActiveSheet.ChartObjects(Array(1, 3, 4, 5, 2, 6, 7))..." and "...For Each cht In ActiveSheet.ChartObjects(Array("name1", "name", etc))..." and all give the same order in slides.

It changes the chart order not the slide order. How are you referencing the slides? Can you show your code?

[colinduncan]

Understood. And if I can assign the order the charts are copied, then it should copy them in this order to the slides (the endgame).
I also tried using ActiveSheet.ChartShapes(Array.... to no avail
I am calling this as a function in both test and operational modes.

Please Login or Register  to view this content.

[AlphaFrog]

It worked for me. All I did was change the this line to...
For Each cht In ActiveSheet.ChartObjects(Array(1, 3, 2))
New PP Presentation
Chart(1) was copied to Slide(1)
Chart(3) was copied to Slide(2)
Chart(2) was copied to Slide(3)

I think (guessing) what you are doing is copying the charts to a new PP Presentation but viewing an already open file PPTfilename.pptm. So nothing appears to change.

Your code is just a sub procedure. A function would return a value to the caller. Here's your same code cleaned up a bit.

Please Login or Register  to view this content.

[colinduncan]

Hi Alpha. The concept is that this code opens a macro enabled PPT with no slides, pastes the charts into relevant slide order, saves the ppt file (leaving the macro enabled PPT empty for next time) and keeping a useful ppt hopefully. The macros in the PPT are to copy the pasted images to emf file for further insertion into a word doc elsewhere (disabled for now and another much longer story and which ultimately is the reason the ppt gets used in the first place as a result of image degradation from excel to word!!!)
I tried the code you sent back, and i am still finding that the new PPT created is receiving slide order in line with the index order. ie 1,2,3,4,5 irrespective of the array instruction.

[AlphaFrog]

It works for me. Don't know what to tell you.

[colinduncan]

Thanks Alpha, I really appreciate the effort. I need to make sure I got the ducks lined up, am probably missing something here. Thanks again.

[colinduncan]

Ok, getting a run time error 2147024809(80070057) The item with the specified name wasnt found. at line -
hovering over cht I get cht = Nothing
Remove the array reference and the code works fine but loops through in index order.
Again,have tried variations including .Chart.Shapes, Chart names etc.
Any thoughts anyone?

Please Login or Register  to view this content.

[Andy Pope]

the chart order is determined by the zorder when using the chartobjects collection.

Please Login or Register  to view this content.

Any reason you base ppt file is not a template?

Please Login or Register  to view this content.

Or rather than create 2 instances of powerpoint simply open the presentation and use the SaveAsCopy, which will leave the original file as was.

[AlphaFrog]

the chart order is determined by the zorder when using the chartobjects collection.

Good to know. Thanks Andy. Any idea why looping through an array of indices Array(1, 3, 4, 5, 2, 6, 7) worked for me but not the OP?

[colinduncan]

Hi there Andy. Thanks very much for this!!! I wasn't able to use the first code to set specific order, as each run of the loop reassigns (or seems to) the index number, thus when the loop occurs there is a change in the shape index selected. However, I did use it as a single run, and simply sent the chart I wished to have at the front, to the bottom of the list, assigned the next chart from the new index just created to the bottom and so on. A little bit of work, but an excellent solution!
Also, thank you very much for the code tidy. No reason for not using a template, I had just used saveascopy as you mentioned while I am constructing the frame for all of this. Now of course i have no excuse!
Again, thank you very much guys, I appreciate the help.

Please Login or Register  to view this content.