Excel to Powerpoint chart issues

Hi all,
I have set up a template of which I run code to transfer ranges and charts over to the template each week to generate a number of slides.
This usually works perfectly ok.
However now and again it all goes wrong when it comes to the charts.
The code assigns the slide, copies the chart (tried enhanced metafile, just paste and other types), pastes it on the slide and then finds the last shape & alters its position.
However sometimes it alters the position of text box instead of the chart, sometimes it hasn't copied the chart and then sometimes after it keeps copying the same chart instead of picking up the new charts when pasting onto the following slides.
I have tried clearing the copy paste after, I have also used the API to clear the clipboard.
Sometimes it works perfectly, other times it goes wrong.
I have been using this on Excel 2010 & PowerPoint 2003
I tried it on Excel 2007 and it works ok

I have highlighted the area where it goes wrong

Please Login or Register  to view this content.

Hi Sean

- are there any charts on the slide before your VBA runs?
- which other objects are on the slide?
- are the 4 charts guaranteed to be present on the worksheet when VBA runs? (ie - there is zero chance of code doing something odd because named chart is missing)
- are there any other charts on that worksheet?
- is your problem confined to slide 15?

hi Kev
- are there any charts on the slide before your VBA runs? - No
- which other objects are on the slide? - there is only 1 text box (the title of the slide) hence why I use the shapes.count to get the last shape added - ie. the chart
- are the 4 charts guaranteed to be present on the worksheet when VBA runs? (ie - there is zero chance of code doing something odd because named chart is missing) - I have a fixed report that is updated weekly with a data set. The Charts are always present and do not change except the data they look up which is automated and always 12 months of data
- are there any other charts on that worksheet? - yes a whole load of them, but I have used their chart names to select which one to copy so shouldn't be an issue
- is your problem confined to slide 15? - No, that's where it starts to go wrong after copying a chart. there are about another 5-6 slides that also have charts from the same worksheet with the charts on.

As mentioned before sometimes it works perfectly, sometimes it doesn't. But that's what's strange, nothing changes in the main report & charts, only the data behind them change.
If I use Excel 2007 the charts work fine, but when it copies a range as an image it chops off the last column.
If I use Excel 2010 the charts don't copy properly but the ranges are perfect.
Its very odd and I have spent hours changing the code, clearing any reference to objects, changing the type of image copied and sometimes its ok then the next time I run the same code on the same report it goes out of bonk

I had to reduce the code as it was too long for the post. However there is additional code that is a duplication of whats on here, only it copies different chart names and pastes them on different slides
Sometimes it will copy the first 5 charts fine. 4 onto slide 15, then the next one on slide 16, then it copies the next chart for the following slides even though I have cleared the clipboard and copying different chart names.
I have tried pausing the code at different stages where it goes wrong, but when I do that it works fine

Thanks
- will update thread tomorrow morning

I have tried pausing the code at different stages where it goes wrong, but when I do that it works fine

- this may be a clue
- it is possible that your VBA is getting ahead of itself
- that could cause the erratic behaviour
- we can make sure that VBA finishes off one thing before it starts doing something else

The code does not change and everything else appears to be the same.
The inconsistencies need to be explained by something - and processing times could easily be responsible.
Your memory and processor are used for a raft of different things and loads vary as a result.

This line delays things by two seconds (approx)

Please Login or Register  to view this content.

I suggest
- you insert above line in the macro after every chart has been pasted and before the next one is copied
- a 2 second delay should be more than enough!
- then run the code repeately to see if the error re-appears
- if it does not, then you have code that works

see post#5
We also need to loook at ways of speeding up your code. Let's start with:

I would expect to see this at the BEGINNING of your code (it isn't there):

Please Login or Register  to view this content.

and this at the end (but it's at the beginning? why?):

Please Login or Register  to view this content.

Screen updating and calculations are usually a waste of resources in mid-VBA
- so we switch them both off at the beginning and back on again at the end
- sometimes one of them may need switching back on and off briefly I do not think that is the case here

Try amending your code as described above (removing all intermediate attempts to use those properties)

Next:
Let me know how you get on with posts #5 & #6 and we'll take it from there
- we should be able to tidy up your code quite a lot
- but (before that) I want to make sure that you have something that works consistently

screen updating - I originally had this set to false (hence the turn to true in the error code)
As you can see I have a number of lines of code that are commented out where I have been playing around.

Originally I didn't use the application.cutcopymode or the ClearClipboard and it was working (most of the time)

There occasions where I am changing a couple of selections before copying the range (see slide 4), this works fine for slides 4-14 (Apart from Excel 2007 sometimes chops off the last column)
Slide 15-18 just copies 4 charts to each slide (no selection changes)
Then I do have a couple of charts after that where a selection is changed first.
I did consider using a time delay. But opted to allow screen updating & calculation to see if that helped with the selection changes taking place before the copying. Didn't seem to make much difference.

I will give the suggested changes a go tomorrow.

Appreciate your assistance as I don't have much hair left and I'm starting to lose more of it!

Just a thought, I read somewhere that early binding helps with referencing objects better and reduces run time.
Could this be worth trying?

I have tried inserting the delay after the chart has been pasted and also in between each section of code per chart. It slows it down, so you can actually watch the slides update.
The first 4 charts loaded on 1 slide, then the next 2 for the following slide.
But for some reason why it then moved the 2nd chart to the 3rd chart position, then to the 4th chart position.
It is not copying the next chart over.
I have tried using the clear clipboard API, application.cutcopymode=false, even tried destroying the object before assigning it to the next chart.
Very strange
Also turned off screen updating & calculation. This hasn't altered whats happening. Only speeded the process up slightly

Well after a lot of playing around with trying to clear clipboards etc I have resorted to going back to the basics.
Originally my code was late binding. So I decided to go with early binding to make sure everything was working properly

Please Login or Register  to view this content.

the problem I had was clearly with the copy/paste.
Sometimes it would work and sometimes it would not. This seemed rather odd to me.
The fact that even that I presumably cleared the clipboard after each cahrt had been copied & pasted, it still managed to copy the same chart sometimes.
I tried using the delay, cutcopymode=false, clear clipboard but still it played up.

the basics:
set everything to an object
sheet name Set ChartWS = ThisWorkbook.Worksheets("CHARTS")
slide Set PPSlide = PPPres.Slides(8)
chart Set Chrt = ChartWS.ChartObjects("Chart 1")

now by setting the chart as a chart object, if it is not found then it will error.

Copy chart - this seems to work fine now as it is copying the object
Chrt.CopyPicture Appearance:=xlScreen, Format:=xlPicture

Paste image - I don't even need a delay now
PPSlide.Shapes.PasteSpecial (ppPasteEnhancedMetafile)

This code now works perfect every time for 18 powerpoint slides as it runs through multiple charts on the same sheet and even makes changes to some of the data before copying the charts.

Please Login or Register  to view this content.