Hi Andrew, your tutorial video is fantastic. What like most about your video is the care that you take to touch upon nuances of different approaches. Being a fairly proficient in VBA, I recommend this to anyone who is interested in learning VBA. Good job posting such good quality video freely on tube when many people are trying to make money out of low quality trainings. Hats off.
Solid work Andrew. I attended a Advanced VBA course years ago, and they never showed you how to work with Powerpoint. This will be really helpful when preparing statistics for presentations. I can essentially automate the whole process. Great stuff
Ths is probably one of the best tutorials that I found on youtube. You show several approaches to get the same result, which is very good. I helped me a lot, since I am a noob in excel VBA. Congratulations for the video you posted. Amazing!
This tutorial is perfect! Thank you very much. As an amateur, It has enabled me to really take my projects to the next level. It has really good explanations and auxiliary codes that gives viewers a comprehensive understanding of what they are doing. It also combines a little bit of everything that you need to know in order to get started using Excel and PowerPoint VBA. For example, how to set up a new ppt slide show from Excel, adding and editing slides & shapes in PowerPoint through Excel. Compatibility issues between older and newer versions, early binding vs. late binding. It's great. I'm going to send it to my business partner. Thanks again.
Hi Andrew,Just want to sincerely say thank you for this excellent and brilliant tutorial ! I'm a beginner with VBA and currently working on a project to automate movement of data from Excel to PP and this video has truly given me the knowledge and initial building blocks I'll need to complete the project. I truly thank you for sharing your expert knowledge! Also thank you for the excellent explanations along with showing how to write the code with the sequences you provided, your delivery in terms of speed and overall rapport with the audience is excellent as well - I was able to follow along with you perfectly - I wish I could have you as an instructor lol. Overall, thank you very very much for this tutorial as it has been extremely helpful to me.Questions: I have successfully utilized your code (using Excel 2013 and PP 2013) for the primary lessons you have provided. However, I was unsuccessful in utilizing the code to copy a chart over to Excel. My challenge now is to modify the code you have provided to fit my needs ... 1. I have more than 1 set of Excel ranges that I need to copy and paste to a single slide. Please kindly advise how to do that ? Your video only shows how to copy and paste 1 range of excel data to 1 PP slide.2. I was unsuccessful in copying a chart over to PP. In the area in VB where it says "VBAProject (Book1)" (not sure what that is called) the "Chart1" is not showing up there ... Is that the reason why I am unable to copy my chart over to PP ?Thank you once again for this brilliant tutorial - it is truly appreciated !
Hello Andrew, If I do the following set then it works like a charm all the time:. I put in couple of Sleep functions after copying and before pasting. ppSlide.Select Range("A1").CurrentRegion.Copy Sleep (50) ppSlide.Select Sleep (50) ppSlide.Shapes.Paste.Select
Holy Cow Batman! I hope you are being paid boxes and boxes of money. You are awesome!! Best video tutorials I have ever seen. Keep up the great work!!!
I love your videos. I have been watching them daily for the last few weeks, and also for a while a couple of months ago. I skipped this one after watching half of it because it just felt like it was about formatting and compatibility. But i guess that is really all that power point is anyway? I'm ready for Outlook!
Andrew Great Video, I did have issues with the pasting but NBAH NBAHOB below gave code ppApp.Window(1).View.Paste and it worked, this is an issue with 2010 users. Great Job and great teaching. Thank you
Hi there, Thanks for an insightful upload. I found that to get around the paste problem in 2010-version of PP we can use this: ppApp.Windows(1).View.Paste Works like a charm.
thanks to your tutorials, I can transfer all my excel dashboard page to PP slides to which I can present on my crucial meettings .... could you pls also show us how to create an event that you can click a shape and select it automaticly in a shape groupitem
I loved this video absolutely.. just broke my heart when i realised at a later point that i would not be able to use paste for powerpoint 2010.. nonetheless extremely helpful..
Great video thanks for sharing. I have a question how do you copy ws.Range("A1:G2").copy from sheet1 in Excel to PPT in an existing table. I am able to paste one cell at a time but how do you paste a range. For example I am able to paste ws.range("A1").copy to tbl.cell(3,2).shape.textframe.textrange.paste but how do you copy a range from excel to a table range in PPT.
Andrew, Your videos are great. I just wanted to say "Thank You". Is there any way to write a vba code to refresh power pivot without opening the Powepivot? If there is one, I would really appreciate a sample code or a video would be great.
Hi Andrew...I've been following along with your tutorials and must say that yours are the best that I've seen. Great accent by the way! I'm using Office 2010 and with this video I'm having a problem of where the data is pasted from Excel into PowerPoint. Everything before this works fine. When I use the line of 'ppSlide.Shapes.Paste' I get an error on this line. It says "Shapes (unknown member): Invalid request, Clipboard is empty or contains data which may not be pasted here." I can look at the data, (a few rows and columns of numbers) from Excel and see that it's selected and I can do a manual paste in Excel and in PowerPoint. I did some testing using the PasteSpecial and it works with ppPasteBitmap, ppEnhancedMetalfile and ppPasteMetaFilePicture. With all the others, it errors out with the message of "Shapes (unknown member): invalid request, the specified data type is unavailable." I thought that maybe the data had to be in a picture format such as jpeg, png, etc. before it would work, but then I was confused to how the ppPasteBitMap would work. Could you enlighten me to what the issue may be? Thanks Andrew for any insight that you may have! Update: I read over the comments after posting this and it seems that Office 2010 has an issue with the paste method in PowerPoint and saw possible fixes. I didn't want to waste your time Andrew and hope you have a great day!
+WiseOwlTutorials Thank you very much Andrew for your quick reply which is a lot to be said with other channels on RUclips where you never hear a reply. Again, you've really done a great job with your tutorials and I know it takes a lot of time to put them together. Just wanted to let you know that it's very nice of you and a lot of folks can really learn VBA from you. Have a super GREAT day!!!
can you set up where the data for each presentation is being pulled from. for example, slide 1 data is from a1 to d6, and slide 2 data is from a10 to d20, etc.
Hi Andrew This video is really nice for me. I got one problem when I try to use the template. 1.How do I using the template in the head page (First page), since my first page style is different then other page. 2.All of the other page can be generated by template succefully. Simply to say, Main page background are different than the others.
Very nice video, You have really given a long but comprehensive account. Thanks a lot. I am sure you have no problem with your code. However, it was very tough to understand what you said initially. Your accent is very tough. Hire someone who can talk slower than you. That makes us understand more. Thanks a lot again, WOT! All the best...
+WiseOwlTutorials Just for the record Andrew, you're doing a great job and your accent is great. Actually keeps me inspired to hear you talk. Keep up the good work and don't worry about nay sayers!
thank you for this. this is exactly what I have been looking for. wonder if you could help out with the below how to center horizontally and vertically the selected table content using the selection and not going through cell by cell I noticed when pasting a table it adds leading spaces which mess up the allignments
I'm not sure why you're seeing extra leading spaces, I don't see the same thing happening for me. I think it's easiest to change the alignment in Excel before copying into PowerPoint With Range("A1:D6") .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter End With But if that's not working, you can loop through the cells in the PowerPoint table after pasting it into the slide, 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 Dim tbl As PowerPoint.Table Dim rw As PowerPoint.Row Dim c As PowerPoint.Cell
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.Paste Set tbl = sh.Table
For Each rw In tbl.Rows For Each c In rw.Cells With c.Shape.TextFrame2 .VerticalAnchor = msoAnchorMiddle .HorizontalAnchor = msoAnchorCenter End With Next c Next rw
@@WiseOwlTutorials Hi thank you for this mate. I actually thought of hte same approaches. Excel has numbers and text with perfect formatting. when pasted into PowerPoint i keep getting those leading spaces and reck the alignment. So I fix the issue by looping through table cells in PowerPoint but given I am doing a large number of export it is time-consuming. I am searching the topic. Until now I haven't figured out a way at least to do the alignment to the table in PowerPoint but by selecting the whole table and do the formatting as you would manually. Insofar what worked for this is to change cell by cell
@@ahmed007Jaber It's so strange that you get these leading spaces, I've never seen that happen before! At least you have a workaround but let me know if you find a better solution!
Hi Andrew, Once again, thank you so much for this excellent tutorial, one request to you, could you please make a tutorial for how can we modify existing created PowerPoint presentations, like how can we take reference of already created charts in PowerPoint, textbox, bullets shapes or any other texts, and alter accordingly, like I have to paste certain data in already created presentaion, but in same font size, same font color, same height, width, that already in the presentation, means just alter the text as a value in some part. I hope, I clear my point as per my ability. Thanks Kashif
Why don't you break it into functions? It seems it is in one big function. Any engineering reason for that or just for keeping it simple? I never know when to break up code into functions.
wow. best vba presentation i have ever come across so far. Thanks a lot! I have a question though. If I want the macro to copy ranges from different sheets on the same excel workbook, how would i do that? Also if i want the macro to recognize empty columns and rows and copy on the non-empty ones, how would i do that? Thank you!
Thanks for this tutorial ... I am currently working on similar task (certificate names pasted in a single textbox in powerpoint, one certificate on each slide, one name in the same textbox on that slide), however the textbox has a combination of "salutation" (Mr for Male and Ms or Mrs for Female etc) for and "name" with a different text size, colour and font type (in the same textbox). Please provide some help on how I can do this. I am a beginner in this however looking at the VBA code and explanation you/others on web provided I was not too bad in understanding and debugging. Thanks in advance - Hari
Hi Andrew, Your videos have been vary helpful, but i need to know one more thing. How would i copy text/numbers from a cell in excel to a text box in power point. A lot of presentations i make are for upper level managment and they change data all the time. I don't mind updating the data in excel, but then i have to go and completely redo the powerpoint too. Thank you for all your help!
Hi Wiseowl, your tutorials are awesome !! Thanks for helping millions of people!!! when ever I paste my grouped pictures and charts into power point using excel vba the name of the object is getting changed. Hence I am unable to do the formatting, Is there any way to name the group in powerpoint using excel vba to refer them in excel coding ?
Hi Andrew, Thank you so much for this video! You made it really easy to follow along! :) I have a question in lieu with the range that we select from the excel sheet. Instead of using it as Range ("A1").Currentregion, is it possible that I could refer it to a named Range? If so do I define the range early on?
Hi Andrew, Awesome! Another video added in my collection! I’m learning VBA thanks to you! Since video 5 :-). May I ask you how could I add a loop into your code so whenever I run it a new slide is created into the same presentation? Aline that because I have a pivot chart and when changing the slicers I would like to create a new slide. Cheers Gilly
Every time I need some information on VBA I find you have a video on it! And a very helpful one as always. Would you consider returning to and adding to this particular topic at any point?
just one thing. I noticed you have a "chart1" object within your "Microsoft Excel Objects". I have a chart but it is not shown as an object. I only have "sheets" listed under my "Microsoft Excel objects". So i am having issues transferring the chart to powerpoint.
24.20 when I tried doing the same, the data gets pasted to PowerPoint but the format does not...I meant if I color any cell...tht color does not come when I run the code. What am I missing? Is there way where I could paste it as a table and not shapes?
One of the best tutorials I have ever seen: Thanks and Chapeau! I was trying to copy a linked picture from Excel into PP, but I failed to do so. Is there something specific with pictures? Besides I am running version 16.0; are there some specific issues I have to know? But again...I have never seen such an informative and usefull tutorial. People should know
Hi, I found the following worked for me rather than pasting as embedded object from excel into powerpoint try something like, worksheets("Sheet1").Range("A1:F30").CopyPicture (the key being CopyPicture) and when pasting into powerpoint a normal paste will work fine - not paste special. ppSlide.Shapes.Paste (my example was copying a chart into powerpoint as a picture so it could not be manipulated by end user of presentation)
I am getting an error "REMOTE Procedure call failed". How to resolve it. I want to create a PPT of 60 slides with various tables,charts copied from excel to PPT, with this error i got struck, can you please help me with this
At 1:07:10 your MS PowerPoint crashed. I faced the same problem as shown with office 2013 and I have single version of office installed. Found that the Powerpoint is crashing because we are trying to close the presentation before the save process has concluded. Hence the crash. We need to place a delay between save and close processes in order to prevent the crash. We can call the below sub between save and close process. Sub Wait(tSecs As Single) Dim sngSec As Single sngSec = Timer + tSecs Do While Timer < sngSec DoEvents Loop End Sub Source: www.nullskull.com/q/10216453/excel-vba-code-leads-to-powerpoint-crash--code-check-f8-ok-f5-nok.aspx
Hello, Andrew! Nice work as always, like everything you do. I have a question: I have several different ppt files (300+) with one or two slides each one. How can a do a VBA routine to merge all slides in a unique file? After that, is there a way to rename each slide with the original file name? Thanks for all your help.
Hi Marcelo! I've added a video showing how to do this ruclips.net/video/UHE7p3ahtF4/видео.html It's a members-only video at the moment but the basic premise involves looping through the PowerPoint files in a folder and using the InsertFromFile method to copy all the slides into the master presentation.
Hi Koushik, here's one way to do it. The code assumes you have text in cells A1:A5 on Sheet1 and that you want to create a new presentation rather than refer to an existing one: Sub CreatePresAndCopyTable() Dim ppt As PowerPoint.Application Dim pres As PowerPoint.Presentation Dim cl As PowerPoint.CustomLayout Dim sl As PowerPoint.Slide Dim sh As PowerPoint.ShapeRange
'Create a new presentation Set ppt = New PowerPoint.Application Set pres = ppt.Presentations.Add
'Reference a CustomLayout and create a slide using it Set cl = pres.SlideMaster.CustomLayouts(7) 'a blank slide Set sl = pres.Slides.AddSlide(1, cl)
Sheet1.Range("A1:D6").Copy
'Various techniques to paste Set sh = sl.Shapes.Paste 'paste as a PowerPoint table 'Set sh = sl.Shapes.PasteSpecial(ppPasteText) 'text 'Set sh = sl.Shapes.PasteSpecial(ppPasteRTF) 'rich text format 'Set sh = sl.Shapes.PasteSpecial(ppPasteOLEObject) 'embedded Excel object
'Set sh = sl.Shapes.PasteSpecial(ppPasteMetafilePicture) '16 bit image 'Set sh = sl.Shapes.PasteSpecial(ppPasteEnhancedMetafile) '32 bit image
'Move the shape to the top left corner of the slide sh(1).Top = 0 sh(1).Left = 0
Hi WiseOwl, This is an excellent video. I tried to reproduce this exact code. But I keep getting an error message when I do the Paste method ppSlide.Shapes.Paste.Select The message reads as follows: Runtime Error -214188160(80048240) Shapes (unknown member): Invalid Request. Clipboard is empty or contains data which may not be pasted here. Could please help me resolve this issue.
Hi Andrew, Love the tutorial! Really easy to follow! Only problem I'm having is when when I add in the line of code to specify the width when I then run the code the shape or chart is completely invisible. At first I thought it was not there but it is in fact still there but incredible narrow. This happens no matter what width I enter 600, 1000 or SlideWidth. Do you know what could be causing this?
HI Abdrew, i just learning the PPT conversion from your video, i just creating what you showed in Video same way i creating the file in 2010 but when i run the file its showing Run time error - its showing when i create the second slide with excel ub CreateNewPresentation() Dim ppApp As PowerPoint.Application Dim ppPres As PowerPoint.Presentation Dim ppSlide As PowerPoint.Slide Set ppApp = New PowerPoint.Application ppApp.Visible = True ppApp.Activate Set ppPres = ppApp.Presentations.Add Set ppSlide = ppPres.Slides.Add(1, ppLayoutTitle) ppSlide.Shapes(1).TextFrame.TextRange = "GROUP CORPORATE" ppSlide.Shapes(2).TextFrame.TextRange = "GBC 2016 - 17" Set ppSlide = ppPres.Slides.Add(2, ppLayoutBlank) ppSlide.Select Range("A1").CurrentRegion.Copy ppSlide.Shapes.Paste End Sub can you help me
Hiiii , thanks for the video, super helpful , I have one Question , Q: How to change Slide size, Like I Want it to be A4 Size , or A3 Size, or other custom size ? hope you answer my question
Hi guys, in this tutorial we can learn how to create a new Powerpoint presentation is there a tutorial which shows how one can modify an EXISTING Powerpoint presentation? e.g. I have a Presentation and I just have to change the current date, stock price, name (of the firm, the presenter, ...), etc on multiply slides, so it would be super helpful to build a macro, where I can type in this data once and it will paste this data on certain slides on an existing Powerpoint presentation thank you :)
Hi, we don't have a video on this but it isn't too difficult to do - the code below assumes you have a presentation saved in the same folder as the Excel workbook: Sub ModifyPresentation() Dim ppt As PowerPoint.Application Dim pres As PowerPoint.Presentation Dim sld As PowerPoint.Slide Dim shp As PowerPoint.Shape Set ppt = New PowerPoint.Application Set pres = ppt.Presentations.Open(ThisWorkbook.Path & "\MyPresentation.pptx") Set sld = pres.Slides(1) Set shp = sld.Shapes(2) shp.TextFrame2.TextRange.Text = "Created on " & Format(Date, "dddd d mmmm yyyy") pres.Save End Sub I hope that helps!
@@WiseOwlTutorials hey WiseOwl I found another tutorial on youtube and have now found a solution to my problem Thank you very much for your fast reply and the code, I'll definitely try yours out as well Keep up the good work, thank you for your efforts and your channel :)
Do you suppose if I had a custom template I wanted to use in the code, that I could embed it in the Excel file and bring it out at run time? Perhaps a few for different office versions would do. However I would need it to not be installed on the user's computer, only accessible in excel or PowerPoint (whatever works best).
Hi, Andrew! Great work, thanks! Question regarding ApplyTemplate method: It perfectly works when using a Microsoft template. However, it throws a run-time error if trying to use custom template. Can you please suggest any workaround for that? Path is set correctly and .potx file exists. Error message: "Presentation (unknown member): Invalid request. PowerPoint could not open the file"
Hi Alisher! I believe the ApplyTemplate method only applies to Microsoft templates docs.microsoft.com/en-us/office/vba/api/powerpoint.presentation.applytemplate I believe (although haven't tried it) that you can use the ApplyTheme method to apply custom themes or templates docs.microsoft.com/en-us/office/vba/api/powerpoint.presentation.applytheme I hope it helps!
Hi Andrew, I am a really big fan of your videos and they have helped me more than I can state in my work! I do have one question though. I am building a tool for a colleague in which I need to update her charts in PPT from data in excel. The charts are not linked (as in they do not automatically update when you change the excel data), but rather they are the type where you have to right click on the data on the graph in PPT (an excel spread sheet will then pop up stating Linked data - "File Path"), so I suppose they are embedded. How is it that from an excel vba tool, I can go into this embedded excel file in the PPT Chart to make edits? Any help would really be appreciated! Thanks Neil
Great videos, Andrew. I was wondering if you could help me with an issue I'm having: in PowerPoint, is it possible to select all shapes within a specific boundary (top, left, height, width)? Basically the same function as when one selects an area with the cursor to activate all shapes within that boundary, but through VBA. Trying to combine this with a loop, but I'm having some issues. Thanks a lot!
Thank you for taking the time to answer, Andrew, I really appreciate it. I get no error when I use this code, but no shapes are selected after I activate. Also I am unsure of how to manipulate the shape properties through this loop. I tried to add "sh.TextFrame.TextRange.Font.Color = RGB(11, 52, 104)" I also experimented with using a with statement, but neither seems to not work. Any suggestions?
I have a query I'm making a multi choice question game for my kids (I teach). what I have is a slideshow with a question box and 4 possible answers I want to run a macro so when I press a command button it generates a new question and answers the questions and answers are in an excel sheet I have all the slideshow setup linking to different slides but I don't know how to get the questions from the excel sheet to the pp slides any help is much appreciated alan
i have it all finished except the code to change the questions and answers. What i want by the end is a form attached to a sheet to input the questions and answers and a button to add the new questions and answers into the boxes.
Hello Andrew, please may I know how to run the VBA without making Powerpoint visible? I have commented out below codes but it still doesn't work. 'ppApp.Visible = True 'ppApp.Activate Seems like your commands with "Select" or "Active" will force Powerpoint become visible, and this is not something I want so how to amend your codes to make it still able to run while making Powerpoint invisible?
Hi WiseOwl, Great Video! I have a question please. The powerpoints that I create using excel vba always takes a long time to open and the data of the charts are also missing. When I try to edit with excel in ppt, it opens up an excel file that is completely blank of data. What is interesting though, is that when the ppt is opened from another computer, the powerpoint is completely normal and fine with all its data points. Do you know how I could fix this?
Hi Andrew, Your video is very interesting and very easy to use but I need your help for a vba problem: I have an Excel with a data board (4 columns like A,B,C, Dand lot of rows).I would like to export some information to powerpoint with a VBA process like that : Powerpoint slide 1 : rows 1,2 and 3Powerpoint slide 2: rows 1 and 4,5,6Powerpoint slide 3: rows 1 and 7,8,9.....And if I want to select diferents colums ? Powerpoint slide1 : rows 1,2 and 3 for columns A,B and D?Can you explain me, what kind of code can I use for this? Thanks a lot for your help.B
yes, for exemple i have a details of contract in access (Reference of the contract, title, duration, etc and i want to copy these informations for one contract in the pp.
Hi Andrew, Appreciate for the video, very useful I have a question, In your code part: Set ppSlide = ppPres.Slides.Add(2, pplayoutblank) ppslide.select Range("A1).CurrentRegion.Copy ppSlide.Shapes.Paste doesnt work for me. I work on excel 2007, and when im typing ppSlide.Shapes.Paste it doesnt pastes at all in my code. instead of this, for me works: Set ppSlide1 = ppPres.Slides.Add(2, ppLayoutBlank) ThisWorkbook.Sheets(1).Range("C2:AD32").Copy ppSlide1.Shapes.PasteSpecial ppPasteOLEObject I want to paste as usual tab, not an OLEObject Please help!
***** , Thank you for your respond. It works! Now, Im facing font size problem Somehow I cant change font size and name the way u did Example: ppSlide1.shapes(1).TextFrame.TextRange.Font.Name = "Calibri" ppSlide1.shapes(1).TextFrame.TextRange.Font.Size = 30 This method work for titles, not for pasted text as tab But I found difficult way to change font size and name My code: ActiveSheet.Range("A2:A28").Copy ppSlide2.Select ppApp.ActiveWindow.View.Paste ppSlide2.shapes(1).Height = 450 ppSlide2.shapes(1).Width = 170 ppSlide2.shapes(1).Left = 20 ppSlide2.shapes(1).Top = 20 Dim i As Integer For i = 1 To 25 ppSlide2.Select ppSlide2.shapes(1).Table.Cell(i, 1).Shape.TextFrame.TextRange.Font.Size = 11 ppSlide2.shapes(1).Table.Cell(i, 1).Shape.TextFrame.TextRange.Font.Name = "Calibri" Next i The problem is it takes too much time, changing cell one by one. Do we have other methods to change font size for pasted tab quickly? Thank you!
***** , yes i've tried to change font size in the excel, but when it pastes to powerpoint slide,it changes font size depending on table size, as u see below ppSlide2.shapes(1).Height = 450 ppSlide2.shapes(1).Width = 170 ppSlide2.shapes(1).Left = 20 ppSlide2.shapes(1).Top = 20 Im not stuck with iterating through cells, it works, but it takes about 30-40 sec. to finish font size changing, and i was looking for faster way to accomplish it. ok, anyway it works and im ok with it! Thank you for providing me necessary information, appreciate! Best!
Hi, I dont know what is the reason, but I am not even able to make the powerpoint application visible. It is giving error at line - pptApp.Visible = True The error message is Run-time error '-2147417856 (80010100)': Automation error System call failed. I am using 2013 Excel and PowerPoint. I have also checked 'Microsoft Powerpoint Object Library' from the Excel VBA window, Tools -> References. Please let me know if I need to some other changes in the settings, etc. Thanks, Manish
Hi Andrew i too got error "shapes (unknown member) : invalid request. clipboard is empty or contains data which may not be pasted here." by seeing comments here i tried Range("A1").CurrentRegion.CopyPicture ppSlide.Shapes.Paste it works for me why .Copy Is not working and .CopyPicture is working i also tried Range("A1").CurrentRegion.CopyPicture ppSlide.Shapes.PasteSpecial ppPasteOLEObject this is not working pls clarify
Hey, I'm having problems when I try to copy the chart from the excel to powerpoint. I tried with the: Worksheets("Sheet1").ChartObjects("Chart1").Chart.ChartArea.Copy when the chart is in the same sheet as the data it says Run-time error -214724809 (80070057) i tried: Chart1.ChartArea.Copy When the chart is in Chart1 sheet it says Run-time error 445 Do you have a solution for any of this problems? if you have a solution for the first better. Sorry if I sound weird but English is not my native language.
when running the code ppslide.Shapes.Paste, it doesn't work and an error occurs saying : shapes (unknown member) : invalid request. clipboard is empty or contains data which may not be pasted here. how to fix this error.
Hi, this is really fantastic to learn Excel VBA. I love it :D. Regarding this run-time error, it still doesn't work by using ppSlides.Shapes.PasteSpecial ppPasteOLEObject. Is there other workaround for me to take?
Many thanks for quick reply :-) Sorry, just realized that you mentioned this error with "ppSlide.Shapes.Paste" on Excel 2010. I was running this VBA code on that version. Here are test results. 1. Range("A1").CurrentRegion.Copy ppSlide.Shapes.Paste [Test Result] runtime error. Shapes(unknown member): Invalid request. Clipboard is empty or contains data which may not be pasted here 2. Range("A1").CurrentRegion.Copy ppSlide.Shapes.PasteSpecial ppPasteOLEObject [Test Result] Excel and Powerpoint both stopped so killed these processes in Task Manager 3. Range("A1").CurrentRegion.Copy ppSlide.Shapes.PasteSpecial ppPasteEnhancedMetafile [Test Result] It works! 4. Range("A1").CurrentRegion.CopyPicture ppSlide.Shapes.Paste [Test Result] It works! May I ask what caused this error and what is difference among them?
Hi Andrew, i too got the same error and i tried Range("A1").CurrentRegion.CopyPicture ppSlide.Shapes.Paste My question is why .copy is not working here and .copyPicture is working Pls clarify
Hi Andrew, As may you know, i am big fan of your videos. It helps me a lot. I have a question about pasting charts from excel 2010 to Powerpoint 2010 without linking the chart or embedding the chart It is like a paste special values in excel. the problem comes from picture type of charts. My CEO strictly rejects the picture type chart at Powerpoint and if i paste all charts in excel with linking or embedding to powerpoint, the size of presentation becomes 300 mb :( and all charts are changing when the excel changes(I really don't want that). Is there a way to prevent this both problems. Thanks in advance
***** He wants to see it as chart because he thinks picture type is unprofessional. I create a solution but it is not efficient. i copy the chart in ppt which i add everytime the macro runs. After tahn break thr link and copy it at the background. It works but i think it is not efficient. Do you think is there a better way like this? Thanks in advance
Hi Andrew, Great video .. Is there a vba code to copy a value from excel worksheet and paste it on a particular slide's textbox where we want to paste. For example : can i paste a value of 20 from excel to a table column in ppt slides. Any help would be helpful. Thnx
***** hey. i wana know another thing. Im trying to calculate last row and last column value and copying to another sheet, but since i want to copy multiple value from same sheet and copy and paste them on different sheet so that i could paste them on ppt. im not able to select them all at once. can u help? this is the code: Sub CopyPaste() Dim ppApp1 As PowerPoint.Application Dim ppApp2 As PowerPoint.Application Dim PPSlide1 As PowerPoint.Slide Dim PPPres1 As PowerPoint.Presentation Dim rng1 As Excel.Range Dim oPPShape1 As Object Dim newslide1 As PowerPoint.SlideRange Dim tb1 As PowerPoint.Shape Dim otable1 As Table Dim iRow1 As Integer Dim iColumn1 As Integer Dim myMschart1 As PowerPoint.Chart Dim LastRow1 As Long Dim LastColumn1 As Long Dim val1 As Variant Dim PPPres2 As PowerPoint.Presentation 'opening up powerpoint "Q3 2014 Basel Model Tracking IEC.pptx" If ppApp1 Is Nothing Then Set ppApp1 = New PowerPoint.Application ppApp1.Visible = True 'If ppApp2 Is Nothing Then Set ppApp2 = New PowerPoint.Application 'ppApp2.Visible = True Set PPPres1 = ppApp1.Presentations.Open("C:\.pptm") ppApp1.Visible = True 'Set PPPres2 = ppApp2.Presentations.Open("C:\.pptx") 'ppApp2.Visible = True 'opening up workbook with specified name 'Workbooks.Open "C:\.xlsm" Worksheets("Overall EAD").Activate 'MsgBox Format(Worksheets("z").Cells(4, 4).Value, "0.00%") 'LastRow1 = Worksheets("z").Cells(Rows.Count, "A").End(xlUp).Row 'MsgBox LastRow1 'Search for any entry, by searching backwards by Columns. 'LastColumn1 = Worksheets("z").Cells(8, Columns.Count).End(xlToLeft).Column 'MsgBox LastColumn1 LastRow1 = ActiveSheet.Range("D7").End(xlDown).Row LastColumn1 = ActiveSheet.Range("E8").End(xlToRight).Column 'LastRow2 = ActiveSheet.Range("AA7").End(xlDown).Row 'LastColumn2 = ActiveSheet.Range("AA8").End(xlToRight).Column 'MsgBox LastRow1 'MsgBox LastColumn1 'This line is showing error, since I cant do this. what's the option? Worksheets("z").Range("Cells(LastRow1, LastColumn1), Cells(LastRow1, LastColumn1)", "Cells(LastRow1, LastColumn1), Cells(LastRow1, LastColumn1)").Select 'Worksheets("z").Range(Cells(LastRow1, LastColumn1), Cells(LastRow1, LastColumn1)).Select Selection.Copy Sheets("sheet1").Activate Range("j1").Select ActiveSheet.Paste Worksheets("sheet1").Range("J1").Activate 'copying the data from excel to powerpoint PPPres1.Slides(7).Shapes(2).Table.Cell(3, 5).Shape.TextFrame.TextRange.Text = Format(ActiveCell.Value, "0.00%") 'PPPres1.Slides(7).Shapes(2).Table.Cell(3, 4).Shape.TextFrame.TextRange.Text = Format(Worksheets("Bucket Accuracy LGD").Cells(LastRow1, LastColumn1).Value, "0.00%") 'Workbooks.Open "C:\hello.xlsx" 'Worksheets("z").Activate 'Range("G4").Activate 'PPPres.Slides(8).Shapes(2).Table.Cell(2, 4).Shape.TextFrame.TextRange.Text = Format(ActiveCell.Value, "0.00%") 'Clear The Clipboard Application.CutCopyMode = False End Sub
***** : i have done that, but the thing is, i have to generalized few things in this code. for Eg: in this line, im putting up the range "AA7" and so no. there is something that I can do, so that I can copy last cell in a row until blank such that I dont need to hard code it in the program. 'LastRow2 = ActiveSheet.Range("AA7").End(xlDown).Row 'LastColumn2 = ActiveSheet.Range("AA8").End(xlToRight).Column 'MsgBox LastRow1 here is new code: Sub ExceltoPpt() Dim ppApp1 As PowerPoint.Application Dim ppApp2 As PowerPoint.Application Dim PPSlide1 As PowerPoint.Slide Dim PPPres1 As PowerPoint.Presentation Dim oPPShape1 As Object Dim newslide1 As PowerPoint.SlideRange Dim tb1 As PowerPoint.Shape Dim otable1 As Table Dim iRow1 As Integer Dim iColumn1 As Integer Dim myMschart1 As PowerPoint.Chart Dim LastRow1 As Long Dim LastColumn1 As Long Dim LastRow2 As Long Dim LastColumn2 As Long Dim LastRow3 As Long Dim LastColumn3 As Long Dim val1 As Variant Dim rng1 As Range, rng2 As Range, rng3 As Range Dim PPPres2 As PowerPoint.Presentation 'opening up powerpoint "Q3 2014 Basel Model Tracking IEC.pptx" If ppApp1 Is Nothing Then Set ppApp1 = New PowerPoint.Application ppApp1.Visible = True Set PPPres1 = ppApp1.Presentations.Open("C:\Users\kgupt48\Desktop\Data automation\Q4 2014 Basel Model Tracking IEC.pptm") ppApp1.Visible = True Worksheets("Overall EAD").Activate ' have to automate this With Sheets("Overall EAD") LastRow1 = ActiveSheet.Range("D7").End(xlDown).Row LastColumn1 = ActiveSheet.Range("E8").End(xlToRight).Column LastRow2 = ActiveSheet.Range("AB7").End(xlDown).Row LastColumn2 = ActiveSheet.Range("AB8").End(xlToRight).Column LastRow3 = ActiveSheet.Range("AV7").End(xlDown).Row LastColumn3 = ActiveSheet.Range("AV8").End(xlToRight).Column MsgBox LastRow1 MsgBox LastColumn1 MsgBox LastRow2 MsgBox LastColumn2 MsgBox LastRow3 MsgBox LastColumn3 Set rng1 = .Cells(LastRow1, LastColumn1) Set rng2 = .Cells(LastRow2, LastColumn2) Set rng3 = .Cells(LastRow3, LastColumn3) Set rngunion = Union(rng1, rng2, rng3) rngunion.Select Selection.Copy End With Sheets("sheet1").Activate Range("J1").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Selection.NumberFormat = "0.00%" 'copying the data from excel to powerpoint- automate value PPPres1.Slides(8).Shapes(2).Table.Cell(3, 5).Shape.TextFrame.TextRange.Text = Format(Worksheets("Sheet1").Range("J1").Value, "0.00%") 'PPPres1.Slides(7).Shapes(2).Table.Cell(3, 4).Shape.TextFrame.TextRange.Text = Format(Worksheets("Bucket Accuracy LGD").Cells(LastRow1, LastColumn1).Value, "0.00%") 'PPPres.Slides(8).Shapes(2).Table.Cell(2, 4).Shape.TextFrame.TextRange.Text = Format(ActiveCell.Value, "0.00%") 'Clear The Clipboard Application.CutCopyMode = False End Sub
Komal Gupta hey, or could you tell me a method so that I can copy last cell value before every blank cell for many rows in a single sheet. I hope you must have understood by the code I just have sent you. I cant hard code the value. or should I save my excel in some other? In short, in any case, i dont want to mention any range, just row and column if possible.
No. Im trying to copy a last cell value from each row where the data is present and then copying that value to another sheet and then from there im copying to ppt slides on a particular table cell
Hi wise owl thank you for great video/ training. I am having a number issues. I am using office 10. My first issue I encounter is when aligning the data in the slide. ppapp.activewindow.selection.shapeRange.align msolignMiddles, msotrue. I can run time error.
Hi wise owl thank for your willingness to help. Here is the error I encounter and the code I used. Run-time error'-2147188160(80048240)': Selection (unknown member):Invalid request. Nothing appropriate is currently selected. My Code Sub CreateNewPresentation() ' This section is were you set variables Dim ppapp As PowerPoint.Application Dim ppPres As PowerPoint.Presentation Dim ppslide As PowerPoint.Slide ' This section is to make Excel aware of Powerpoint Set ppapp = New PowerPoint.Application ppapp.Visible = True ppapp.Activate 'This section is about adding slide, the intro slide and populating each of the text boxes of slide with text data Set ppPres = ppapp.Presentations.Add Set ppslide = ppPres.Slides.Add(1, ppLayoutTitle) ' Here the instruction will be to input text into each of the boxes of the slide in Powerpoint ppslide.Shapes(1).TextFrame.TextRange.Text = "Excel to Powerpoint Demo" ppslide.Shapes(2).TextFrame.TextRange.Text = "By J" ' This section is to add new blank slide and set range to copy and paste data from Excel into Powerpoint. Plus align to fit slide Set ppslide = ppPres.Slides.Add(2, ppLayoutBlank) ppslide.Select Range("A1").CurrentRegion.Copy ppslide.Shapes.PasteSpecial ppPasteOLEObject 'This section deals with alignment of the data within the Powerpoint slide ppslide.Shapes(1).Width = ppPres.PageSetup.SlideWidth ppslide.Shapes(1).Left = 0 ppapp.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, msoTrue End Sub
Hi wise owl I am down to getting a chart from excel to power point bunt when I follow the instruction - set ppslide = ppPres.Slide.add(3, ppLayoutBlank) Ppslide.select Chart1.chartArea.copy I get error Method or data member not found My apologises for all the messages.
Hi It is slides.add, sorry about the typo in my messages. The debug line is related to chart1.ChartArea.Copy line. I went back to earlier teachings of yours and used a work around based of them instructions. I added in the likes of worksheets(2).select and used this command Activesheets.ChartObjects("Chart 1").Activate ActiveChart.chartArea.Copy This works I am not sure why, found it via goggle, as I don't want to be taken advantage of your good nature. But what happens is it does not generate PowerPoint in the order I want. So if I add worksheet(3) using this active Chart command, the slide will appear as slide two and so on.
Hi Andrew, Thanks for this video. I have a query related to VBA in PPT, if you can help me with that. I want to copy the tables from PPT to Excel. I have tried the following code in PPT VBA, its partially working but not the way i want. - there are 4 shapes on PPT : 2 tables and 2 text box - following code is copying and pasting everything as picture in Excel - I want to copy my tables as table in excel so that I can use those tables value for further calculation ---------------------------------------------------------------------------- Sub CopyTablesToExcel() Dim i As Integer Dim xlApp As Object Dim xlWorkBook As Object Set xlApp = CreateObject("Excel.Application") xlApp.Visible = True Set xlWorkBook = xlApp.Workbooks.Open("C:\Experiments\Matrix.xlsm", True, False) For i = 1 To 4 ActivePresentation.Slides(1).Shapes(i).Copy xlWorkBook.sheets("Sheet2").Cells(2, 9).PasteSpecial ppPasteDefault Next i End Sub ------------------------------------------------------------------------------------------ Thanks, KP
Hi, Do you know how I would code in VBA in order to apply an existing template? the template already has graphs, charts, and texts. I am trying to automate my reports from excel into powerpoint instead of doing it all manually. I have already created all the charts and I am now just trying to figure out how to create the whole powerpoint presentation through coding so that it is semi-automated. Please let me know. Also if you could just direct message me, I would need alot of help and if you could aid me in this process, I would be truly grateful. Thanks!
How would I set the code so that I can Copy and Paste into the powerpoint? doing ppslide = pppres.slides does not work and also just trying to copy and paste into the slide with ppslide(3) does not work as well. (slide number is 3). I have also tried a number of different stuff but it did not work. Do you know how to set the code so I can start copying and pasting? Sorry being such an inconvenience but your help would be amazing.
Hello. I am in Excel 2010, PowerPoint 14 is ticked, around minute 23 you explain ppSlide.Shapes.Paste, and it doesn't work for me, but the pasteOLEObject works fine, I also get the same problem with pasteJPG, PNG and other somple formats. it says "Shapes(unknown member):Invalid request. Clipboard is empty or contains data which may not be pasted here". I checked and it is copying the table, and the table is a copy of yours. Thank you.
Hi Andrew, your tutorial video is fantastic. What like most about your video is the care that you take to touch upon nuances of different approaches. Being a fairly proficient in VBA, I recommend this to anyone who is interested in learning VBA. Good job posting such good quality video freely on tube when many people are trying to make money out of low quality trainings. Hats off.
Solid work Andrew. I attended a Advanced VBA course years ago, and they never showed you how to work with Powerpoint. This will be really helpful when preparing statistics for presentations. I can essentially automate the whole process. Great stuff
Ths is probably one of the best tutorials that I found on youtube. You show several approaches to get the same result, which is very good. I helped me a lot, since I am a noob in excel VBA. Congratulations for the video you posted. Amazing!
This tutorial is perfect! Thank you very much. As an amateur, It has enabled me to really take my projects to the next level. It has really good explanations and auxiliary codes that gives viewers a comprehensive understanding of what they are doing. It also combines a little bit of everything that you need to know in order to get started using Excel and PowerPoint VBA. For example, how to set up a new ppt slide show from Excel, adding and editing slides & shapes in PowerPoint through Excel. Compatibility issues between older and newer versions, early binding vs. late binding. It's great. I'm going to send it to my business partner. Thanks again.
You don't ever disappoint! Thank you Andrew 😊
Thank you Naima, glad you enjoyed that one!
Hi Andrew,Just want to sincerely say thank you for this excellent and brilliant tutorial ! I'm a beginner with VBA and currently working on a project to automate movement of data from Excel to PP and this video has truly given me the knowledge and initial building blocks I'll need to complete the project. I truly thank you for sharing your expert knowledge! Also thank you for the excellent explanations along with showing how to write the code with the sequences you provided, your delivery in terms of speed and overall rapport with the audience is excellent as well - I was able to follow along with you perfectly - I wish I could have you as an instructor lol. Overall, thank you very very much for this tutorial as it has been extremely helpful to me.Questions: I have successfully utilized your code (using Excel 2013 and PP 2013) for the primary lessons you have provided. However, I was unsuccessful in utilizing the code to copy a chart over to Excel. My challenge now is to modify the code you have provided to fit my needs ... 1. I have more than 1 set of Excel ranges that I need to copy and paste to a single slide. Please kindly advise how to do that ? Your video only shows how to copy and paste 1 range of excel data to 1 PP slide.2. I was unsuccessful in copying a chart over to PP. In the area in VB where it says "VBAProject (Book1)" (not sure what that is called) the "Chart1" is not showing up there ... Is that the reason why I am unable to copy my chart over to PP ?Thank you once again for this brilliant tutorial - it is truly appreciated !
Hello Andrew, If I do the following set then it works like a charm all the time:. I put in couple of Sleep functions after copying and before pasting.
ppSlide.Select
Range("A1").CurrentRegion.Copy
Sleep (50)
ppSlide.Select
Sleep (50)
ppSlide.Shapes.Paste.Select
Thanks a lot sir, I learnt too much there.
Thanks Manoj!
Holy Cow Batman! I hope you are being paid boxes and boxes of money. You are awesome!! Best video tutorials I have ever seen. Keep up the great work!!!
Wonderful Tutorial! From Beginner to Hero! Challenging bit was the later versions 2019 and others
Happy to hear that you enjoyed it, thanks for watching!
I love your videos. I have been watching them daily for the last few weeks, and also for a while a couple of months ago. I skipped this one after watching half of it because it just felt like it was about formatting and compatibility. But i guess that is really all that power point is anyway? I'm ready for Outlook!
Andrew Great Video, I did have issues with the pasting but NBAH NBAHOB below gave code ppApp.Window(1).View.Paste and it worked, this is an issue with 2010 users. Great Job and great teaching. Thank you
Andrew....... just wanna say, Great work, you are awesome with ur skills... Thank YOU
Hi there,
Thanks for an insightful upload.
I found that to get around the paste problem in 2010-version of PP we can use this:
ppApp.Windows(1).View.Paste
Works like a charm.
You are amazing. Thank you!
Thanks, this worked for me :) another way that worked is ppSlide.Shapes.PasteSpecial(ppPasteOLEObject).Select but that's for image only :)
The great VBA maestro👍
This is just an amazing tutorial. Thanks a lot for this one!!!
Awesome!!
Thanks Satish!
thanks to your tutorials, I can transfer all my excel dashboard page to PP slides to which I can present on my crucial meettings ....
could you pls also show us how to create an event that you can click a shape and select it automaticly in a shape groupitem
Thank you very much Andrew , it is very nice of you
I loved this video absolutely.. just broke my heart when i realised at a later point that i would not be able to use paste for powerpoint 2010.. nonetheless extremely helpful..
Great video thanks for sharing. I have a question how do you copy ws.Range("A1:G2").copy from sheet1 in Excel to PPT in an existing table. I am able to paste one cell at a time but how do you paste a range. For example I am able to paste ws.range("A1").copy to tbl.cell(3,2).shape.textframe.textrange.paste but how do you copy a range from excel to a table range in PPT.
Hi Andrew! Another amazing video. Thank you so much!
Thanks for this video. I am surprised that it only has such low number of views. This is quite an interesting topic, isnt it? :-)
Andrew, Your videos are great. I just wanted to say "Thank You".
Is there any way to write a vba code to refresh power pivot without opening the Powepivot? If there is one, I would really appreciate a sample code or a video would be great.
Absolutely excellent video. Thank you very much!
Hi Andrew...I've been following along with your tutorials and must say that yours are the best that I've seen. Great accent by the way! I'm using Office 2010 and with this video I'm having a problem of where the data is pasted from Excel into PowerPoint. Everything before this works fine.
When I use the line of 'ppSlide.Shapes.Paste' I get an error on this line. It says "Shapes (unknown member): Invalid request, Clipboard is empty or contains data which may not be pasted here." I can look at the data, (a few rows and columns of numbers) from Excel and see that it's selected and I can do a manual paste in Excel and in PowerPoint. I did some testing using the PasteSpecial and it works with ppPasteBitmap, ppEnhancedMetalfile and ppPasteMetaFilePicture. With all the others, it errors out with the message of "Shapes (unknown member): invalid request, the specified data type is unavailable."
I thought that maybe the data had to be in a picture format such as jpeg, png, etc. before it would work, but then I was confused to how the ppPasteBitMap would work. Could you enlighten me to what the issue may be? Thanks Andrew for any insight that you may have!
Update: I read over the comments after posting this and it seems that Office 2010 has an issue with the paste method in PowerPoint and saw possible fixes. I didn't want to waste your time Andrew and hope you have a great day!
+WiseOwlTutorials Thank you very much Andrew for your quick reply which is a lot to be said with other channels on RUclips where you never hear a reply. Again, you've really done a great job with your tutorials and I know it takes a lot of time to put them together. Just wanted to let you know that it's very nice of you and a lot of folks can really learn VBA from you. Have a super GREAT day!!!
Man, this is Gold :D. Thank you so much
can you set up where the data for each presentation is being pulled from. for example, slide 1 data is from a1 to d6, and slide 2 data is from a10 to d20, etc.
Hi
After adding two labels I want to format the two labels differently .how would I add that.
Hi Andrew
This video is really nice for me. I got one problem when I try to use the template.
1.How do I using the template in the head page (First page), since my first page style is different then other page.
2.All of the other page can be generated by template succefully.
Simply to say, Main page background are different than the others.
How can i get the template filr?
Very nice video, You have really given a long but comprehensive account. Thanks a lot. I am sure you have no problem with your code. However, it was very tough to understand what you said initially. Your accent is very tough. Hire someone who can talk slower than you. That makes us understand more.
Thanks a lot again, WOT! All the best...
+WiseOwlTutorials OK
+WiseOwlTutorials Just for the record Andrew, you're doing a great job and your accent is great. Actually keeps me inspired to hear you talk. Keep up the good work and don't worry about nay sayers!
Excellent tutorial. Outstanding!
thank you for this. this is exactly what I have been looking for. wonder if you could help out with the below
how to center horizontally and vertically the selected table content using the selection and not going through cell by cell
I noticed when pasting a table it adds leading spaces which mess up the allignments
I'm not sure why you're seeing extra leading spaces, I don't see the same thing happening for me.
I think it's easiest to change the alignment in Excel before copying into PowerPoint
With Range("A1:D6")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
But if that's not working, you can loop through the cells in the PowerPoint table after pasting it into the slide, 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
Dim tbl As PowerPoint.Table
Dim rw As PowerPoint.Row
Dim c As PowerPoint.Cell
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.Paste
Set tbl = sh.Table
For Each rw In tbl.Rows
For Each c In rw.Cells
With c.Shape.TextFrame2
.VerticalAnchor = msoAnchorMiddle
.HorizontalAnchor = msoAnchorCenter
End With
Next c
Next rw
@@WiseOwlTutorials Hi thank you for this mate.
I actually thought of hte same approaches.
Excel has numbers and text with perfect formatting. when pasted into PowerPoint i keep getting those leading spaces and reck the alignment. So I fix the issue by looping through table cells in PowerPoint but given I am doing a large number of export it is time-consuming.
I am searching the topic. Until now I haven't figured out a way at least to do the alignment to the table in PowerPoint but by selecting the whole table and do the formatting as you would manually. Insofar what worked for this is to change cell by cell
@@ahmed007Jaber It's so strange that you get these leading spaces, I've never seen that happen before!
At least you have a workaround but let me know if you find a better solution!
@@WiseOwlTutorials will do for sure. I programme in R and i just found about possibilities with VBA so i am learning a lot about it now
Hi Andrew,
Once again, thank you so much for this excellent tutorial, one request to you, could you please make a tutorial for how can we modify existing created PowerPoint presentations, like how can we take reference of already created charts in PowerPoint, textbox, bullets shapes or any other texts, and alter accordingly, like I have to paste certain data in already created presentaion, but in same font size, same font color, same height, width, that already in the presentation, means just alter the text as a value in some part.
I hope, I clear my point as per my ability.
Thanks
Kashif
Hi I have one question why didn't you make even a single video MS Access macro?
How do I set the slide size to custom 4: 3 ? Please please help
very usefull tks. Nevertheless, I need to repace fields in mt ppt file, from data in my xls file. Do you thing it is possible?
maybe you can delete all the fields first and re-paste them
Why don't you break it into functions? It seems it is in one big function. Any engineering reason for that or just for keeping it simple? I never know when to break up code into functions.
Thanks for the video. Can we scroll(forward/backward) slides in all open powerpoints simultaneously using VBA in one screen?
Thanks a million for this tutorial. I would like to ask, how to paste the data from excel to word or powerpoint as text and not as image or table?
wow. best vba presentation i have ever come across so far. Thanks a lot!
I have a question though. If I want the macro to copy ranges from different sheets on the same excel workbook, how would i do that? Also if i want the macro to recognize empty columns and rows and copy on the non-empty ones, how would i do that?
Thank you!
At the end of session ppPres.close is not working properly..Getting Restart Program after Pressing F5
Thanks for this tutorial ... I am currently working on similar task (certificate names pasted in a single textbox in powerpoint, one certificate on each slide, one name in the same textbox on that slide), however the textbox has a combination of "salutation" (Mr for Male and Ms or Mrs for Female etc) for and "name" with a different text size, colour and font type (in the same textbox). Please provide some help on how I can do this. I am a beginner in this however looking at the VBA code and explanation you/others on web provided I was not too bad in understanding and debugging. Thanks in advance - Hari
Hi Andrew,
Your videos have been vary helpful, but i need to know one more thing. How would i copy text/numbers from a cell in excel to a text box in power point. A lot of presentations i make are for upper level managment and they change data all the time. I don't mind updating the data in excel, but then i have to go and completely redo the powerpoint too.
Thank you for all your help!
Hi Wiseowl,
your tutorials are awesome !! Thanks for helping millions of people!!!
when ever I paste my grouped pictures and charts into power point using excel vba the name of the object is getting changed. Hence I am unable to do the formatting, Is there any way to name the group in powerpoint using excel vba to refer them in excel coding ?
Excellent Video!
Were can I download the PowerPoint Templates For Version 2016?
Hi Andrew, Thank you so much for this video! You made it really easy to follow along! :)
I have a question in lieu with the range that we select from the excel sheet. Instead of using it as Range ("A1").Currentregion, is it possible that I could refer it to a named Range? If so do I define the range early on?
Hi Andrew,
Awesome! Another video added in my collection! I’m learning VBA thanks to you! Since video 5 :-).
May I ask you how could I add a loop into your code so whenever I run it a new slide is created into the same presentation? Aline that because I have a pivot chart and when changing the slicers I would like to create a new slide. Cheers Gilly
Every time I need some information on VBA I find you have a video on it! And a very helpful one as always.
Would you consider returning to and adding to this particular topic at any point?
Well I appreciate all of your hard work. Stick it somewhere on the list, I'll look forward to it if you ever get the time!
this is just a brilliant video.
just one thing. I noticed you have a "chart1" object within your "Microsoft Excel Objects". I have a chart but it is not shown as an object. I only have "sheets" listed under my "Microsoft Excel objects". So i am having issues transferring the chart to powerpoint.
Thank you for this additional information! I couldn't figure out why my pivot charts weren't showing as objects. This is a great fix! Thanks!
24.20 when I tried doing the same, the data gets pasted to PowerPoint but the format does not...I meant if I color any cell...tht color does not come when I run the code. What am I missing? Is there way where I could paste it as a table and not shapes?
One of the best tutorials I have ever seen: Thanks and Chapeau! I was trying to copy a linked picture from Excel into PP, but I failed to do so. Is there something specific with pictures? Besides I am running version 16.0; are there some specific issues I have to know? But again...I have never seen such an informative and usefull tutorial. People should know
Hi, I found the following worked for me rather than pasting as embedded object from excel into powerpoint try something like, worksheets("Sheet1").Range("A1:F30").CopyPicture (the key being CopyPicture) and when pasting into powerpoint a normal paste will work fine - not paste special. ppSlide.Shapes.Paste (my example was copying a chart into powerpoint as a picture so it could not be manipulated by end user of presentation)
I am getting an error "REMOTE Procedure call failed". How to resolve it. I want to create a PPT of 60 slides with various tables,charts copied from excel to PPT, with this error i got struck, can you please help me with this
At 1:07:10 your MS PowerPoint crashed.
I faced the same problem as shown with office 2013 and I have single version of office installed.
Found that the Powerpoint is crashing because we are trying to close the presentation before the save process has concluded. Hence the crash.
We need to place a delay between save and close processes in order to prevent the crash.
We can call the below sub between save and close process.
Sub Wait(tSecs As Single)
Dim sngSec As Single
sngSec = Timer + tSecs
Do While Timer < sngSec
DoEvents
Loop
End Sub
Source: www.nullskull.com/q/10216453/excel-vba-code-leads-to-powerpoint-crash--code-check-f8-ok-f5-nok.aspx
Hello, Andrew! Nice work as always, like everything you do. I have a question: I have several different ppt files (300+) with one or two slides each one. How can a do a VBA routine to merge all slides in a unique file? After that, is there a way to rename each slide with the original file name? Thanks for all your help.
Hi Marcelo! I've added a video showing how to do this ruclips.net/video/UHE7p3ahtF4/видео.html
It's a members-only video at the moment but the basic premise involves looping through the PowerPoint files in a folder and using the InsertFromFile method to copy all the slides into the master presentation.
Hi Andrew - I also have enjoyed all the tutorials - but I am also having the same problem as Ricardo. Are you still in Excel 2007 at this point?
how to copy text from multiple rows of an excel to powerpoint as text and not as image
Hi Koushik, here's one way to do it. The code assumes you have text in cells A1:A5 on Sheet1 and that you want to create a new presentation rather than refer to an existing one:
Sub CreatePresAndCopyTable()
Dim ppt As PowerPoint.Application
Dim pres As PowerPoint.Presentation
Dim cl As PowerPoint.CustomLayout
Dim sl As PowerPoint.Slide
Dim sh As PowerPoint.ShapeRange
'Create a new presentation
Set ppt = New PowerPoint.Application
Set pres = ppt.Presentations.Add
'Reference a CustomLayout and create a slide using it
Set cl = pres.SlideMaster.CustomLayouts(7) 'a blank slide
Set sl = pres.Slides.AddSlide(1, cl)
Sheet1.Range("A1:D6").Copy
'Various techniques to paste
Set sh = sl.Shapes.Paste 'paste as a PowerPoint table
'Set sh = sl.Shapes.PasteSpecial(ppPasteText) 'text
'Set sh = sl.Shapes.PasteSpecial(ppPasteRTF) 'rich text format
'Set sh = sl.Shapes.PasteSpecial(ppPasteOLEObject) 'embedded Excel object
'Set sh = sl.Shapes.PasteSpecial(ppPasteMetafilePicture) '16 bit image
'Set sh = sl.Shapes.PasteSpecial(ppPasteEnhancedMetafile) '32 bit image
'Move the shape to the top left corner of the slide
sh(1).Top = 0
sh(1).Left = 0
End Sub
I hope it helps!
Hi WiseOwl, This is an excellent video. I tried to reproduce this exact code. But I keep getting an error message when I do the Paste method
ppSlide.Shapes.Paste.Select
The message reads as follows:
Runtime Error -214188160(80048240)
Shapes (unknown member): Invalid Request. Clipboard is empty or contains data which may not be pasted here.
Could please help me resolve this issue.
Hi Andrew, I seem to get an error even after trying this out. Could you help me out with a fix for this?
I had this problem too. This reply fixed it. Thanks!
Hi Andrew, Love the tutorial! Really easy to follow! Only problem I'm having is when when I add in the line of code to specify the width when I then run the code the shape or chart is completely invisible. At first I thought it was not there but it is in fact still there but incredible narrow. This happens no matter what width I enter 600, 1000 or SlideWidth. Do you know what could be causing this?
Hi Andrew yes the same happens when I try to change the Height property. I am using PowerPoint for mac 2011
HI Abdrew,
i just learning the PPT conversion from your video,
i just creating what you showed in Video same way i creating the file in 2010 but when i run the file its showing Run time error - its showing when i create the second slide with excel
ub CreateNewPresentation()
Dim ppApp As PowerPoint.Application
Dim ppPres As PowerPoint.Presentation
Dim ppSlide As PowerPoint.Slide
Set ppApp = New PowerPoint.Application
ppApp.Visible = True
ppApp.Activate
Set ppPres = ppApp.Presentations.Add
Set ppSlide = ppPres.Slides.Add(1, ppLayoutTitle)
ppSlide.Shapes(1).TextFrame.TextRange = "GROUP CORPORATE"
ppSlide.Shapes(2).TextFrame.TextRange = "GBC 2016 - 17"
Set ppSlide = ppPres.Slides.Add(2, ppLayoutBlank)
ppSlide.Select
Range("A1").CurrentRegion.Copy
ppSlide.Shapes.Paste
End Sub
can you help me
Hiiii , thanks for the video, super helpful , I have one Question ,
Q: How to change Slide size, Like I Want it to be A4 Size , or A3 Size, or other custom size ?
hope you answer my question
Super , Thank a lot. ^_^
Hi guys,
in this tutorial we can learn how to create a new Powerpoint presentation
is there a tutorial which shows how one can modify an EXISTING Powerpoint presentation?
e.g. I have a Presentation and I just have to change the current date, stock price, name (of the firm, the presenter, ...), etc on multiply slides, so it would be super helpful to build a macro, where I can type in this data once and it will paste this data on certain slides on an existing Powerpoint presentation
thank you :)
Hi, we don't have a video on this but it isn't too difficult to do - the code below assumes you have a presentation saved in the same folder as the Excel workbook:
Sub ModifyPresentation()
Dim ppt As PowerPoint.Application
Dim pres As PowerPoint.Presentation
Dim sld As PowerPoint.Slide
Dim shp As PowerPoint.Shape
Set ppt = New PowerPoint.Application
Set pres = ppt.Presentations.Open(ThisWorkbook.Path & "\MyPresentation.pptx")
Set sld = pres.Slides(1)
Set shp = sld.Shapes(2)
shp.TextFrame2.TextRange.Text = "Created on " & Format(Date, "dddd d mmmm yyyy")
pres.Save
End Sub
I hope that helps!
@@WiseOwlTutorials hey WiseOwl
I found another tutorial on youtube and have now found a solution to my problem
Thank you very much for your fast reply and the code, I'll definitely try yours out as well
Keep up the good work, thank you for your efforts and your channel :)
Great! Happy to hear that you found a solution and thanks for watching!
Hi Andrew, is it possible to run an instance of powerpoint in a userform in excel?
Sorry I don't know of any way to do that.
You are the best
Show!!!!
Thanks for sharing.
I was wondering if you could help me with select specific text length from the ppt sentence and bold it those are text
Can I edit the slide masters from excel vba?
One question: Can i get the excel file with the code somehow?
Do you suppose if I had a custom template I wanted to use in the code, that I could embed it in the Excel file and bring it out at run time? Perhaps a few for different office versions would do. However I would need it to not be installed on the user's computer, only accessible in excel or PowerPoint (whatever works best).
Hi, Andrew! Great work, thanks!
Question regarding ApplyTemplate method: It perfectly works when using a Microsoft template. However, it throws a run-time error if trying to use custom template. Can you please suggest any workaround for that? Path is set correctly and .potx file exists.
Error message: "Presentation (unknown member): Invalid request. PowerPoint could not open the file"
Hi Alisher! I believe the ApplyTemplate method only applies to Microsoft templates docs.microsoft.com/en-us/office/vba/api/powerpoint.presentation.applytemplate
I believe (although haven't tried it) that you can use the ApplyTheme method to apply custom themes or templates docs.microsoft.com/en-us/office/vba/api/powerpoint.presentation.applytheme
I hope it helps!
@@WiseOwlTutorials Thanks, but I am still getting the same error.
@@alishernarzulloyev3855 Hi Alisher, sorry I don't know what the problem is in that case.
Hi Andrew,
I am a really big fan of your videos and they have helped me more than I can state in my work!
I do have one question though. I am building a tool for a colleague in which I need to update her charts in PPT from data in excel. The charts are not linked (as in they do not automatically update when you change the excel data), but rather they are the type where you have to right click on the data on the graph in PPT (an excel spread sheet will then pop up stating Linked data - "File Path"), so I suppose they are embedded.
How is it that from an excel vba tool, I can go into this embedded excel file in the PPT Chart to make edits?
Any help would really be appreciated!
Thanks
Neil
Great videos, Andrew.
I was wondering if you could help me with an issue I'm having: in PowerPoint, is it possible to select all shapes within a specific boundary (top, left, height, width)? Basically the same function as when one selects an area with the cursor to activate all shapes within that boundary, but through VBA. Trying to combine this with a loop, but I'm having some issues. Thanks a lot!
Thank you for taking the time to answer, Andrew, I really appreciate it. I get no error when I use this code, but no shapes are selected after I activate. Also I am unsure of how to manipulate the shape properties through this loop. I tried to add "sh.TextFrame.TextRange.Font.Color = RGB(11, 52, 104)" I also experimented with using a with statement, but neither seems to not work. Any suggestions?
I have a query I'm making a multi choice question game for my kids (I teach). what I have is a slideshow with a question box and 4 possible answers I want to run a macro so when I press a command button it generates a new question and answers the questions and answers are in an excel sheet I have all the slideshow setup linking to different slides but I don't know how to get the questions from the excel sheet to the pp slides any help is much appreciated
alan
i have it all finished except the code to change the questions and answers. What i want by the end is a form attached to a sheet to input the questions and answers and a button to add the new questions and answers into the boxes.
Hello Andrew, please may I know how to run the VBA without making Powerpoint visible? I have commented out below codes but it still doesn't work.
'ppApp.Visible = True
'ppApp.Activate
Seems like your commands with "Select" or "Active" will force Powerpoint become visible, and this is not something I want so how to amend your codes to make it still able to run while making Powerpoint invisible?
Hi WiseOwl,
Great Video! I have a question please. The powerpoints that I create using excel vba always takes a long time to open and the data of the charts are also missing. When I try to edit with excel in ppt, it opens up an excel file that is completely blank of data.
What is interesting though, is that when the ppt is opened from another computer, the powerpoint is completely normal and fine with all its data points.
Do you know how I could fix this?
Sir everytime you are closing ppt and opening it again through excel VBA.
But how can edit already opened presentation file?
Hi, if you have a single presentation open you can do this:
Dim ppt As PowerPoint.Application
Dim pres As PowerPoint.Presentation
Set ppt = GetObject(Class:="PowerPoint.Application")
Set pres = ppt.Presentations(1)
Debug.Print pres.Name
I hope it helps!
@@WiseOwlTutorials I will try this out thank you for help❤️
@@sagargondage6266 No problem!
Hi Andrew, Your video is very interesting and very easy to use but I need your help for a vba problem: I have an Excel with a data board (4 columns like A,B,C, Dand lot of rows).I would like to export some information to powerpoint with a VBA process like that : Powerpoint slide 1 : rows 1,2 and 3Powerpoint slide 2: rows 1 and 4,5,6Powerpoint slide 3: rows 1 and 7,8,9.....And if I want to select diferents colums ? Powerpoint slide1 : rows 1,2 and 3 for columns A,B and D?Can you explain me, what kind of code can I use for this? Thanks a lot for your help.B
Thank you a lot Andrew for your answer, indeed you're absolutly right, this is a good method to fix this problem.
Thanks a lot.
+
how about pasting data from access form
yes, for exemple i have a details of contract in access (Reference of the contract, title, duration, etc and i want to copy these informations for one contract in the pp.
Hi Andrew,
Appreciate for the video, very useful
I have a question,
In your code part:
Set ppSlide = ppPres.Slides.Add(2, pplayoutblank)
ppslide.select
Range("A1).CurrentRegion.Copy
ppSlide.Shapes.Paste
doesnt work for me.
I work on excel 2007, and when im typing
ppSlide.Shapes.Paste it doesnt pastes at all in my code.
instead of this, for me works:
Set ppSlide1 = ppPres.Slides.Add(2, ppLayoutBlank)
ThisWorkbook.Sheets(1).Range("C2:AD32").Copy
ppSlide1.Shapes.PasteSpecial ppPasteOLEObject
I want to paste as usual tab, not an OLEObject
Please help!
***** , Thank you for your respond. It works!
Now, Im facing font size problem
Somehow I cant change font size and name the way u did
Example:
ppSlide1.shapes(1).TextFrame.TextRange.Font.Name = "Calibri"
ppSlide1.shapes(1).TextFrame.TextRange.Font.Size = 30
This method work for titles, not for pasted text as tab
But I found difficult way to change font size and name
My code:
ActiveSheet.Range("A2:A28").Copy
ppSlide2.Select
ppApp.ActiveWindow.View.Paste
ppSlide2.shapes(1).Height = 450
ppSlide2.shapes(1).Width = 170
ppSlide2.shapes(1).Left = 20
ppSlide2.shapes(1).Top = 20
Dim i As Integer
For i = 1 To 25
ppSlide2.Select
ppSlide2.shapes(1).Table.Cell(i, 1).Shape.TextFrame.TextRange.Font.Size = 11
ppSlide2.shapes(1).Table.Cell(i, 1).Shape.TextFrame.TextRange.Font.Name = "Calibri"
Next i
The problem is it takes too much time, changing cell one by one.
Do we have other methods to change font size for pasted tab quickly?
Thank you!
***** , yes i've tried to change font size in the excel, but when it pastes to powerpoint slide,it changes font size depending on table size, as u see below
ppSlide2.shapes(1).Height = 450
ppSlide2.shapes(1).Width = 170
ppSlide2.shapes(1).Left = 20
ppSlide2.shapes(1).Top = 20
Im not stuck with iterating through cells, it works, but it takes about 30-40 sec. to finish font size changing, and i was looking for faster way to accomplish it.
ok, anyway it works and im ok with it!
Thank you for providing me necessary information, appreciate!
Best!
Hi,
I dont know what is the reason, but I am not even able to make the powerpoint application visible.
It is giving error at line - pptApp.Visible = True
The error message is
Run-time error '-2147417856 (80010100)': Automation error System call failed.
I am using 2013 Excel and PowerPoint. I have also checked 'Microsoft Powerpoint Object Library' from the Excel VBA window, Tools -> References.
Please let me know if I need to some other changes in the settings, etc.
Thanks,
Manish
To add to my previous note, I am not able to open a new powerpoint application. Look forward to hear some solution to my problem. Thanks,Manish
Hi Andrew i too got error "shapes (unknown member) : invalid request. clipboard is empty or contains data which may not be pasted here."
by seeing comments here i tried
Range("A1").CurrentRegion.CopyPicture
ppSlide.Shapes.Paste
it works for me
why .Copy Is not working
and .CopyPicture is working
i also tried
Range("A1").CurrentRegion.CopyPicture
ppSlide.Shapes.PasteSpecial ppPasteOLEObject
this is not working
pls clarify
Thank You
Hey,
I'm having problems when I try to copy the chart from the excel to powerpoint.
I tried with the:
Worksheets("Sheet1").ChartObjects("Chart1").Chart.ChartArea.Copy
when the chart is in the same sheet as the data it says Run-time error -214724809 (80070057)
i tried:
Chart1.ChartArea.Copy
When the chart is in Chart1 sheet it says Run-time error 445
Do you have a solution for any of this problems? if you have a solution for the first better.
Sorry if I sound weird but English is not my native language.
it actually says 445 in both Run Time Erros. Not -214724809 (80070057)
when running the code ppslide.Shapes.Paste, it doesn't work and an error occurs saying : shapes (unknown member) : invalid request. clipboard is empty or contains data which may not be pasted here. how to fix this error.
thank's a lot, it Works perfectly
Hi, this is really fantastic to learn Excel VBA. I love it :D. Regarding this run-time error, it still doesn't work by using ppSlides.Shapes.PasteSpecial ppPasteOLEObject. Is there other workaround for me to take?
Many thanks for quick reply :-) Sorry, just realized that you mentioned this error with "ppSlide.Shapes.Paste" on Excel 2010. I was running this VBA code on that version.
Here are test results.
1. Range("A1").CurrentRegion.Copy
ppSlide.Shapes.Paste
[Test Result] runtime error. Shapes(unknown member): Invalid request. Clipboard is empty or contains data which may not be pasted here
2. Range("A1").CurrentRegion.Copy
ppSlide.Shapes.PasteSpecial ppPasteOLEObject
[Test Result] Excel and Powerpoint both stopped so killed these processes in Task Manager
3. Range("A1").CurrentRegion.Copy
ppSlide.Shapes.PasteSpecial ppPasteEnhancedMetafile
[Test Result] It works!
4. Range("A1").CurrentRegion.CopyPicture
ppSlide.Shapes.Paste
[Test Result] It works!
May I ask what caused this error and what is difference among them?
Hi Andrew, i too got the same error
and i tried
Range("A1").CurrentRegion.CopyPicture
ppSlide.Shapes.Paste
My question is
why .copy is not working here
and .copyPicture is working
Pls clarify
you are great Andrew, you solve my problem with this statement beause I have the same problem, now my code works perfectly
Hi Andrew,
As may you know, i am big fan of your videos. It helps me a lot.
I have a question about pasting charts from excel 2010 to Powerpoint 2010 without linking the chart or embedding the chart
It is like a paste special values in excel. the problem comes from picture type of charts.
My CEO strictly rejects the picture type chart at Powerpoint and if i paste all charts in excel with linking or embedding to powerpoint, the size of presentation becomes 300 mb :( and all charts are changing when the excel changes(I really don't want that). Is there a way to prevent this both problems.
Thanks in advance
***** He wants to see it as chart because he thinks picture type is unprofessional. I create a solution but it is not efficient. i copy the chart in ppt which i add everytime the macro runs. After tahn break thr link and copy it at the background. It works but i think it is not efficient. Do you think is there a better way like this?
Thanks in advance
Hi Andrew,
Great video .. Is there a vba code to copy a value from excel worksheet and paste it on a particular slide's textbox where we want to paste. For example : can i paste a value of 20 from excel to a table column in ppt slides.
Any help would be helpful. Thnx
***** : Thanx, Im being able to do that. Your video was quite useful. Thnx for help
***** hey. i wana know another thing. Im trying to calculate last row and last column value and copying to another sheet, but since i want to copy multiple value from same sheet and copy and paste them on different sheet so that i could paste them on ppt. im not able to select them all at once. can u help?
this is the code:
Sub CopyPaste()
Dim ppApp1 As PowerPoint.Application
Dim ppApp2 As PowerPoint.Application
Dim PPSlide1 As PowerPoint.Slide
Dim PPPres1 As PowerPoint.Presentation
Dim rng1 As Excel.Range
Dim oPPShape1 As Object
Dim newslide1 As PowerPoint.SlideRange
Dim tb1 As PowerPoint.Shape
Dim otable1 As Table
Dim iRow1 As Integer
Dim iColumn1 As Integer
Dim myMschart1 As PowerPoint.Chart
Dim LastRow1 As Long
Dim LastColumn1 As Long
Dim val1 As Variant
Dim PPPres2 As PowerPoint.Presentation
'opening up powerpoint "Q3 2014 Basel Model Tracking IEC.pptx"
If ppApp1 Is Nothing Then Set ppApp1 = New PowerPoint.Application
ppApp1.Visible = True
'If ppApp2 Is Nothing Then Set ppApp2 = New PowerPoint.Application
'ppApp2.Visible = True
Set PPPres1 = ppApp1.Presentations.Open("C:\.pptm")
ppApp1.Visible = True
'Set PPPres2 = ppApp2.Presentations.Open("C:\.pptx")
'ppApp2.Visible = True
'opening up workbook with specified name
'Workbooks.Open "C:\.xlsm"
Worksheets("Overall EAD").Activate
'MsgBox Format(Worksheets("z").Cells(4, 4).Value, "0.00%")
'LastRow1 = Worksheets("z").Cells(Rows.Count, "A").End(xlUp).Row
'MsgBox LastRow1
'Search for any entry, by searching backwards by Columns.
'LastColumn1 = Worksheets("z").Cells(8, Columns.Count).End(xlToLeft).Column
'MsgBox LastColumn1
LastRow1 = ActiveSheet.Range("D7").End(xlDown).Row
LastColumn1 = ActiveSheet.Range("E8").End(xlToRight).Column
'LastRow2 = ActiveSheet.Range("AA7").End(xlDown).Row
'LastColumn2 = ActiveSheet.Range("AA8").End(xlToRight).Column
'MsgBox LastRow1
'MsgBox LastColumn1
'This line is showing error, since I cant do this. what's the option?
Worksheets("z").Range("Cells(LastRow1, LastColumn1), Cells(LastRow1, LastColumn1)", "Cells(LastRow1, LastColumn1), Cells(LastRow1, LastColumn1)").Select
'Worksheets("z").Range(Cells(LastRow1, LastColumn1), Cells(LastRow1, LastColumn1)).Select
Selection.Copy
Sheets("sheet1").Activate
Range("j1").Select
ActiveSheet.Paste
Worksheets("sheet1").Range("J1").Activate
'copying the data from excel to powerpoint
PPPres1.Slides(7).Shapes(2).Table.Cell(3, 5).Shape.TextFrame.TextRange.Text = Format(ActiveCell.Value, "0.00%")
'PPPres1.Slides(7).Shapes(2).Table.Cell(3, 4).Shape.TextFrame.TextRange.Text = Format(Worksheets("Bucket Accuracy LGD").Cells(LastRow1, LastColumn1).Value, "0.00%")
'Workbooks.Open "C:\hello.xlsx"
'Worksheets("z").Activate
'Range("G4").Activate
'PPPres.Slides(8).Shapes(2).Table.Cell(2, 4).Shape.TextFrame.TextRange.Text = Format(ActiveCell.Value, "0.00%")
'Clear The Clipboard
Application.CutCopyMode = False
End Sub
***** : i have done that, but the thing is, i have to generalized few things in this code.
for Eg: in this line, im putting up the range "AA7" and so no. there is something that I can do, so that I can copy last cell in a row until blank such that I dont need to hard code it in the program.
'LastRow2 = ActiveSheet.Range("AA7").End(xlDown).Row
'LastColumn2 = ActiveSheet.Range("AA8").End(xlToRight).Column
'MsgBox LastRow1
here is new code:
Sub ExceltoPpt()
Dim ppApp1 As PowerPoint.Application
Dim ppApp2 As PowerPoint.Application
Dim PPSlide1 As PowerPoint.Slide
Dim PPPres1 As PowerPoint.Presentation
Dim oPPShape1 As Object
Dim newslide1 As PowerPoint.SlideRange
Dim tb1 As PowerPoint.Shape
Dim otable1 As Table
Dim iRow1 As Integer
Dim iColumn1 As Integer
Dim myMschart1 As PowerPoint.Chart
Dim LastRow1 As Long
Dim LastColumn1 As Long
Dim LastRow2 As Long
Dim LastColumn2 As Long
Dim LastRow3 As Long
Dim LastColumn3 As Long
Dim val1 As Variant
Dim rng1 As Range, rng2 As Range, rng3 As Range
Dim PPPres2 As PowerPoint.Presentation
'opening up powerpoint "Q3 2014 Basel Model Tracking IEC.pptx"
If ppApp1 Is Nothing Then Set ppApp1 = New PowerPoint.Application
ppApp1.Visible = True
Set PPPres1 = ppApp1.Presentations.Open("C:\Users\kgupt48\Desktop\Data automation\Q4 2014 Basel Model Tracking IEC.pptm")
ppApp1.Visible = True
Worksheets("Overall EAD").Activate
' have to automate this
With Sheets("Overall EAD")
LastRow1 = ActiveSheet.Range("D7").End(xlDown).Row
LastColumn1 = ActiveSheet.Range("E8").End(xlToRight).Column
LastRow2 = ActiveSheet.Range("AB7").End(xlDown).Row
LastColumn2 = ActiveSheet.Range("AB8").End(xlToRight).Column
LastRow3 = ActiveSheet.Range("AV7").End(xlDown).Row
LastColumn3 = ActiveSheet.Range("AV8").End(xlToRight).Column
MsgBox LastRow1
MsgBox LastColumn1
MsgBox LastRow2
MsgBox LastColumn2
MsgBox LastRow3
MsgBox LastColumn3
Set rng1 = .Cells(LastRow1, LastColumn1)
Set rng2 = .Cells(LastRow2, LastColumn2)
Set rng3 = .Cells(LastRow3, LastColumn3)
Set rngunion = Union(rng1, rng2, rng3)
rngunion.Select
Selection.Copy
End With
Sheets("sheet1").Activate
Range("J1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.NumberFormat = "0.00%"
'copying the data from excel to powerpoint- automate value
PPPres1.Slides(8).Shapes(2).Table.Cell(3, 5).Shape.TextFrame.TextRange.Text = Format(Worksheets("Sheet1").Range("J1").Value, "0.00%")
'PPPres1.Slides(7).Shapes(2).Table.Cell(3, 4).Shape.TextFrame.TextRange.Text = Format(Worksheets("Bucket Accuracy LGD").Cells(LastRow1, LastColumn1).Value, "0.00%")
'PPPres.Slides(8).Shapes(2).Table.Cell(2, 4).Shape.TextFrame.TextRange.Text = Format(ActiveCell.Value, "0.00%")
'Clear The Clipboard
Application.CutCopyMode = False
End Sub
Komal Gupta hey, or could you tell me a method so that I can copy last cell value before every blank cell for many rows in a single sheet. I hope you must have understood by the code I just have sent you. I cant hard code the value. or should I save my excel in some other? In short, in any case, i dont want to mention any range, just row and column if possible.
No. Im trying to copy a last cell value from each row where the data is present and then copying that value to another sheet and then from there im copying to ppt slides on a particular table cell
Hi wise owl thank you for great video/ training. I am having a number issues. I am using office 10. My first issue I encounter is when aligning the data in the slide. ppapp.activewindow.selection.shapeRange.align msolignMiddles, msotrue. I can run time error.
Thank you for the response, will I attach it here or will I email and if it email would you have email I can send it to
Hi wise owl thank for your willingness to help. Here is the error I encounter and the code I used.
Run-time error'-2147188160(80048240)':
Selection (unknown member):Invalid request. Nothing appropriate is currently selected.
My Code
Sub CreateNewPresentation()
' This section is were you set variables
Dim ppapp As PowerPoint.Application
Dim ppPres As PowerPoint.Presentation
Dim ppslide As PowerPoint.Slide
' This section is to make Excel aware of Powerpoint
Set ppapp = New PowerPoint.Application
ppapp.Visible = True
ppapp.Activate
'This section is about adding slide, the intro slide and populating each of the text boxes of slide with text data
Set ppPres = ppapp.Presentations.Add
Set ppslide = ppPres.Slides.Add(1, ppLayoutTitle)
' Here the instruction will be to input text into each of the boxes of the slide in Powerpoint
ppslide.Shapes(1).TextFrame.TextRange.Text = "Excel to Powerpoint Demo"
ppslide.Shapes(2).TextFrame.TextRange.Text = "By J"
' This section is to add new blank slide and set range to copy and paste data from Excel into Powerpoint. Plus align to fit slide
Set ppslide = ppPres.Slides.Add(2, ppLayoutBlank)
ppslide.Select
Range("A1").CurrentRegion.Copy
ppslide.Shapes.PasteSpecial ppPasteOLEObject
'This section deals with alignment of the data within the Powerpoint slide
ppslide.Shapes(1).Width = ppPres.PageSetup.SlideWidth
ppslide.Shapes(1).Left = 0
ppapp.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, msoTrue
End Sub
Thank you that worked. Now continue with the rest of the video
Hi wise owl I am down to getting a chart from excel to power point bunt when I follow the instruction -
set ppslide = ppPres.Slide.add(3, ppLayoutBlank)
Ppslide.select
Chart1.chartArea.copy
I get error Method or data member not found
My apologises for all the messages.
Hi
It is slides.add, sorry about the typo in my messages. The debug line is related to chart1.ChartArea.Copy line. I went back to earlier teachings of yours and used a work around based of them instructions. I added in the likes of worksheets(2).select and used this command Activesheets.ChartObjects("Chart 1").Activate
ActiveChart.chartArea.Copy
This works I am not sure why, found it via goggle, as I don't want to be taken advantage of your good nature. But what happens is it does not generate PowerPoint in the order I want. So if I add worksheet(3) using this active Chart command, the slide will appear as slide two and so on.
Hi Andrew,
Thanks for this video.
I have a query related to VBA in PPT, if you can help me with that.
I want to copy the tables from PPT to Excel. I have tried the following code in PPT VBA, its partially working but not the way i want.
- there are 4 shapes on PPT : 2 tables and 2 text box
- following code is copying and pasting everything as picture in Excel
- I want to copy my tables as table in excel so that I can use those tables value for further calculation
----------------------------------------------------------------------------
Sub CopyTablesToExcel()
Dim i As Integer
Dim xlApp As Object
Dim xlWorkBook As Object
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlWorkBook = xlApp.Workbooks.Open("C:\Experiments\Matrix.xlsm", True, False)
For i = 1 To 4
ActivePresentation.Slides(1).Shapes(i).Copy
xlWorkBook.sheets("Sheet2").Cells(2, 9).PasteSpecial ppPasteDefault
Next i
End Sub
------------------------------------------------------------------------------------------
Thanks,
KP
*****
Hi Andrew,
This is perfect, Its really great help. Its working.
Thanks,
KP
Hi,
Do you know how I would code in VBA in order to apply an existing template? the template already has graphs, charts, and texts.
I am trying to automate my reports from excel into powerpoint instead of doing it all manually. I have already created all the charts and I am now just trying to figure out how to create the whole powerpoint presentation through coding so that it is semi-automated.
Please let me know. Also if you could just direct message me, I would need alot of help and if you could aid me in this process, I would be truly grateful.
Thanks!
Thanks! Would you possibly know how to start writing in that opened template?
How would I set the code so that I can Copy and Paste into the powerpoint?
doing ppslide = pppres.slides does not work and also just trying to copy and paste into the slide with ppslide(3) does not work as well. (slide number is 3).
I have also tried a number of different stuff but it did not work. Do you know how to set the code so I can start copying and pasting? Sorry being such an inconvenience but your help would be amazing.
Hey, Thanks for the help. But it says an object required? Do you know anything to fix this?
Thanks you are the best
Hello. I am in Excel 2010, PowerPoint 14 is ticked, around minute 23 you explain ppSlide.Shapes.Paste, and it doesn't work for me, but the pasteOLEObject works fine, I also get the same problem with pasteJPG, PNG and other somple formats. it says "Shapes(unknown member):Invalid request. Clipboard is empty or contains data which may not be pasted here". I checked and it is copying the table, and the table is a copy of yours. Thank you.
19:07