Automate the ppt with excel data

[jayalaxmi]

hi Folks,

I need one help from you..I am looking for how can i automate few slides of my ppt with the latest excel data. i have tried linking the excel with ppt. It works fine for me, but the issue that i am facing is when i open the ppt i am getting the update links pop up everytime. So i dont need that option to pop up when i send my ppt to my clients as that might me irritating and also i need the data from excel to ppt should be the latest one.
One more issue that i am facing is i have week wise sheet in my excel according to the particular month. so in my file name month keeps on changing every month but the excel will be same with different data. Also i need the ppt to be refreshed even if my file name changes. Please help me if there is any option available?

 

[GraH - Guido]

Hijayalaxmi,
I've done similar many times.
If you put the update flag to Manual, you don't get the pop-up.



I applied the following procedure:
1. Make a ppt template
2. link to Excel template, which has always the same name (not the same data)
3. Save Excel as template (while keeping the month version)
4. Update links in ppt (to reflect data changes)
5. Break all links (links are replaced by pictures) + save as ppt version month
6. Send to 3rd parties (they will never get the pop-up)

Could this work for you?

 

[jayalaxmi]

Hijayalaxmi,
I've done similar many times.
If you put the update flag to Manual, you don't get the pop-up.

View attachment 50653

I applied the following procedure:
1. Make a ppt template
2. link to Excel template, which has always the same name (not the same data)
3. Save Excel as template (while keeping the month version)
4. Update links in ppt (to reflect data changes)
5. Break all links (links are replaced by pictures) + save as ppt version month
6. Send to 3rd parties (they will never get the pop-up)

Could this work for you?

Hi Grah,

Many thanks!!!
Yes it helps..But the thing is for every month i have week 1 to Week 5 sheet in my excel. I use the same file as my base every month but changing the file name to Feb,Mar,April and so on...so the thing is every month/week i need to link to every new ppt deck that i share with the clients on weekly basis. eg ,week1,week2 and so on. It will of great help if you help me with this. Also i had used vlookup for getting the latest numbers according to weeks for every region and same issue i am facing as the filename(month) changes. I can share the base file for reference.

Note : I have region wise slide in my deck for every region and also i show the overall Country performance based on every week and MTD

Regards
Jaya

 

[GraH - Guido]

Jaya,
Hmmm... It does not really matter if you have 4 or 5 weeks.... Try this as a fine tuning... (proposal, right)
- As in proposed procedure, make a single "source data" template in Excel with 5 weeks and all sheets you need for region and country.
- Make 2 Excel-for-pptx templates: one for 4 and a second for 5 weeks. Both refer to the same source template via simple formulas: a reference to the correct sheet and cell address. So no need for vlookup...
- Make also 2 templates in ppt, linked to either one of the Excel-for-pptx templates.

1. When new data arrived, save the file as "source data".
2. Update the correct Excel-for-pptx (perhaps via macro, choose 4 or 5 week template). Open both files, the formulas are updated and save the Excel-for-pptx templates.
3. Open the correct (or both) pptx templates, update links, break link and save as pptx for period so and so.
4. share this final pptx with customers.

Now really clever people will propose doing it via macros. But it requires knowledge of VBA to call PowerPoint from Excel or vice versa. I cannot do that, so I used the above procedure which has helped me out several times.

 

[Deepak]

Hi @jayalaxmi ,

Good to see you after a long time.

As GraH - Guido suggested! First do the makeover at excel end and then auto transfer the same to PPT via VBA.

If you relies at PPT to xl refresh then there might be chances of data errors due to many things.

I am sharing a sample approach (Not mine) which will transfer xl to ppt, You may find many others thread to do so.

Would you pls share a sample dummy of your latest inboxed xl & ppt.

 

[jayalaxmi]

Hi Deepak,

Thank u for the reply..I am sharing my sample file(template) where i will use the same (base)template to reflect in my ppt(deck) every week(week 1,week 2, till week 5). In this template i am using just the vlookup from the template week 1,week 2 and so on according to the particular week. Also i would like to inform if i m publising the deck for week 1,the rest of the weeks the data will be blanks(should be shown as blanks) and for the coming weeks the data for rest of the weeks.My MTD % are available in MTD sheet. I got this idea of making this file as the base template as every week my file name changes to week 1,week 2 etc and the links wont work in such case. so i named the file without any week/month.

Note: Every month i will be having 4-5 weeks..in my ppt the slide number will differ every weeks due to addition of some slides. Its basically i am showing the performance for every weeks for overall Europe based on weeks /MTD. I am also showing the regional slides for every region in Europe such as UKI,EHQ,Italy etc..

Ur effort will of great help!!

Regards
Jaya

 

[Deepak]

Hi,

Why there are too many sheets for same type of dashboard, why not just setup a dropdown or similar for regional dashboard and same needs to be follow in Week report too.

This exercise will curb your all issue & you just need to select the week number for final output.(via VBA)