Sometimes I receive a PowerPoint file that has an Excel chart that has linked data that is not available. (Previous employee didn't send data file with PPT, etc) I need to update this data. Is there any way to extract the current data somehow? Thanks.
Last edited by ImpetuousRacer; 05-30-2012 at 01:29 PM.
Hi
I'm sure one of the VBA experts could do a better job than this, but at least it sort of does the trick
Run the macro in Powerpoint. It will copy the x values and series names and values into the clipboard in a single long column that you can then paste into Excel. I haven't worked out how to arrange the series data in adjacent columns in the clipboard, though I'm sure this can be done.
Make sure microsoft forms 2.0 is checked in tools > references, or the dataobject code won't work
Please Login or Register to view this content.
I didnt see Microsoft Forms 2.0, so I went to browse, and found the FM20.dll, pressed ok, then I had the Microsoft Forms 2.0 option checked.
I ran it and it worked. However, there are multiple excel charts on the Slide. How do I extract for the chart I want?
If I press ALT+F10, it brings up the Selection and Visibility pane which allows to be see the number of the various charts. Perhaps something can be setup to input the chart number?
Thanks for the help!
Last edited by ImpetuousRacer; 05-25-2012 at 06:07 PM. Reason: Added information
how will you identify the chart you want - do you know its name or number?
If I press ALT+F10, it brings up the Selection and Visibility pane which allows me to see the naming of all the various charts. When I click on the chart I want, it highlights the Chart name/number.Originally Posted by NickyC
Hi
calling the selections and returning the value to the macro is beyond my VBA skills I'm afraid.
But this modification of the macro above achieves a similar result - listing all the chart shapes on the slide, allowing the user to select one then extracting the values from it.
Please Login or Register to view this content.
Perfect! This worked great! This will be a good solution for future instances of this I come across. Thanks so much for your help!
Just a recap for anyone else that comes across this thread.
1. If you cant find Microsoft Forms 2.0 in the object Tools Reference Library, search for FM20.dll
2. Use Alt-F10 to find chart number of chart you're looking for
3. If an axis has a date range in the chart, it will show as numerical value. Just format it as a date in Excel after pasting over.
A quick and dirty way to add a reeference to Forms 2.0 is to insert a user form into the file's VB project. When you remove the user form, the reference remains.
Jon Peltier
http://PeltierTech.com/
Trying to use this but the charts I have are appearing as "Content Placeholder n" where n is a number?
bump bump bump
Hi
I'm no PowerPoint expert, so may not be much use to you. My understanding is that the content placeholder is not a chart, it's a shape on the slide into which you can put charts etc. The macro will only work on a chart created in excel and copied as an excel chart into PowerPoint. if it's in another format (e.g. picture), the macro won't work.
Foxyloxy,
Welcome to the Forum, unfortunately:
Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.
If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
when I run the marco it tells me to select a range?
I'm a bit confused.
Hi Yookii & Welcome to the Forum,
Administrative Note:
- Somebody would be happy to help with your query, but first, before we can proceed…
- Please see Forum Rule #2...Do not post a question in the thread of another member -- start your own thread..
- If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.
- Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
HTH
Regards, Jeff
Hi
if anyone is looking for a macro that copies data values from a chart in PowerPoint back into Excel, this works for me
it copies data in columns, with x-axis data in the first column, first series data in the second etc. Set the first cell to copy into as DestCell
Please Login or Register to view this content.
when i use the last code it tells me : user-defined type not defined and blocks on Dim GFile As Excel.Workbook
any advice? can you help me on that Nick?
Try changing it to Dim GFile As Workbook
No, GFile is declared fine. The problem is this line:
Set GFile = GetObject(MyMsExcel.Activeworkbook.FullName)
Change it to
Set GFile = MyMsExcel.Activeworkbook
can't get it to work
It would be easier to figure out what's wrong if you indicated the code you're using, what you expected to happen, what happened instead, what error message you received (the complete text of the message, not just the error number), and what line was highlighted as causing the error.
Sorry about that. I'm not very good with VBA.
I'm using the latest code that Nick proposed on page 1 with the proposed modifications. I've attached it below.
It blocks on Dim GFile As Excel.Workbook and returns the following compile error: user-defined type not defined.
My excel is running fine in the background.
Please Login or Register to view this content.
Last edited by jeffreybrown; 08-18-2013 at 11:51 PM. Reason: Please use code tags...Thanks.
Okay, sounds like there's no reference set to Excel.
In PowerPoint's VB editor window, go to Tools menu > References, scroll down the list until you get to Microsoft Excel X.0 Object Library (X is 14 for Office 2010), and check the box in front of this line.
Now PowerPoint will know what Excel.Workbook and Excel.Range mean.
and it works
thank you very much for taking the time to help me Jon Peltier
I greatly appreciate it
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks