Solved

Error when exporting from excel to powerpoint

Posted on 2014-01-06
10
409 Views
Last Modified: 2014-01-09
I have used this same code countless times, but all of a sudden I'm getting errors.  In fact, I execute the same code multiple times in the same macro, but I only seem to have a problem on this one slide.  For simplicity, I've extracted just the snippet I'm having trouble with and attached it here.  I'm certainly confused why I'm having a problem pasting here, but that is where it is getting hung up.  

This was working when I copied just the chart, but when I changed it to copy a range of cells, I started getting this error (I do it both ways for different slides, depending on whether or not I need data that is in a cell outside of the chart).

Also, it seems to be taking an inordinate amount of time to get through the copy step.  Again, I've run similar code for years but it's never taken so long to execute.  The entire macro exports data to a presentation with 15 slides, and on some computers it has taken upwards of 30-45 seconds to run, which seems ridiculous (that was before I started getting this error).  Is there a way I can make this execute more quickly?

As always, your help is appreciated!
0
Comment
Question by:Rich5150
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
10 Comments
 

Author Comment

by:Rich5150
ID: 39761000
Here are the files for your testing.
Excel-example.xlsm
powerpoint-example.pptx
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 39761186
Hi, under
    Set pptApp = CreateObject("PowerPoint.Application")

Try adding
    pptApp.Visible = True

Regards,

Rob.
0
 

Author Comment

by:Rich5150
ID: 39761268
That won't have any impact whatsoever on resolving this issue.  I did it anyway, and it still takes forever to execute and I still get the error.  

As I said before, the chart in the example file is actually the fifth item to be exported in my main file, and the first four work just great using the same code, which is what has me so perplexed.

FYI - I also tried running code cleaner on it but it had no effect.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 65

Expert Comment

by:RobSampson
ID: 39761324
I can't reproduce the error. Can you please post details of the error and the debug line?
0
 

Author Comment

by:Rich5150
ID: 39761998
You bet.  I know I'm stating the obvious, but I'm running the CreatePresentation macro which exports the chart on the Timeline worksheet to the PowerPoint file called powerpoint example, so you have to save both files to your hard drive before running that macro.  I have a button in my main file that executes that macro but I didn't think it that was necessary here.

Here is the error message:
error message
and the debug line:
debug line
0
 
LVL 23

Accepted Solution

by:
JSRWilson earned 500 total points
ID: 39762385
Hi Rich

You have the name of the Presentation wrong in the download I have - there's a hyphen missing. I guess this is a typo though.

When I step through your code

This line
Worksheets("timeline").Range("a1:k37").CopyPicture Appearance:=xlPrinter, Format:=xlPicture

Does not copy the range and the clipboard is empty.

Try using Appearance:=xlScreen instead of xlPrinter see if that helps.
0
 

Author Comment

by:Rich5150
ID: 39762467
I don't know where the hyphen came from.  There is no hyphen in the file name that I uploaded.  Anyhow, I've done a few more tests on those files.  If I don't have the chart there, everything executes well.  If I have the chart there with no data (ie, nothing graphed and no links to data), then it executes well.  However, with the data linked to the chart, it will not execute.  I'm assuming this is why Rob was unable to see the error, since when I opened the file that I had uploaded, it broke the links to the data in the original file.  That's my fault, but I wasn't considering that the data links could be the problem.

I've never run into this before.  This is a very complex graph that is created by another macro, but why would that make any difference if I'm simply copying a range of cells as a picture and then exporting that picture to PowerPoint?

This did work when I referenced the actual chart (as opposed to a range of cells) and copied that into PowerPoint as a picture, but as I mentioned before, this macro runs very slowly, and now I'm thinking that that also has something to do with the complexity of the graph.  Unfortunately I don't have any ideas on what to do about it.
0
 
LVL 23

Expert Comment

by:JSRWilson
ID: 39762481
Using the default xlScreen it ran much faster for me and no error. No idea why though!
0
 

Author Comment

by:Rich5150
ID: 39762534
JSRwilson,
Just tried your suggestion and it worked.  Also sped things up a bit.  Like you, I don't really know why.  Maybe some Microsoft engineer out there does.  Thanks for your suggestion!
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 39763869
Here is the documentation for the CopyPicture method:
http://msdn.microsoft.com/en-us/library/office/ff821596.aspx

xlPrinter appears to copy the picture as it would be printed.  I assume this puts the picture object through the printing process and renders it differently according to the print driver, and this may slow things down.   The default value is xlScreen anyway, so it would be best to leave it that way.

Regards,

Rob.
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

735 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