Using VBA to insert chart in Powerpoint from data in Excel

**Keroro** · 05-18-2017, 02:05 AM

Hi there

Please assist me with the following automating task using Excel 2010...

I need to write a macro, from my current workbook that contains the data, that will:
- copy the data from my current sheet
- open powerpoint
- use PowerPoint's Insert chart function as seen on the ribbon (choose bar graph) and upon which an excel sheet will appear to contain data for the particular chart
- paste the data from current worksheet into the sheet that was opened by powerpoint as values (once the data has been pasted, the chart should have formed it's shape)
- and return back to my excel workbook.

Ideally, at the end of the day, this automation should go thru each of my worksheets to get data and insert charts via PowerPoint which should just the loop through the above tasks.

However I am already having trouble without the looping and would like to know for now how to tell PowerPoint to implement it's insert chart function and paste my data using VBA .

I know that a potential solution is to make graphs inside excel and paste them into PowerPoint, but that is precisely what I am trying to avoid because I don't want to depend too much on the linking of data between PowerPoint and Excel.

Please please help!

Thanks

**kev_** · 05-18-2017, 07:07 AM

deleted by kev

**kev_** · 05-18-2017, 07:10 AM

Do you need the data in Powerpoint or only the chart?
If you only need the chart, I would create in Excel and paste the image to PowerPoint (then delete the chart in Excel if you do not want it there)
Otherwise you may find this Microsoft link useful

**Keroro** · 05-18-2017, 08:21 AM

I need the data in Powerpoint.

I have seen the link you have referred to me but I keep getting error on the line:

Set MyChart = ActivePresentation.Slides(1).Shapes.AddChart.Chart

**kev_** · 05-18-2017, 09:24 AM

Powerpoint Presentation needs to be Activated for that line to work
Otherwise you need to refer to the Presentation

Try something like this:

Please Login or Register  to view this content.

then where you open the file:

Please Login or Register  to view this content.

and your line of code becomes:

Please Login or Register  to view this content.

If you are still unable to move forward, post your code (enclosed in code tags) and I will look at it by tomorrow for you

**Keroro** · 05-19-2017, 02:29 AM

Hi there

Thanks for the assist.
I'm still getting errors like ActiveX component can't create object.
I've decided to settle for just copying over chart pictures from excel for now.

Here's my code:

Please Login or Register  to view this content.

Any idea how I would adjust my code ?

**kev_** · 05-19-2017, 04:26 AM

Hi Keroro - Thanks for the code
Does the code run without any errors?

I will look at this in the next couple of days

**Keroro** · 05-19-2017, 04:47 AM

Hi

Yes, it should run without error.
The first sub copies all my charts from previous sheets into the final sheet.
The second uses those charts in the final sheet and copies them into excel.

I also found the following that might be relevant but they give me errors:

Please Login or Register  to view this content.

Thanks again for the assistance. Much appreciated!

**kev_** · 05-20-2017, 08:56 AM

Let's take this in stages so that you understand the whole process.
STAGE1
Put the attached 2 files in the same folder,open PowerPoint file (ppFile1.pptm) & run macro CreateChartFromExcelData

What the code does:
- creates a column chart in the ppFile1
- opens up the Excel File
- copies the data from named range in Excel into PowerPoint's chart worksheet
- copies the chart title from named range in Excel into PowerPoint
- saves as new presentation ppFile2.pptm
- closes Excel

This is driven from within PowerPoint - which is where we need to start to fully understand the steps we are required to undertake. It illustrates:
- how to create a single chart using Data already present in an Excel file
- how to amend one chart attribute (chart Title)

I will update the thread later with a version driven from Excel

Please Login or Register  to view this content.

EDIT - oops -

Spotted a minor error - I wanted it to take the axis title from the workbook

Amend this line:

Please Login or Register  to view this content.

to:

Please Login or Register  to view this content.

**kev_** · 05-21-2017, 08:46 AM

STAGE2
This is the version driven from Excel
- download the attached workbook
- run sub CreateChartInPowerPointUsingExcelData
- PowerPoint file ppFile3.pptx is saved to same folder as the workbook

Please Login or Register  to view this content.

**kev_** · 05-21-2017, 09:48 AM

STAGE3

This version
- driven from Excel
- loops through all charts on sheet MyCharts
- creates and saves ppFile4.pptx

Microsoft does not appear to have anticipated your desire

.
- to mirror what is in the worksheet all attributes of every chart require setting up again FROM SCRATCH - perhaps there is a way using Excel Chart Templates - I doubt it but will investigate this next

It is virtually impossible to get at some of the values needed to easily mirror the charts eg SourceDataRange.
- so an additional sheet has been added with a lookup table for values required to create charts in PowerPoint
- I suggest that you set up all the chart attributes in a table and use the table first to create the charts in Excel and then to mirror everything in PowerPoint
(the table will need many more columns!)

Here are my workarounds:
to provide Source Range

Please Login or Register  to view this content.

to provide Chart Title

Please Login or Register  to view this content.

to match chart type

Please Login or Register  to view this content.

and the amended code is:

Please Login or Register  to view this content.

**Keroro** · 05-22-2017, 03:57 AM

Wow!

Thank you so much Kev!
Sorry for the delayed response.

I'll definitely work on this today.
Again, thanks so much for the assistance! This is a lot of material I can learn from

**kev_** · 05-22-2017, 04:46 AM

You are welcome

I have made some further progress - it is possible to use saved Excel chart templates in PowerPoint - which makes life a bit easier for you

In principle we should be able to:
- create new chart in PowerPoint
- copy active chart data from Excel to PowerPoint
- create active Excel chart "Template"
- apply Template to new PowerPoint chart

Today/tomorrow, I will test this to understand how to best use the template
- we still need a lookup table (eg for SourceDataRange and Title) but I need to establish which values will come from the chart template and which ones must be in the lookup table

I expect the final code to be very similar to the code in post#11
- amended to apply templates
- plus anything unique affecting each chart

**Keroro** · 05-23-2017, 10:38 AM

Hi Kev

I cannot thank you enough for showing me that my task is possible!

I've worked with what you've given me on xlFile4.

I've noticed that the macro doesn't run on my Office 2010 version (I'll only be able to test it again when I have access to a computer with Office 2016).

But for now, I wanted to ask that if my Excel File had multiple sheets and each sheet had a graph, how would I modify my code to go through each sheet (without the need to loop through the sheets)?
I was thinking of activating the sheet I need the graph from and then run the code that loops through all the charts in a sheet...

I've commented on the parts where I've edited

Please Login or Register  to view this content.

Is the above 'algorithm' sound?

**kev_** · 05-23-2017, 11:48 AM

Doing things with charts is often easier if you loop through them all, otherwise every chart has to be specifically referenced.
Clever naming, or grouping allows for easy inclusion or exclusion when some charts are not required.

Each chart needs referencing via its sheet
- coding is made much easier if we loop through all sheets\all charts picking out the ones we want
- otherwise we need to list every chart by name and by sheet

We need a way to refer to EVERY chart to be included in PowerPoint
- so common first few characters could be a useful way to go
and loop for all charts with names beginning "PP.."
- or put only relevant charts in adjacent sheets
and loop for all charts on sheets with index numbers within a given range

Q1. Your original code suggested multiple charts on sheets - have you now moved away from that plan?
Q2. I notice in the downloaded file that the charts on both sheets are called "Chart 2" - was that deliberate or an error?
Q3. How many charts and sheets will there be (rough number)? It may impact the approach
Q4. Will all charts be included in PowerPoint or only some?

**Keroro** · 05-24-2017, 02:24 AM

Hi Kev

A1: My original intention is to transfer all my charts, in each excel sheet into powerpoint. In my original code where I settled with just pasting pictures of the charts, the code only worked when I copied all my charts from the previous sheets into one sheet. (So it looks like I'm dealing with multiple charts in one sheet). But now it looks like I need to copy both the data source and chart into one sheet which could become messy so I thought I could just activate each sheet and run the code so the charts from different sheets are copied and "appended" into powerpoint for each new sheet.

A2: Apologies! That was an error I didn't notice.

A3: I intend to use this code on a sheet with at at 10 sheets where each sheet would have one chart.

A4: All charts will be included in powerpoint.

I agree with the looping through every chart; but I felt that it would be easier for me if I don't need to loop through the sheets and just activate the sheets and run code to transfer and append the charts into the powerpoint file.

Thanks.

**Keroro** · 05-24-2017, 03:36 AM

Hi

I've uploaded a modified version which kind of points to what I'm trying to achieve.

I've kept changes from the code in xlFile4 a minimum and only added a variable SheetName.

The code is quite static, but it appears to be doing what I want.
The macro OneSheet copies all the data sources and charts into one sheet.
I'll then run the macro CreateChartInPowerPointUsingExcelData2 which will transfer all those charts in that particular sheet to powerpoint.

Basically, is there a way to do the above, without having to copy the ranges and charts into one sheet (because data range sizes could change I'll have to ensure that I paste them in the right cells to ensure the data don't overlap).

**kev_** · 05-24-2017, 06:34 AM

I will look at posts 16 & 17 later today and get back to you tomorrow

I am puzzled by your earlier comment:

I've noticed that the macro doesn't run on my Office 2010 version

I have 2010 on my PC and 2016.
The macro works flawlessly on both versions for me.
So we should be able to get it to work on 2010 for you.
Can you identify where it stops working - I need to know which line fails and what the message is (if there is one)

**Keroro** · 05-24-2017, 07:19 AM

Hello Kev

I feel like such a fool.

I didn't realize that under References, I should have ticked Microsoft Powerpoint 14.0 Object Library. I overlooked that it said Missing: Microsoft Powerpoint 16.0 Object Library.

Sorry for the confusion! xlFile4 works now.
Gonna work on this today. Thanks for the assistance!

Will update you if I make progress.

**ecueva** · 01-18-2020, 05:58 PM

Hola, disculpe tal vez entro alguna solucion.

Ya que yo tambien quisiera exportar los datos que tengo en excel a una platilla de power point o que cree una nueva presentacion y los graficos pero con los datos exportados de excel.

Gracias.

**alansidman** · 01-18-2020, 08:48 PM

@ecueva

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

Using VBA to insert chart in Powerpoint from data in Excel

LinkBack

Thread Tools

Display

Using VBA to insert chart in Powerpoint from data in Excel

Re: Using VBA to insert chart in Powerpoint from data in Excel

Re: Using VBA to insert chart in Powerpoint from data in Excel

Re: Using VBA to insert chart in Powerpoint from data in Excel

Re: Using VBA to insert chart in Powerpoint from data in Excel

Re: Using VBA to insert chart in Powerpoint from data in Excel

Re: Using VBA to insert chart in Powerpoint from data in Excel

Re: Using VBA to insert chart in Powerpoint from data in Excel

Re: Using VBA to insert chart in Powerpoint from data in Excel

Re: Using VBA to insert chart in Powerpoint from data in Excel

Re: Using VBA to insert chart in Powerpoint from data in Excel

Re: Using VBA to insert chart in Powerpoint from data in Excel

Re: Using VBA to insert chart in Powerpoint from data in Excel

Re: Using VBA to insert chart in Powerpoint from data in Excel

Re: Using VBA to insert chart in Powerpoint from data in Excel

Re: Using VBA to insert chart in Powerpoint from data in Excel

Re: Using VBA to insert chart in Powerpoint from data in Excel

Re: Using VBA to insert chart in Powerpoint from data in Excel

Re: Using VBA to insert chart in Powerpoint from data in Excel

Re: Using VBA to insert chart in Powerpoint from data in Excel

Re: Using VBA to insert chart in Powerpoint from data in Excel

Thread Information

Users Browsing this Thread

Similar Threads

Powerpoint 2010 To Excel 2010 - Edit Data Updates Chart In Excel, But Not In Powerpoint

Excel/PowerPoint VBA teaser: How to change data labels in PowerPoint chart by VBA?

To get Chart Data Source of powerpoint chart by excel VBA

[SOLVED] Attempting to edit Excel chart data in PowerPoint

Unlinking Excel Chart Data in PowerPoint

transfer data from excel to powerpoint database chart

How to create Chart in Powerpoint automagically using Excel data

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions