Wow this is amazing! Finally found a VBA solution that solved my issues (have been searching for it all night on Stack Overflow). Thanks a million anameiwontforget! Btw - I can only second that this would be even better if it showed how to update PP charts (or more correctly the numbers behind them). Many PP slides pres. that needs automation have charts in them. Thx!
Dim ppapp As PowerPoint.Application Dim pppres As PowerPoint.Presentation Sub getshapedata() On Error GoTo line1 Set ppapp = GetObject(, "Powerpoint.application") Set pppres = ppapp.ActivePresentation Dim shapeslide Dim shapename Dim shapetext Dim nextrow shapeslide = ppapp.ActiveWindow.View.slide.slideindex shapename = ppapp.ActiveWindow.Selection.ShapeRange(1).Name shapetext = pppres.slides(shapeslide).Shapes(shapename).TextEffect.Text friendlyname = InputBox("Insert Friendly Name for " & shapetext, "Friendly Name", "") nextrow = Sheet1.Range("a" & Rows.Count).End(xlUp).Row + 1 Sheet1.Range("a" & nextrow) = shapeslide Sheet1.Range("b" & nextrow) = shapename Sheet1.Range("c" & nextrow) = shapetext Sheet1.Range("d" & nextrow) = friendlyname Exit Sub line1: MsgBox "No item selected" End Sub
The suggested code uses a worksheet codenamed Sheet1. This is different from the name that shows on the tab of the worksheet, and so tends to confuse novice VBA programmers who just want to copy and paste code from the internet. In actual fact, I was directed to this RUclips video by a person who had exactly that problem--an inexplicable runtime error when trying to use the code. I recommend using a With block to capture all the references to a specific worksheet in a single, easy to edit location. In the suggested code below, I used the ActiveSheet in that With block (both subs). I also declared the variables as Integer, String, Range or Long instead of the default Variant. Dim ppapp As PowerPoint.Application Dim pppres As PowerPoint.Presentation Sub getshapedata() Dim shapeslide As Integer Dim shapename As String Dim shapetext As String Dim friendlyname As String Dim nextrow As Long On Error GoTo line1 Set ppapp = GetObject(, "Powerpoint.application") Set pppres = ppapp.ActivePresentation shapeslide = ppapp.ActiveWindow.View.Slide.SlideIndex shapename = ppapp.ActiveWindow.Selection.ShapeRange(1).Name shapetext = pppres.Slides(shapeslide).Shapes(shapename).TextEffect.Text friendlyname = InputBox("Insert Friendly Name for " & shapetext, "Friendly Name", "") With ActiveSheet nextrow = .Range("a" & .Rows.Count).End(xlUp).Row + 1 .Range("a" & nextrow) = shapeslide .Range("b" & nextrow) = shapename .Range("c" & nextrow) = shapetext .Range("d" & nextrow) = friendlyname End With Exit Sub line1: MsgBox "No item selected" End Sub Sub writedata() Dim c As Range Dim shapeslide As Integer Dim shapename As String Dim shapetext As String Dim friendlyname As String Set ppapp = GetObject(, "Powerpoint.application") Set pppres = ppapp.ActivePresentation With ActiveSheet For Each c In .Range("a2:a" & .Range("a" & .Rows.Count).End(xlUp).Row) shapeslide = .Range("a" & c.Row) shapename = .Range("b" & c.Row) shapetext = .Range("c" & c.Row).Text friendlyname = .Range("d" & c.Row) pppres.Slides(shapeslide).Shapes(shapename).TextEffect.Text = shapetext Next c End With End Sub
Thanks this is really helpful, I am working on a similar project but in place of text box I am using tables in PPT and want to update required cells. Can you please suggest what change in the code is required to achieve this?
Great video. Thanks! Hoping you can expand on this a little. I want to use the getdata sub you created to find the reference to a selected cell in a powerpoint table. So in addition to the slide number and shape, I need the cell row and cell column returned as well. Thanks in advance for the help.
Very helpful tutorial! Is there a way to expand that to every shape in my ppt, not only the selected one? For the part "get data, and then à could just delete the ones i dont want? That way i could just do it once
Do you know how to control a video in a slide with buttons? I inserted some bookmarks on the video, and I want the buttons to start playing the video at bookmarks times. For example, button 3 starts bookmark 3, button 4 starts bookmark 4, etc. I couldn't find out a way to do that using normal Powerpoint actions. Maybe with VBA it is possible. What do you think?
Hello. This is great, I need this. I was wandering you know of a way to update tables behind the charts on PP in a similar fashion. I know there is a way to make linked tables. But that requires excel to be linked to specific PP file. It would be cool if there is a way to update any open/active powerpoint through excel - in the same way as you shown here. Thanks for the video.
thank you so much for sharing this code, but I have a problem, it doesn't run for me, despite having chosen the Shape in PowerPoint Presenation, it gives the error that no box is selected
Great stuff. May I ask if you have any advice on how to transform this xlsm into an interactive web page and at the same time still able to interact with the active Powerpoint? I'd tried a couple of the third-party spreadsheet converter software but none is able to make the button interactive like the original one. I'm using Excel 2013. Thanks in advance for your kind advice.
Thats really fantastic! I was wondering how difficult the same process would be bringing a template full of tables and dashboards to powerpoint using vba?
adding a template powerpoint slide, Like a sales presentation, Can you use this method to quickly change the content in the template? if so what would need to be add to the workflow and the VBA program?
I always get this one error that says run time error: slides (unknown member): bad argument type. Expected collection index (string or integer). May I know why?
i'm creating a virtual exam (like CFA mock tests).... i already have a question bank in excel i'm trying to improve the text boxes layout, since i have simple alternative questions(a, b, c, d) but i also have sufficient questions I - sentence I II - sentence II III - sentence III a) only I b) I & III c) II & III d) I, II & III i need to know how to adjust the size of the text box, depending the size of the text. if anoyone could help, i'd be very happy!
This is a great video! Really helpful! Just one questions is there a way to update the powerpoint while it is not active? For example, I am trying to update a ppt file on my google drive so that it updates live on another computer that is playing the ppt in the lobby of my office. What would you suggest the best way to go about this is? Thanks for the amazing video!
+Alizain Maredia One way I can think of is if you have text files on your Google Drive, and then have a macro that pulls the text from the text files for the text you want to change. It could check for updates how ever often you program in the macro. This would allow you to remotely save the text file, Google Drive would sync the file, then your computer in the lobby would automatically update the PowerPoint presentation that is currently showing. You could even write a macro that would allow you to export data from excel to a text file if you wanted your data to match some data from excel
here it is... don't forget you still need to enable the PowerPoint library in the Tools-References option of VBA Dim ppapp As PowerPoint.Application Dim pppres As PowerPoint.Presentation Sub getshapedata() On Error GoTo line1 Set ppapp = GetObject(, "Powerpoint.application") Set pppres = ppapp.ActivePresentation Dim shapeslide Dim shapename Dim shapetext Dim nextrow shapeslide = ppapp.ActiveWindow.View.Slide.SlideIndex shapename = ppapp.ActiveWindow.Selection.ShapeRange(1).Name shapetext = pppres.Slides(shapeslide).Shapes(shapename).TextEffect.Text friendlyname = InputBox("Insert Friendly Name for " & shapetext, "Friendly Name", "") nextrow = Sheet1.Range("a" & Rows.Count).End(xlUp).Row + 1 Sheet1.Range("a" & nextrow) = shapeslide Sheet1.Range("b" & nextrow) = shapename Sheet1.Range("c" & nextrow) = shapetext Sheet1.Range("d" & nextrow) = friendlyname Exit Sub line1: MsgBox "No item selected" End Sub Sub writedata() Dim c As Object Dim shapeslide Dim shapename Dim shapetext Set ppapp = GetObject(, "Powerpoint.application") Set pppres = ppapp.ActivePresentation For Each c In Sheet1.Range("a2:a" & Sheet1.Range("a" & Rows.Count).End(xlUp).Row) shapeslide = Sheet1.Range("a" & c.Row) shapename = Sheet1.Range("b" & c.Row) shapetext = Sheet1.Range("c" & c.Row).Text friendlyname = Sheet1.Range("d" & c.Row) pppres.Slides(shapeslide).Shapes(shapename).TextEffect.Text = shapetext Next c End Sub
anameiwontforget I have some questions and think you could help me. I wanted to adapt your tutorial for a simple slide deck and excel file. I have 12 columns of data (30 rows). Each row of data in the excel file essentially makes a new slide for the deck. Each slide has a bulleted list. The first bullet has some text, then cell A data, a hyphen, cell G data, a #, cell B data, more text cell K data, more text, cell E data, a comma, cell AF data, more text. Then comes bullet 2 indented 1 level, which is cell L data. Bullet 3 is one more indentation level, some text, and cell T data... Bullets 4-7 are similar to bullet 3. The goal is to autobuild the slide deck based on the excel file which is subject to change cell values and number of rows. If you have time to explain Gmail jbs4radio, thanks.
Wow this is amazing! Finally found a VBA solution that solved my issues (have been searching for it all night on Stack Overflow).
Thanks a million anameiwontforget!
Btw - I can only second that this would be even better if it showed how to update PP charts (or more correctly the numbers behind them). Many PP slides pres. that needs automation have charts in them.
Thx!
Thanks so much. You are saving my life.
It is difficult to watch your code in the video. Would you please put your code in the Video information column
Dim ppapp As PowerPoint.Application
Dim pppres As PowerPoint.Presentation
Sub getshapedata()
On Error GoTo line1
Set ppapp = GetObject(, "Powerpoint.application")
Set pppres = ppapp.ActivePresentation
Dim shapeslide
Dim shapename
Dim shapetext
Dim nextrow
shapeslide = ppapp.ActiveWindow.View.slide.slideindex
shapename = ppapp.ActiveWindow.Selection.ShapeRange(1).Name
shapetext = pppres.slides(shapeslide).Shapes(shapename).TextEffect.Text
friendlyname = InputBox("Insert Friendly Name for " & shapetext, "Friendly Name", "")
nextrow = Sheet1.Range("a" & Rows.Count).End(xlUp).Row + 1
Sheet1.Range("a" & nextrow) = shapeslide
Sheet1.Range("b" & nextrow) = shapename
Sheet1.Range("c" & nextrow) = shapetext
Sheet1.Range("d" & nextrow) = friendlyname
Exit Sub
line1:
MsgBox "No item selected"
End Sub
Thank you, so much, for this video! Great job. 🙂🙂
The suggested code uses a worksheet codenamed Sheet1. This is different from the name that shows on the tab of the worksheet, and so tends to confuse novice VBA programmers who just want to copy and paste code from the internet. In actual fact, I was directed to this RUclips video by a person who had exactly that problem--an inexplicable runtime error when trying to use the code.
I recommend using a With block to capture all the references to a specific worksheet in a single, easy to edit location. In the suggested code below, I used the ActiveSheet in that With block (both subs). I also declared the variables as Integer, String, Range or Long instead of the default Variant.
Dim ppapp As PowerPoint.Application
Dim pppres As PowerPoint.Presentation
Sub getshapedata()
Dim shapeslide As Integer
Dim shapename As String
Dim shapetext As String
Dim friendlyname As String
Dim nextrow As Long
On Error GoTo line1
Set ppapp = GetObject(, "Powerpoint.application")
Set pppres = ppapp.ActivePresentation
shapeslide = ppapp.ActiveWindow.View.Slide.SlideIndex
shapename = ppapp.ActiveWindow.Selection.ShapeRange(1).Name
shapetext = pppres.Slides(shapeslide).Shapes(shapename).TextEffect.Text
friendlyname = InputBox("Insert Friendly Name for " & shapetext, "Friendly Name", "")
With ActiveSheet
nextrow = .Range("a" & .Rows.Count).End(xlUp).Row + 1
.Range("a" & nextrow) = shapeslide
.Range("b" & nextrow) = shapename
.Range("c" & nextrow) = shapetext
.Range("d" & nextrow) = friendlyname
End With
Exit Sub
line1:
MsgBox "No item selected"
End Sub
Sub writedata()
Dim c As Range
Dim shapeslide As Integer
Dim shapename As String
Dim shapetext As String
Dim friendlyname As String
Set ppapp = GetObject(, "Powerpoint.application")
Set pppres = ppapp.ActivePresentation
With ActiveSheet
For Each c In .Range("a2:a" & .Range("a" & .Rows.Count).End(xlUp).Row)
shapeslide = .Range("a" & c.Row)
shapename = .Range("b" & c.Row)
shapetext = .Range("c" & c.Row).Text
friendlyname = .Range("d" & c.Row)
pppres.Slides(shapeslide).Shapes(shapename).TextEffect.Text = shapetext
Next c
End With
End Sub
Is there a way to do the same for charts or is this only for text? Thank you for the code above as my sheet was sheet23 instead of sheet1.
This is SOOOOOO helpful. You just saved me days of work! thanks so much.
Thanks this is really helpful, I am working on a similar project but in place of text box I am using tables in PPT and want to update required cells. Can you please suggest what change in the code is required to achieve this?
Great video. Thanks! Hoping you can expand on this a little. I want to use the getdata sub you created to find the reference to a selected cell in a powerpoint table. So in addition to the slide number and shape, I need the cell row and cell column returned as well. Thanks in advance for the help.
Hi anameiwontforget
Great video I must agree, but do you have the module code available as text/a file?
Dude, that was awesome! You saved my life here, haha! Great video!
Very helpful tutorial! Is there a way to expand that to every shape in my ppt, not only the selected one? For the part "get data, and then à could just delete the ones i dont want? That way i could just do it once
Where are you defining the VBA action for the button time 9:03?
Do you know how to control a video in a slide with buttons? I inserted some bookmarks on the video, and I want the buttons to start playing the video at bookmarks times. For example, button 3 starts bookmark 3, button 4 starts bookmark 4, etc. I couldn't find out a way to do that using normal Powerpoint actions. Maybe with VBA it is possible. What do you think?
Hello. This is great, I need this. I was wandering you know of a way to update tables behind the charts on PP in a similar fashion. I know there is a way to make linked tables. But that requires excel to be linked to specific PP file. It would be cool if there is a way to update any open/active powerpoint through excel - in the same way as you shown here. Thanks for the video.
thank you so much for sharing this code, but I have a problem, it doesn't run for me, despite having chosen the Shape in PowerPoint Presenation, it gives the error that no box is selected
Great stuff. May I ask if you have any advice on how to transform this xlsm into an interactive web page and at the same time still able to interact with the active Powerpoint? I'd tried a couple of the third-party spreadsheet converter software but none is able to make the button interactive like the original one. I'm using Excel 2013. Thanks in advance for your kind advice.
Awesome! Thanks a lot!
Just one question.. What do I need to change to specify the ppt's file location instead of using ActivePresentation?
thanks for this tutorial! this is really helpful! :D
Thank you, great video and reply with the coding.
any idea how to automate creating a PPT, using pre made sections that are stored on a sharepoint drive?
Would this work in Slide Show mode?
Is it possible to duplicate the slide with another data written? like when your doing certificates? same template but different names?
Thats really fantastic! I was wondering how difficult the same process would be bringing a template full of tables and dashboards to powerpoint using vba?
Can I know if its possible to do like an auto file generator from excel into a fixed powerpoint template using this ?
How would I go about selecting ALL textboxes (instead of one by one) in a powerpoint slide, and getting the data for each object in Excel at once?
adding a template powerpoint slide, Like a sales presentation, Can you use this method to quickly change the content in the template? if so what would need to be add to the workflow and the VBA program?
Probably, but what needs to be added depends on what you are trying to accomplish and your specific application.
It's amazing! Thanks a lot!
Thank you so much for sharing this, do you have a personal blog to learn some more codes... Please reply
how can I bold the font and the change font size of the slide number using vba ?
I always get this one error that says run time error: slides (unknown member): bad argument type. Expected collection index (string or integer). May I know why?
i'm creating a virtual exam (like CFA mock tests)....
i already have a question bank in excel
i'm trying to improve the text boxes layout, since i have simple alternative questions(a, b, c, d) but i also have sufficient questions
I - sentence I
II - sentence II
III - sentence III
a) only I
b) I & III
c) II & III
d) I, II & III
i need to know how to adjust the size of the text box, depending the size of the text.
if anoyone could help, i'd be very happy!
awesome, thanks !
good job 👍
This is a great video! Really helpful! Just one questions is there a way to update the powerpoint while it is not active? For example, I am trying to update a ppt file on my google drive so that it updates live on another computer that is playing the ppt in the lobby of my office. What would you suggest the best way to go about this is? Thanks for the amazing video!
Are you trying to just update text or change whole slides?
just do a live update on the text in the power point. Any ideas on how to do this is much appreciated!
+Alizain Maredia One way I can think of is if you have text files on your Google Drive, and then have a macro that pulls the text from the text files for the text you want to change. It could check for updates how ever often you program in the macro. This would allow you to remotely save the text file, Google Drive would sync the file, then your computer in the lobby would automatically update the PowerPoint presentation that is currently showing. You could even write a macro that would allow you to export data from excel to a text file if you wanted your data to match some data from excel
If I have a template to which I want to autopopulate data from excel how do I do this?
I am getting error saying "user defined type not defined". What to do aname
Hi, how I can copy-paste charts?
Please redo this video. It'd be massively useful!
thanks so much
This doesn't work in Office 2016. Anyone know why?
Great explanation, can you past the code in the comments?
here it is... don't forget you still need to enable the PowerPoint library in the Tools-References option of VBA
Dim ppapp As PowerPoint.Application
Dim pppres As PowerPoint.Presentation
Sub getshapedata()
On Error GoTo line1
Set ppapp = GetObject(, "Powerpoint.application")
Set pppres = ppapp.ActivePresentation
Dim shapeslide
Dim shapename
Dim shapetext
Dim nextrow
shapeslide = ppapp.ActiveWindow.View.Slide.SlideIndex
shapename = ppapp.ActiveWindow.Selection.ShapeRange(1).Name
shapetext = pppres.Slides(shapeslide).Shapes(shapename).TextEffect.Text
friendlyname = InputBox("Insert Friendly Name for " & shapetext, "Friendly Name", "")
nextrow = Sheet1.Range("a" & Rows.Count).End(xlUp).Row + 1
Sheet1.Range("a" & nextrow) = shapeslide
Sheet1.Range("b" & nextrow) = shapename
Sheet1.Range("c" & nextrow) = shapetext
Sheet1.Range("d" & nextrow) = friendlyname
Exit Sub
line1:
MsgBox "No item selected"
End Sub
Sub writedata()
Dim c As Object
Dim shapeslide
Dim shapename
Dim shapetext
Set ppapp = GetObject(, "Powerpoint.application")
Set pppres = ppapp.ActivePresentation
For Each c In Sheet1.Range("a2:a" & Sheet1.Range("a" & Rows.Count).End(xlUp).Row)
shapeslide = Sheet1.Range("a" & c.Row)
shapename = Sheet1.Range("b" & c.Row)
shapetext = Sheet1.Range("c" & c.Row).Text
friendlyname = Sheet1.Range("d" & c.Row)
pppres.Slides(shapeslide).Shapes(shapename).TextEffect.Text = shapetext
Next c
End Sub
anameiwontforget Hey are you able to post the rest of it? Please and thanks!
anameiwontforget I have some questions and think you could help me. I wanted to adapt your tutorial for a simple slide deck and excel file. I have 12 columns of data (30 rows). Each row of data in the excel file essentially makes a new slide for the deck. Each slide has a bulleted list. The first bullet has some text, then cell A data, a hyphen, cell G data, a #, cell B data, more text cell K data, more text, cell E data, a comma, cell AF data, more text. Then comes bullet 2 indented 1 level, which is cell L data. Bullet 3 is one more indentation level, some text, and cell T data... Bullets 4-7 are similar to bullet 3. The goal is to autobuild the slide deck based on the excel file which is subject to change cell values and number of rows. If you have time to explain Gmail jbs4radio, thanks.
did you get an answer to this one? can you please share.
Code not visible Hero 🦸♂️
God...font is not easily visible.
thanks for fast explanation. without stupid music and "how to run power point" shit
Thank you so much
thank you very much