thank you for this . I will use those ideas, Andrew wonder if you use personal workbook to store macros. if so ever noticed the workbook get curropted? I have had this exerpience more than twice in less than a month how do you organise your macros save each in a module or bunch in a module? appreciate your feedback. thank you
Hi! I don't use the Personal Macro Workbook, but a couple of us at Wise Owl have experienced corruption with VBA files in the past few months. It's certainly encouraged me to keep more regular backups! How I organise procedures and modules depends on the complexity of the project. It would be very rare to create a separate module for each macro!
@@WiseOwlTutorials thank you for your input Any resources you recommend how to organize code snippets? Would have general purpose ones and definitely longer ones for a particular automation How do you get hold of your macros if you dont use personal work book? Curious as i am new to this Will ensure backing up then
@@ahmed007Jaber Hi! Honestly I don't tend to store snippets for VBA these days. Back when I was getting started I would tend to create a separate file for related techniques just for reference but I'm not claiming that's the most efficient way to do it 😀
You're welcome Azhar! I added a video recently to answer a similar question to create a new slide for each row of an Excel list ruclips.net/video/WOKKN03NVjs/видео.html It's only available for channel members with the early-access perk at the moment.
Spectacular video and examples! Thank you so much for sharing! Could you tell us what would happen if you emailed out a power point presentation with a link to an excel document? I would assume it would fail to open the excel document but I always like to verify with an expert.
Thanks Eric, glad you enjoyed it! When the user opens the PowerPresentation it will ask if they would like to update links. If they choose to update the links PowerPoint will tell them that the file can't be found. The user will still see a static image of the Excel document on the PowerPoint slide. Double-clicking the image will indeed fail with a message informing the user that the file could not be found. I hope that helps and thanks for the question!
@@WiseOwlTutorials thank you for the answer. I have been a subscriber / viewer of your channel for years and whenever one of my colleges has a vba question I always send them to your channel. Thank you much for all you do!
Hi! You can just apply the Copy method to the Shape object in Excel and paste it into PowerPoint, like so: Sub Copy_Pic_To_PowerPoint() Dim ppt As New PowerPoint.Application Dim pres As PowerPoint.Presentation Dim sld As PowerPoint.Slide
Set pres = ppt.Presentations.Add Set sld = pres.Slides.Add(1, ppLayoutBlank)
Sheet1.Shapes("Picture 2").Copy
sld.Shapes.Paste
End Sub You can find the name of the picture by selecting it and looking in the Name Box in the top left corner of the Excel window. I hope it helps!
Hi Andrew, what happens if the user changes the name of the excel file or versions up the excel file? Does the PowerPoint link automatically update and change the file path too or does it mess up the PowerPoint? Also, what happens when the user has multiple images in Powerpoint from the same excel file but only wants to update just one image?
Sure, you can do something like this: Dim ppt As PowerPoint.Application Dim pres As PowerPoint.Presentation Dim sl As PowerPoint.Slide Dim cl As PowerPoint.CustomLayout Dim sh As PowerPoint.ShapeRange
Set ppt = New PowerPoint.Application Set pres = ppt.Presentations.Add Set cl = pres.SlideMaster.CustomLayouts(7)
Set sl = pres.Slides.AddSlide(1, cl)
Range("A1").CurrentRegion.Copy
Set sh = sl.Shapes.PasteSpecial(DataType:=ppPasteEnhancedMetafile, Link:=msoTrue)
Is it possible to paste in PowerPoint using Source Formatting. I need an editable table to be pasted in Source Formatting. When I use the Paste option I’m getting the vba to paste only as a text table without any formatting
Great video indeed. I became a VBA developer following your videos. My Question: How can we copy the text formatting along with text in Excel and paste the same text with formatting to PPT TextBox? Like we do in excel, copy the text and paste it as range("A1").PasteSpecial(xlPasteAll)
Hi Asad, happy to hear that the videos have helped you! Did you try the RTF paste option in this video (you can find it in the chapters list in the video description or the video play bar). This code: Set sh = sl.Shapes.PasteSpecial(DataType:=ppPasteRTF) Will paste the copied Excel data into a new text box with the text formatting as well. I hope that helps!
Thank you for your reply with the code. It will work with the new Textbox. I have an existing PPT and on slide 3, there is already an existing Textbox. The user wants to update the text in an Excel cell and run the macro and wants the same text with formatting be coppied on the exisiting Textbox (this is the 2nd Textbox on slide3). You response will help me save a project which I have denied after a lot of Google research. Thank you for helping me shape my career. Had your videos not been there, i would have been struggling in my career so far. Thank you from the core of my heart.
@@asadmahboob1123 Hi Asad, you can reference the TextFrame and TextRange properties of an existing shape if you want to paste text into it. Here's some basic sample code that will paste the contents of some Excel cells into an existing text box including the text formatting: Sub PasteIntoExistingTextBox() Dim ppt As PowerPoint.Application Dim pres As PowerPoint.Presentation Dim sl As PowerPoint.Slide Dim sh As PowerPoint.Shape
Set ppt = New PowerPoint.Application Set pres = ppt.Presentations.Open(ThisWorkbook.Path & "\MyPresentation.pptx")
Is it possible to copy Excel data into OneNote using VBA? Like a copy and paste and not printing as a picture to OneNote? If possible, can you please do a video showing this? Thanks!
Hi Sudheer, I don't use OneNote so I have no experience doing this. This Stackoverflow post suggests that it's possible but I haven't tried it out stackoverflow.com/questions/47426666/using-vba-to-paste-excel-picture-into-onenote I hope it helps!
@@WiseOwlTutorials No problem. Thanks for the link, I think I may have stumbled upon how to do it. I’ve learnt so much about VBA thanks to your tutorials! Cheers!
How to add excel charts into power point and different headers for eg. Job role breakdown: 36% of leads were Manager. How to change the % in each header and respective name using vba
Hello sir, Just a question. Is it possible to paste an excel data to powerpoint using vba, without creating a powerpoint slide on vba? In example, a macro enabled worksheet (data source) transferring to a powerpoint slide (receiver) . Which is like the copy from worksheet to worksheet, but this time, worksheet to powerpoint slide? Thanks
Sir as follow up question, if there is an existing power point file, is it possible to have a code in excel that can execute the following in order, 1. Open an existing power point file (already answered) 2. Go to a slide (already answered) 3. Select a table in a slide and delete it. (I hope you can help me with this) 4. Paste a range from the excel file. Thanks in advance
@@pm_ght1747 Hi George, all the objects on a PowerPoint slide belong to the Shapes collection. This page shows how you can check if each object on the slide is a table, I hope it helps! docs.microsoft.com/en-us/office/vba/api/powerpoint.table
Hi Andrew, Is there a way or vba solution to check a cell if its value is a word or not? Just like excel function 'ISNUMBER' or 'ISTEXT'. Sample: WORD TRUE WRDO FALSE RWOD FALSE Thank you.
Hi Dan, there are a few different answers here depending on exactly what you're trying to get. If you want to use an Excel function in VBA you can use the WorksheetFunction object. Here's how to use the IsText function in a subroutine: Sub TestIsText() Dim r As Range For Each r In Range("A1:A8") If WorksheetFunction.IsText(r.Value) Then r.Offset(0, 1).Value = "Is text" Else r.Offset(0, 1).Value = "Is not text" End If Next r End Sub VBA has the IsNumeric and IsDate functions to check for numbers and dates. Here's how you might use those: Sub TestNumberDate() Dim r As Range For Each r In Range("A1:A8") If (Not IsNumeric(r.Value)) And (Not IsDate(r.Value)) Then r.Offset(0, 1).Value = "Is not a number or date" Else r.Offset(0, 1).Value = "Is a number or date" End If Next r End Sub VBA also has function called TypeName to return the data type of a value, here's how you might use it: Sub TestDataType() Dim r As Range For Each r In Range("A1:A8") r.Offset(0, 1).Value = TypeName(r.Value) Next r End Sub From the example data you provided, it looks as though you're trying to check if a word is spelt correctly. To do this you can call the CheckSpelling method like so: Sub TestSpelling() Dim r As Range For Each r In Range("A1:A8") If Application.CheckSpelling(r.Value) Then r.Offset(0, 1).Value = "Is a word" Else r.Offset(0, 1).Value = "Is not a word" End If Next r End Sub I hope one of those is what you need!
@@WiseOwlTutorials I had to go back on my comment because RUclips didn't notify me for your incredible reply and to my surprise I got one :) . Anyway, your vba solutions are brilliant. I already downloaded a list of all English words to solve my question but I will definitely try yours - CheckSpelling method later coz my windows is updating again. The purpose of this is to get the longest word possible from the Letters round in 8 out 10 Cats does Countdown. I have the code for all combinations of letters and all I need is incorporate your code. I really really appreciate this! Be safe and keep sharing your knowledge to us.
@@dan_draft Dan it's my pleasure, I'm glad you found it useful! And that's one of the best uses of VBA I've ever heard of - 8 out of 10 cats does Countdown is brilliant!
please can you share the code to paste a chart from excel to powerpoint as embedded chart, every time i paste its getting linked. please help. thank you
@@WiseOwlTutorials thanks for the reply, the issue was sorted, the problem was with one drive, when I ran the macro after quitting one drive it worked just fine.
@@WiseOwlTutorialsand one more thing. I used the normal Excel shape.copy to copy the chart and in PowerPoint application.commandBars.ExecuteMso "pasteExcelChartSourceFormatting" This is the only way it works. The other paste methods don't give this output. PasteSpecial ppOLEobject method does paste the chart but keeps the chart linked and embedded at the same time, it's really strange. Other paste methods with link set of MSOfalse didn't work for me. Maybe it could be because of the one drive issue. With one drive running even if you manually do the copy paste it wouldn't work, everytime the chart will be pasted as a link. It's because of the file path that if being copied when copying the chart. VBA understands only a normal local path and not the URL path from one drive or SharePoint. That's the reason it worked after quitting one drive.
I have a record of 500 students in excel(name, roll no., Date of training, trainer name) and my job is to prepare their certificates in ppt and save it as PDF. This data from Excel should go particularly in 4 different text boxes in ppt and save it and save it as PDF also. I beleive u can easily do this. Please help... In this video you have placed data of all the cells in 1 text box of ppt but i need help on placing data of all 4 cells into 4 different text box at same time and save the file using the name mentioned in 1 text box that is students name
@@WiseOwlTutorials in this video, details of all the cell are appearing in one single text box... However i need data of 1 cell to go in one text box in ppt
Started viewing... your videos are simply great 👍
Thank you so much Rajesh!
Great video Andrew. Thanks for the in-depth answer to the question posed.
My pleasure Kevin, glad that you enjoyed it and thanks for leaving a comment!
Right first time. It was quite easy to follow. Thanks
Happy it helped, thanks for watching!
Hi Andrew. Thanks for your always interesting and useful videos. Much appreciated :)) Thumbs up!!
Thanks as always Wayne, I appreciate the support!
I was actually looking for something similar to this, you are amazing!
Excellent! Happy to hear you found it useful and thanks for taking the time to leave a comment!
Sir very nice. Excellent video.
Thank you Rohith!
This is so helpful, thank you!
How would you adapt this to import the excel data into an existing powerpoint, rather than creating a new one?
Like this ruclips.net/video/bGiAZZk6LlI/видео.html 😀
I hope it helps, Jack!
thank you for this . I will use those ideas, Andrew
wonder if you use personal workbook to store macros. if so ever noticed the workbook get curropted? I have had this exerpience more than twice in less than a month
how do you organise your macros save each in a module or bunch in a module?
appreciate your feedback. thank you
ll check if you hav more content like this. this is amazong one and I love how you explain things
Hi! I don't use the Personal Macro Workbook, but a couple of us at Wise Owl have experienced corruption with VBA files in the past few months. It's certainly encouraged me to keep more regular backups!
How I organise procedures and modules depends on the complexity of the project. It would be very rare to create a separate module for each macro!
@@WiseOwlTutorials thank you for your input
Any resources you recommend how to organize code snippets? Would have general purpose ones and definitely longer ones for a particular automation
How do you get hold of your macros if you dont use personal work book? Curious as i am new to this
Will ensure backing up then
@@ahmed007Jaber Hi! Honestly I don't tend to store snippets for VBA these days. Back when I was getting started I would tend to create a separate file for related techniques just for reference but I'm not claiming that's the most efficient way to do it 😀
Thank you for this video, can u please share a video or code for copying different range of data each time to different slides?
You're welcome Azhar! I added a video recently to answer a similar question to create a new slide for each row of an Excel list ruclips.net/video/WOKKN03NVjs/видео.html
It's only available for channel members with the early-access perk at the moment.
Thanks a lot Wise Owl
You're very welcome, thanks for watching!
Spectacular video and examples! Thank you so much for sharing!
Could you tell us what would happen if you emailed out a power point presentation with a link to an excel document?
I would assume it would fail to open the excel document but I always like to verify with an expert.
Thanks Eric, glad you enjoyed it!
When the user opens the PowerPresentation it will ask if they would like to update links. If they choose to update the links PowerPoint will tell them that the file can't be found. The user will still see a static image of the Excel document on the PowerPoint slide. Double-clicking the image will indeed fail with a message informing the user that the file could not be found.
I hope that helps and thanks for the question!
@@WiseOwlTutorials thank you for the answer. I have been a subscriber / viewer of your channel for years and whenever one of my colleges has a vba question I always send them to your channel. Thank you much for all you do!
@@EricHartwigExcelConsulting You're very welcome Eric! Thank you so much for the recommendations and your support!
Great tutorial. I would like to know more about slidemaster/custom layout. Like how to paste excel value into PowerPoint using a specific theme?
Thanks! I think that you might find this useful docs.microsoft.com/en-us/office/vba/api/powerpoint.presentation.applytheme
I hope it helps!
This Video is very helpful thank you for uploading this video.
How to tranfer alredy inserted image in excel sheet to ppt.
Hi! You can just apply the Copy method to the Shape object in Excel and paste it into PowerPoint, like so:
Sub Copy_Pic_To_PowerPoint()
Dim ppt As New PowerPoint.Application
Dim pres As PowerPoint.Presentation
Dim sld As PowerPoint.Slide
Set pres = ppt.Presentations.Add
Set sld = pres.Slides.Add(1, ppLayoutBlank)
Sheet1.Shapes("Picture 2").Copy
sld.Shapes.Paste
End Sub
You can find the name of the picture by selecting it and looking in the Name Box in the top left corner of the Excel window.
I hope it helps!
Hi Andrew, what happens if the user changes the name of the excel file or versions up the excel file? Does the PowerPoint link automatically update and change the file path too or does it mess up the PowerPoint? Also, what happens when the user has multiple images in Powerpoint from the same excel file but only wants to update just one image?
Hi again Mr. Owl! Can I ask if there's a way to paste the excel data as picture that will cover the whole slide? Thank you in advance!!
Sure, you can do something like this:
Dim ppt As PowerPoint.Application
Dim pres As PowerPoint.Presentation
Dim sl As PowerPoint.Slide
Dim cl As PowerPoint.CustomLayout
Dim sh As PowerPoint.ShapeRange
Set ppt = New PowerPoint.Application
Set pres = ppt.Presentations.Add
Set cl = pres.SlideMaster.CustomLayouts(7)
Set sl = pres.Slides.AddSlide(1, cl)
Range("A1").CurrentRegion.Copy
Set sh = sl.Shapes.PasteSpecial(DataType:=ppPasteEnhancedMetafile, Link:=msoTrue)
sh(1).Top = 0
sh(1).Left = 0
sh(1).Width = pres.PageSetup.SlideWidth
Is it possible to paste in PowerPoint using Source Formatting. I need an editable table to be pasted in Source Formatting. When I use the Paste option I’m getting the vba to paste only as a text table without any formatting
Great video. Thanks. Could you share how to send file attachment to Telegram bot by VBA. Thank you, Andrew
Thank you Villa!
I'm sorry but I'm not familiar with Telegram so I can't give you any advice on this topic.
@@WiseOwlTutorials Understood. I have learnt a lots from your video tutorials. Once again thanks very much, Andrew
Great video indeed. I became a VBA developer following your videos.
My Question: How can we copy the text formatting along with text in Excel and paste the same text with formatting to PPT TextBox?
Like we do in excel, copy the text and paste it as range("A1").PasteSpecial(xlPasteAll)
Hi Asad, happy to hear that the videos have helped you!
Did you try the RTF paste option in this video (you can find it in the chapters list in the video description or the video play bar).
This code:
Set sh = sl.Shapes.PasteSpecial(DataType:=ppPasteRTF)
Will paste the copied Excel data into a new text box with the text formatting as well.
I hope that helps!
Thank you for your reply with the code. It will work with the new Textbox. I have an existing PPT and on slide 3, there is already an existing Textbox. The user wants to update the text in an Excel cell and run the macro and wants the same text with formatting be coppied on the exisiting Textbox (this is the 2nd Textbox on slide3).
You response will help me save a project which I have denied after a lot of Google research.
Thank you for helping me shape my career. Had your videos not been there, i would have been struggling in my career so far. Thank you from the core of my heart.
@@asadmahboob1123 Hi Asad, you can reference the TextFrame and TextRange properties of an existing shape if you want to paste text into it. Here's some basic sample code that will paste the contents of some Excel cells into an existing text box including the text formatting:
Sub PasteIntoExistingTextBox()
Dim ppt As PowerPoint.Application
Dim pres As PowerPoint.Presentation
Dim sl As PowerPoint.Slide
Dim sh As PowerPoint.Shape
Set ppt = New PowerPoint.Application
Set pres = ppt.Presentations.Open(ThisWorkbook.Path & "\MyPresentation.pptx")
Set sl = pres.Slides(3)
Set sh = sl.Shapes(2)
Range("A1").CurrentRegion.Copy
sh.TextFrame2.TextRange.Paste
End Sub
Hope it helps!
Dear Andrew, you are awesome!
You code fixed the issue...
So nice of you...
@@asadmahboob1123 You're very welcome Asad!
I love your videos
But I'm having trouble installing SSIS. I'm always told to close some Microsoft services but I can't locate them too
Thanks P Jay! Sorry but I don't know how to solve your SSIS installation problem so I can't offer any help on that.
Is it possible to copy Excel data into OneNote using VBA? Like a copy and paste and not printing as a picture to OneNote? If possible, can you please do a video showing this? Thanks!
Nvm, I only need to print the data to OneNote. If you can do a video on this I would much appreciate it. Thanks!
Hi Sudheer, I don't use OneNote so I have no experience doing this. This Stackoverflow post suggests that it's possible but I haven't tried it out stackoverflow.com/questions/47426666/using-vba-to-paste-excel-picture-into-onenote
I hope it helps!
@@WiseOwlTutorials No problem. Thanks for the link, I think I may have stumbled upon how to do it. I’ve learnt so much about VBA thanks to your tutorials! Cheers!
@@sudheerkupchand9228 That's great, it looked unnecessarily complicated from the couple of links I looked at. I hope you found a simpler solution!
How to add excel charts into power point and different headers for eg. Job role breakdown: 36% of leads were Manager. How to change the % in each header and respective name using vba
Hello sir,
Just a question.
Is it possible to paste an excel data to powerpoint using vba, without creating a powerpoint slide on vba? In example, a macro enabled worksheet (data source) transferring to a powerpoint slide (receiver) . Which is like the copy from worksheet to worksheet, but this time, worksheet to powerpoint slide?
Thanks
Hi George! Yes, you can copy data into an existing slide, this video shows a similar example which might help ruclips.net/video/bGiAZZk6LlI/видео.html
@@WiseOwlTutorials thanks
Sir as follow up question, if there is an existing power point file, is it possible to have a code in excel that can execute the following in order,
1. Open an existing power point file (already answered)
2. Go to a slide (already answered)
3. Select a table in a slide and delete it. (I hope you can help me with this)
4. Paste a range from the excel file.
Thanks in advance
@@pm_ght1747 Hi George, all the objects on a PowerPoint slide belong to the Shapes collection. This page shows how you can check if each object on the slide is a table, I hope it helps! docs.microsoft.com/en-us/office/vba/api/powerpoint.table
Thank you sir, I will read this.
Hi Andrew, Is there a way or vba solution to check a cell if its value is a word or not? Just like excel function 'ISNUMBER' or 'ISTEXT'.
Sample:
WORD TRUE
WRDO FALSE
RWOD FALSE
Thank you.
Hi Dan, there are a few different answers here depending on exactly what you're trying to get.
If you want to use an Excel function in VBA you can use the WorksheetFunction object. Here's how to use the IsText function in a subroutine:
Sub TestIsText()
Dim r As Range
For Each r In Range("A1:A8")
If WorksheetFunction.IsText(r.Value) Then
r.Offset(0, 1).Value = "Is text"
Else
r.Offset(0, 1).Value = "Is not text"
End If
Next r
End Sub
VBA has the IsNumeric and IsDate functions to check for numbers and dates. Here's how you might use those:
Sub TestNumberDate()
Dim r As Range
For Each r In Range("A1:A8")
If (Not IsNumeric(r.Value)) And (Not IsDate(r.Value)) Then
r.Offset(0, 1).Value = "Is not a number or date"
Else
r.Offset(0, 1).Value = "Is a number or date"
End If
Next r
End Sub
VBA also has function called TypeName to return the data type of a value, here's how you might use it:
Sub TestDataType()
Dim r As Range
For Each r In Range("A1:A8")
r.Offset(0, 1).Value = TypeName(r.Value)
Next r
End Sub
From the example data you provided, it looks as though you're trying to check if a word is spelt correctly. To do this you can call the CheckSpelling method like so:
Sub TestSpelling()
Dim r As Range
For Each r In Range("A1:A8")
If Application.CheckSpelling(r.Value) Then
r.Offset(0, 1).Value = "Is a word"
Else
r.Offset(0, 1).Value = "Is not a word"
End If
Next r
End Sub
I hope one of those is what you need!
@@WiseOwlTutorials I had to go back on my comment because RUclips didn't notify me for your incredible reply and to my surprise I got one :) .
Anyway, your vba solutions are brilliant. I already downloaded a list of all English words to solve my question but I will definitely try yours - CheckSpelling method later coz my windows is updating again.
The purpose of this is to get the longest word possible from the Letters round in 8 out 10 Cats does Countdown. I have the code for all combinations of letters and all I need is incorporate your code.
I really really appreciate this! Be safe and keep sharing your knowledge to us.
@@dan_draft Dan it's my pleasure, I'm glad you found it useful! And that's one of the best uses of VBA I've ever heard of - 8 out of 10 cats does Countdown is brilliant!
please can you share the code to paste a chart from excel to powerpoint as embedded chart, every time i paste its getting linked. please help. thank you
Hi! Can you use the PasteSpecial method and set the Link parameter to False? learn.microsoft.com/en-us/office/vba/api/powerpoint.shapes.pastespecial
@@WiseOwlTutorials thanks for the reply, the issue was sorted, the problem was with one drive, when I ran the macro after quitting one drive it worked just fine.
@@ganeshs1360 Interesting, thanks for sharing!
@@WiseOwlTutorialsand one more thing. I used the normal Excel shape.copy to copy the chart and in PowerPoint application.commandBars.ExecuteMso "pasteExcelChartSourceFormatting"
This is the only way it works. The other paste methods don't give this output. PasteSpecial ppOLEobject method does paste the chart but keeps the chart linked and embedded at the same time, it's really strange. Other paste methods with link set of MSOfalse didn't work for me. Maybe it could be because of the one drive issue. With one drive running even if you manually do the copy paste it wouldn't work, everytime the chart will be pasted as a link. It's because of the file path that if being copied when copying the chart. VBA understands only a normal local path and not the URL path from one drive or SharePoint. That's the reason it worked after quitting one drive.
I have a record of 500 students in excel(name, roll no., Date of training, trainer name) and my job is to prepare their certificates in ppt and save it as PDF. This data from Excel should go particularly in 4 different text boxes in ppt and save it and save it as PDF also. I beleive u can easily do this. Please help... In this video you have placed data of all the cells in 1 text box of ppt but i need help on placing data of all 4 cells into 4 different text box at same time and save the file using the name mentioned in 1 text box that is students name
Hi there, you might find this video helpful ruclips.net/video/bGiAZZk6LlI/видео.html
@@WiseOwlTutorials in this video, details of all the cell are appearing in one single text box... However i need data of 1 cell to go in one text box in ppt
Any update on this sir?