AckeemK
asked on
Macro to open presentation and update charts
I am looking for a macro within Excel for Mac 2011 to open a presentation and update the charts that are in it. The charts that are in the presentation are located within the report in the 'Charts of SRs' tab. On the 'Table of Contents' tab, there is a button that says "Create Presentation" and I would like for the macro to be placed in there. The user will have both this report and powerpoint hand in hand but I want them to still be prompted to open the presentation to confirm that it is able to be updated. If they do not have the file or it can't be recognized, the user is informed that the presentation could not be updated.
DSE-Carelog-Report-V1.xlsm
DSE-Carelog-Slidedeck-Quarterly-Business
DSE-Carelog-Report-V1.xlsm
DSE-Carelog-Slidedeck-Quarterly-Business
Saurabh Singh Teotia
I'm not able to open the power point presentation due to extension missing..Can you give a short name to powerpoint and then update it again..Don't update files with such a long names as extension goes missing..Just reduce the file name and when you update make sure the extension of the file is visible...
ASKER
Attached is the slide deck as requested with a shorter name.
DSE-Carelog.pptx
DSE-Carelog.pptx
Quick question you open to creating chart in PPT and saving the workbook their in the ppt only with only chart data as that will be comparatively faster and easy to update?
If you are not open to above solution then..do you want to paste the chart as a picture as shown in your ppt? or how do you want to paste that? Also would have a template in place where you update these blank sides with new chart ??
In additional can you help me point out which chart will go to which slide ?? Point me like this column-A-M and rows-5-10 chart will go to slide-1 and so on..
If you are not open to above solution then..do you want to paste the chart as a picture as shown in your ppt? or how do you want to paste that? Also would have a template in place where you update these blank sides with new chart ??
In additional can you help me point out which chart will go to which slide ?? Point me like this column-A-M and rows-5-10 chart will go to slide-1 and so on..
ASKER
So the reason the file is so big originally is because I would copy the charts over from the "Charts of SRs" tab and paste special "Microsoft Excel Chart Object". I was informed this is how it was done for a previous tool similar to this one.
Ideally, I would want to click the button "Create Presentation" and have that slidedeck open up and the charts update based on what data is in the "Charts of SRs" tab. If you look at this tab, you will see that it is aligned with the charts within the presentation. Not sure which way you feel would be most appropriate for a smaller file size but whichever solution you feel will accomplish this we can go with.
Ideally, I would want to click the button "Create Presentation" and have that slidedeck open up and the charts update based on what data is in the "Charts of SRs" tab. If you look at this tab, you will see that it is aligned with the charts within the presentation. Not sure which way you feel would be most appropriate for a smaller file size but whichever solution you feel will accomplish this we can go with.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Jamie,
Very thankful for the coding. I've double check the constants and after running the macro, I keep receiving a error saying "Application-defined or object-defined error and when I click debug it takes me to line 66. Any actions I'm missing here?
oWS.ChartObjects(counter).
Very thankful for the coding. I've double check the constants and after running the macro, I keep receiving a error saying "Application-defined or object-defined error and when I click debug it takes me to line 66. Any actions I'm missing here?
oWS.ChartObjects(counter).
Is your sheet with the charts still called 'Charts of SRs' and if not, change this line accordingly:
Which version of Office are you using so I can double check?
Const sChartSheet = "Charts of SRs"Which version of Office are you using so I can double check?
I can reproduce on MSO 2013 (sorry, I only checked on Mac:2011). I will correct and repost changed code shortly.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Jamie,
Thanks again for all your help! This code accomplishes what I've been trying to do for some time now and I really do appreciate all your help on this.
Thanks,
Ackeem
Thanks again for all your help! This code accomplishes what I've been trying to do for some time now and I really do appreciate all your help on this.
Thanks,
Ackeem
You're welcome Ackeem. Glad it's working for you and thank you for the question!
ASKER
One last thing Jamie. I've placed that macro in the "Create Presentation" button and it works perfectly. The only issue is that when I try to rename the file to be "V2" at the end since this is the new version I want to release, I am receiving errors and the file completely shuts down. Any idea if changing the name to read V2 and not V1 affected the smoothness of the macro? I attached it so you can view the tool as well and make the change and see the errors.
DSE-Carelog-Report-V1.xlsm
DSE-Carelog-Report-V1.xlsm
The Excel filename isn't a dependency so I'm not sure what's happening there but I did forget to mention that when I opened your original project, I found a module containing the Sub CreatePresentation() procedure so added my code in that module. The code I have pasted back didn't change that but you need to make sure there aren't now two copies of that procedure in your project now. I'll download your latest file anyway and double check.
Hi Ackeem. I tried downloading your latest Excel file, saving it as "V2", renaming the pptx to your new name "DSE Carelog Slidedeck Business Review Template.pptx" and it works without code changes for me. Note that the Excel file and PowerPoint file must be in the same folder otherwise you will get an error. What error did you get?
By the way, I meant to say earlier, I would recommend renaming the modules from Module1...13 to something more descriptive to help maintain this project. In my test environment, I used "M_PowerPoint", "M_MacPC", "M_SelectFile" etc.
By the way, I meant to say earlier, I would recommend renaming the modules from Module1...13 to something more descriptive to help maintain this project. In my test environment, I used "M_PowerPoint", "M_MacPC", "M_SelectFile" etc.
ASKER
Hi Jamie. I was just able to fix it by separating out the V2 file and the slide deck into a different folder. I think it is good to go now and I really appreciate your help again! You rock!