How to Create an Amazing PowerPoint Dashboard With Dynamic Excel Backend [Tutorial Part III]
Вставка
- Опубліковано 27 жов 2024
- How to Create an Amazing PowerPoint Dashboard With Dynamic Excel Backend [Tutorial Part III]
► Subscribe for more awesome Excel and PowerPoint content: tinyurl.com/yd...
This is a tutorial on how to create an amazing PowerPoint Dashboard that can be dynamically updated from an Excel Backend File.
In this third and final part of the tutorial you will learn how to code two Excel VBA scripts that are required to update the respective charts or dynamic labels in the PowerPoint Dashboard File once a change in the data of the Excel Backend File has happened.
► Here you can find the other parts of the tutorial and the final dashboard demonstration video
Tutorial Part I: • How to Create an Amazi...
Tutorial Part II: • How to Create an Amazi...
Demo Video: • Amazing PowerPoint Das...
► Stay tuned and subscribe if you like to see more videos of this kind.
❤ Music by
/ ikson
www.bensound.com
#excel #powerpoint #dashboard
![How to Create an Amazing PowerPoint Dashboard With Dynamic Excel Backend [Tutorial Part I]](http://i.ytimg.com/vi/oU0WqM7cQfk/mqdefault.jpg)
![How to Create an Amazing PowerPoint Dashboard With Dynamic Excel Backend [Tutorial Part I]](/img/tr.png)
![How to Create an Amazing PowerPoint Dashboard With Dynamic Excel Backend [Tutorial Part II]](http://i.ytimg.com/vi/eKRpslgdX0w/mqdefault.jpg)
Tutorial Part I: ua-cam.com/video/oU0WqM7cQfk/v-deo.html
Tutorial Part II: ua-cam.com/video/eKRpslgdX0w/v-deo.html
Demo Video: ua-cam.com/video/Uat-xmtvA0M/v-deo.html
The Office Lab is there a way to make this work across a network?
Wonderful tutorial. You've made it as easy as you can. But alas, for like mo who is not familiar with encoding i hope you could provide a list of the script for me to see clearly. Thanks for being generous in your knowledge and skill.
The module:
Sub Refresh(ParamArray var() As Variant)
Dim pApp As Object
Dim pPreso As Object
Dim pslide As Object
Dim sPreso As String
'Powerpoint Dashboard Filepath
sPreso = "/Users/Dennis/Documents/Werbetafel/Dshboard.pptx"
'open and referenc Powerpoint Dashboard File
On Error Resume Next
Set pApp = GetObject(, "PowerPoint.Application")
If Err.Number 0 Then
Set pApp = CreateObject("PowerPoint.Application")
pApp.Visible = True
End If
On Error Resume Next
Set pPreso = pApp.Presentations(sPreso)
If Err.Number 0 Then
Set pPreso = pApp.Presentations.Open(Filename:=sPreso)
'Set pPreso = pApp.Presentations.Open(Filename:=sPreso)
End If
'Update every Chart in ParamArray var
Dim varSize As Integer
Dim i As Integer
varSize = UBound(var) - LBound(var) + 1
For i = 0 To (varSize - 1)
pPreso.Slides(1).Shapes(var(i)).LinkFormat.Update
Next i
End Sub
The spreadsheet:
Sub Worksheet_Change(ByVal Target As Range)
'Jedes Element erneuern:
'Diagramm 2
If Not Application.Intersect(Target, Range("D4")) Is Nothing Then
Call Refresh("Diagramm 2")
End If
End Sub
Sadly it doesn't work for Strings. Also ask for an salary increase when you're done.
Great bit of automation here Alex and well explained 👍 I guess anyone wanting to do this as a one-off could shortcut the process by refreshing all PowerPoint objects when anything in the Excel input area changes. I know it would be less CPU efficient but for a single presentation it would be quite a lot less coding. Worth doing more thoroughly like you for a dashboard you'll be using a while though.
Great tutorial tnx! Which code do i need when i have multiple sheets with charts?
Thanks @The Office Lab - Extremely Helpful, time saving and innovative Video. You're Gem!
It works, thank you so much :)
Note: If you are looking for solution, put a breakpoint on Worksheet_Change and debug it. Look for every line, every statement and every typos. Finally you'll get in there.
Hi, I'm not understand what you saying, but I hope you can breakdown the explanation. Because I have try many time, but every time I close the file (excel & ppt), the change won't give update to the presentation.
Hi, First of all thanks for this wonderful tutorial. Have a query, after VBA programming the data changes automatically in PPT but after close the file and reopen it the data haven't auto synced. How to solve this. Plz help.
Part I and II is simple to follow. But this part III is more difficult to understand the command function in VBA for common excel user. Maybe you can tell more about each command. Thx. Good job
Dear Sir
I was learning if you could share the background you've been using. I really like your PowerPoint dashboard please detail explain from basic .
Hey there. I was curious if you could show me how to do the same thing over multiple slides in PowerPoint? Would I need additional scripts to tell it which slide to look at?
This is also my query. :) but the video was brilliant.
Hi Friend.... your vídeos are great!!!!! Greetings from Colombia
Thank you so much, Sergio! I love to hear that 😊
great tutorial, thanks for detailed explanation.
Great tutorial
Glad you think so!
Thank you Alex, That's exactly what I'm looking for !! Great job!
It is incredible..... thank you so much . i am from #Bangladesh, Asia
Thanks very much for that !
Amazing tutorial!
Wonderful tutorial. It is very to follow and understand. However, I do have a question for the VBA coding since I have 7 pages presentation, and every page has their own chart - but the whole presentation use the same data in one excel file. How should the coding look like?
Awesome... really hats off
Well done 👌
This looks so fancy ! And is smart, too ;) And uses exactly the technics exactly what I was looking for. Tausend Dank Euch :-))
Wonderful, congratulations, here from Brazil
Thank you :)
I would like to see a video on how you connect / automaticaly import data från other programs into excel. And perhaps some Video where the program automatical creates a new sheet every month. In short term, More automation tuts!
Hi! I was wondering if you could share the bavkground you've been using. I really like the way it blends with your tutorials. Hopi g to here from you.
Cheers!
THANK YOU
Parabéns, excelente trabalho!
Amazing new subscriber I’ll be the hero at work lol 😂
Awesome! Congrats.
Dear Office Lab, Thanks for the Tutorials, in the first page of a Powerpoint presentation it works fine, but how can I also use this in the second or more pages of the Powerpoint?
@09:32, Check it out.
(1) for Slide 1, And put "(2)" if you want Slide 2.
Could we kindly ask to have the default code in text (so I don't need to look at the video and copy word by word), and then will change the few details (path, chart no', etc.) we have too.
Thanks so much again.
@Simple IT 👍👍👍
@Simple IT You have 2 typing mistakes.
Your presentation is amazing!
@@HongKong1842 want to point them out or....?
Great!! Thanks ; )
Thanks a lot for the video! Helped a lot! Can you please also guide how do I establish link when I reopen both these files post closing? Macro worked fine when I put in code
This might be late but just save the excel file in a macro friendly format. (xlsm.) Otherwise the VBA script won't save when you close the spreadsheet
This is pretty awesome.
Thank you :) Glad you like it.
Brilliant video, thanks a lot!
I have one issue though. All of my charts update no problem but once I close down the powerpoint and excel files and then open them up again, the link seems to have be broken. I have double checked my code and everything seems to be ok.
Do you have any ideas what my issue could be?
Thanks again!
Hey Robert. Is there any error message appearing? Sometimes links are getting broken when you move one of the files to another directory.
@@theofficelab I have the same issue, once I close the files and re-open them up again I have to click on PowerPoint charts to edit them so value update. There are no errors. It works awesome until you close the files. It needs something to activate the links again once the files are opened again. By the way, awesome tutorial, hands down, one really useful tutorial for anyone reporting and presenting such information. Thank you.
Another Question I have is, what if I have multiple slides that need to be linked, how do I modify the code so they are inlcuded in the refresh code?
For i = 0 To (varSize - 1)
pPreso.Slides(1).Shape(var(i)).LinkFormat.Update
@@MrEmirMansouriif you have multiple slides I suggest to modify the "Refresh" sub in such a way that it gets another argument (e.g. "slide_number") passed when it is called. Not sure if this is completely conform to VBA syntax, but I guess it would look like this:
Sub Refresh(slide_number as Integer, ParamArray var() as Variant)
...
For i = 0 To (varSize - 1)
pPreso.Slides(slide_number).Shape(var(i)).LinkFormat.Update
...
End Sub
Now let's assume you want to refresh "Chart 34" which is on the third slide in your presentation. Then you would call the "Refresh" sub like this: Call Refresh(3, "Chart 34"). Hope that helps.
Regarding your first question, I'm afraid I don't know what causes this issue with your file because actually the scripts are supposed to solve that exact issue. Maybe check if you have passed the correct chart names when you call the "Refresh" sub.
@@theofficelab Firstly, thanks for your tutorial. It has been usefull to me. I'm afraid that I've the same issue that @Emir Mansouriif:
There is more then one slide in my Power Point file that need to be updated (3 to be more specific).
When I close the files (ppt and excel) and open it again, no error messages are viewed and the charts are not updated on PPT.
Could you send to me a VBA Project that contains the code working?
This is great, I built it all out, got the same error you did, fixed it, but its still not working. And now im getting no errors, its just not changing. Can you help?
That's a great job
But I think the "link way" is better and easy, what do you think 🤔?
Useful tutorial and well explained. Everything works fine! However, whenever I create a copy of the files (Backend and Frontend) the charts are not updating. I have adapted the location of the Frontend file accordingly in the VBA code. I also checked the names of the objects that I try to update. They are the same in the VBA Code. Any idea why dynamic linking does not work when i create copy of the files and how to fix this?
Nice
Hey @The Office Lab, your video is amazing- it solves me a number of issues I had.
I'm facing an issue- once I close the PowerPoint and re-open it, sync from Excel to PowerPoint slides stops. I've had checked a file path and it's OK (once I change the value in Excel it open PPT file). If I re-select data from PowerPoint everything starts to work fine and live sync is working but once I re-open the file everything stops.
Any idea what is causing this?
Thanks,
Vlad
Hey Vladimir, I have the same issue. Did you find a solution so far?
I also have same issue
Please go to FILE, INFO, "Edit Links to Files".
Once there, place a check mark in "Automatic Update" for each of the links.
With this approach, you do not need the VBA code. You update the Excel file and save it. Close PowerPoint and re-open it. Click on "Update Link" in the pop up window and that is it. Every single entry updates.
I hope this helps
Check your intersect cell references, only reference cells into which new values are directly entered, do not reference cells whose values change in response to direct changes in other cells.
AMazing !!!
Thanks, Arrato 1977! I'm really glad you like it 😊
excellent video. she is very practical. but how can we do to get
macros
What is the difference between your solution and to copy the excel chart then use Paste Special in Power point and choose Past link? The data will be Synced every time you change the excel or open the power point choosing update links..
I found out something. The vba is needed ONLY for text-chart update. The vba is NOT required on pie-chart update.
You are mad!, Great! you're very creative!!!,
One small question if you can explain me, why can't you make (for the second part of the code) all in once, like: A1:F1000, call refresh for Chart 51, Chart 56, Chart 58 etc. ?
Thanks for your answer, and for the GREAT Video(s), I've learned something!
Thanks man, love to hear that :)
Regarding your question, I chose to do it that way in order to make the relevant chart refresh quicker.
@@theofficelab That's what I thought right after commenting...
I have a question on the Executives/Employees tab:
is it possible to create a button into the powerpoint slide that when clicked change those value, instead of changing manually in excell?
Let me explain better. Imagine to have into the powerpoint slide a "Executives +1" button, then (in presentation mode) when you click it it will add 1 in B8:C8, so in powerpoint you can see that executives changes from 8 to 9 without having to manually modify the value in the excell sheet. And imagine to set an animation trigger on that button, so when you click it you change the value and simultaneously play an animation (for example: "+1" text box popping near the number).
I'm used to create in powerpoint some lower thirds and overimposed graphics to use in live streamings. For example, i made a quiz show live on youtube with all graphics animated in powerpoint (yeah, lots of triggers and hidden animations) and captured via OBS (with chroma key for transparency). When i had to update the scoreboard, i had no other way than to switch scene in obs, exit presentation mode, reach the correct scoreboard slide, manualy change the text with the updated score, switch back to presentation mode, switch back to the correct scene in obs. All of this while directing the live show. Do you think it's possible to create those buttons?
Hey cavatappi, that idea sounds quite interesting and I am pretty sure it's possible to implement this feature the way you described it. I guess it would require another VBA 'layer' on top in form of a little Powerpoint VBA script that writes the incremented value into the excel file, then triggering the label-update VBA script that is already implemented in order to update the label in the dashboard. Adding an animation trigger for a "+1" animation should also be possible. However, my only concern is that there will be a significant time lag between pressing the button and updating the respective label with that much cross-file VBA action happening in between.
But I will put that onto my list and try to realize it once I find a little time ;)
Does it work on someone's computer if I email the PPT and Excel to co-worker?
Hi The Office Lab: Do you do consulting in the area of dynamic excel/PPT updates?
Hi Mick, currently I don't do consulting at all for reasons of time. Sorry for that.
Great job! anyway the Refresh macro for me doesn't work, it doesn't read pPreso.Slides. Any help?
I have just spent 12 hours smashing my head against a brick wall trying to sort this. Anyway, in the end it boiled down to me needing to watch the video again, pausing a lot, and using a bit of a deductive logic. This works.
Will it automatically update if the slide is currently in slide show??
Who else went back to the manual update part and was happy with that? Lol
Excellent video! I have a question. for example, when you do a dashboard in excel normally there are slice, where you can control the charts,etc. Is it possible to add this types of slices to powerpoint and control the charts in powerpoint?
The only idea that come to my mind to solve this is to open the powerpoint in one screen and the excel file in other screen, in this case you can control the powerpoint with the slices but this slices are in the excel file. Any idea would be really appreciated. Thanks
I saw the other video, that you can update the data even in presentation mode. That is really also a good solution.
Is it different for a pivot chart? I made a pivot table, then i made the chart. I select at the VBA script the cells of the pivot table but I can't make it work like in the other cases
that was a great question! i would add: slices and... dynamic tables. like why do we create a sales chart already full of data? don't we want to have daily or monthly updates retrieved from external report files? i mean who needs to automatically update presentation if the list of sales months was already in?
Would really appreciate the code posted. I've been through it several times and it doesn't work for me.
Trying to copy the VBA script but I am receiving error after error on PPt 2016 - can you advise as to whether the script as per the video is still accurate - could you provide the VBA script file please?
Hello! I recently tried doing your video, but the VBA coding isn’t working. It says the: Sub Worksheet_Change(ByVal Target as Range) is incorrect. What am I doing wrong?
Does this work if I am getting a CSV that is automatically pushed out every 5 seconds to the same folder destination with the same name? The data is changing so every 5 seconds a new CSV with the same name is pushed to the same directory location. Would I lose all coding that was built in the original CSV?
Is there a way to make this work across an office network? I want to display the presentation and as people make updates to file it displays real time. Please let me know in what direction I should look.
Hey Melchor, it depends on your office network structure. If you have a shared place to put both files (like a server file system, that is accessible to anyone who has the respective rights) it should be no problem to display the presentation while your people access the excel file (separately, not at the same time) in the background to update it.
Great Video, but my back end dashboard isn't updating my power point and i did everything on the video.
You may need to "refresh chart" first. It might work after that. Alternatively, you've made a small error in VBA, or possibly the "range" you are selecting to reference the chart source data in the VBA isn't correct. These are some of many solutions I needed to check before fixing the many issues I had with it.
Tooshlong thank you
do you have o save it as an xlsm file?
Yes and you have to activate them
can you please tell me how to change the code if I have more than one slide?
Hey. get this bug all the time. Help please
On Error Resume Next
sat pApp = GetObjet(, "PowerPoint.Application")
Do you teach any online classes?
Not yet ;)
could you share this template?
I am having the same problem, when I close Excel the charts stop updating, that file you created do they work normal after you close?
I have reviewed the codes more than once and everyone is correct, I was in doubt on this code 'Define PowerPoint Dashboard File Patch.
Please, could you help me.
Yes, with my files that works.
That exactly is the actual purpose of the code, to make sure the charts are updated even after closing and reopening the files. If it does not work for you you might have the wrong path for the PowerPoint file inserted at that line of the code. You have to replace it with the place where you saved your PowerPoint file.
@@theofficelab I need to put all the location where the files is, type from the C: disk?
Yes, so assuming you are using windows and your PowerPoint file is in the Documents folder, you should put in something like C:\Users\User_Name\Documents. You can get the complete file path by right-clicking on the PowerPoint file.
@@theofficelab I checked in here and the path is right.
Can I send the worksheet to you to see if the code is right?
You're going to help me a lot, thank you.
@@dbritoteixeira We solved it and a simple way to solve it is to change the path in the macro so that it always works (instead of looking for a specific path that looks for the name of the file) if you need help you can contact me Whats app +1 809-627-8739 and by email lucasarmando417@gmail.com, and again Congrat to Alex for excellent Job. @The Office Lab
Why is pSlide not used?
This tutorial is excellent but lost me at the VBA juncture.
Set pPresp = pApp.Presentations.Open(Filename:=sPreso)
is that typo? or just like that? thanks before
Set pPreso = pApp.Presentations.Open(Filename:=sPreso)
Access to powerpoint ? Can you?
it cannot work for me....no error showing....code is completely fine....at power point values cannot change but in excel file it change...
Same there, have you figured out it?
any missing letter will affect the process , make sure the every single letter is there.
this doesn't work anymore in 2024.
i tried everything, but nothing works.
@UCKsfJnvC-HCoEQEEIKZIzQQ Alex your job is excellent, go aheat bro!!!
I mean go ahead
Thank you :)
Ok there is somebody beneath this video trying to be terribly helpful by posting the code 1:1. It has mistakes in it. Save yourself the time and energy by following the video, not taking short cuts.
Needless to say. This didn't work. No idea why. Wtf does office not allow for simple integration of their own products. Pisses me off the time I'm wasting on this project shit.
EDIT: I DID IT!!!!!! WHOOOOOP YOU UTTER LEGEND!!!!!!!!!!
Is the link still working after closing the PP and Excel? If yes, can you write what's the point?
@@Toczusiek I don't really understand your question. What link? Yes this works after closing PP and Excel.
@@tooshlong I meant the link between Excel and PP, you answered that it works so the link still exists.
I have 1:1 author's code (without misprints) and it doesn't update my charts in Powerpoint after closing. Debugger shows that it works fine, dunno what to do.
@@Toczusiek ah. the link as in "the ability of PowerPoint to update automatically via the macro".
Hmmm he hasn't posted the macro though and I know this is an irritating suggestion but a typo is likely if copied, as it is *long*.
I had to literally rewatch the video 3 times to find the mistake.
I had to then manually refresh each chart once all was inputed correctly.
Beyond these basics I don't know what to suggest. It definitely works. But as with everything in life, nothing is simple, and it took a lot of faffing around before locating the issue.
Assuming excel and PowerPoint software is up to date (with his at least), this works. You've made an irritating mistake somewhere.
@@Toczusiek btw somebody beneath the video has posted the code. It has mistakes in it lol. I felt like telling him, "thanks for nothing". But didn't. Don't copy anyone elses code. Input it manually. Slow the video down by persistently pausing and rewinding, and do it off a separate screen. This will work assuming all else is equal.