{"id":63793,"date":"2007-10-17T02:44:00","date_gmt":"2007-10-17T02:44:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2007\/10\/17\/hey-scripting-guy-how-can-i-copy-data-from-excel-to-powerpoint\/"},"modified":"2007-10-17T02:44:00","modified_gmt":"2007-10-17T02:44:00","slug":"hey-scripting-guy-how-can-i-copy-data-from-excel-to-powerpoint","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/hey-scripting-guy-how-can-i-copy-data-from-excel-to-powerpoint\/","title":{"rendered":"Hey, Scripting Guy! How Can I Copy Data from Excel to PowerPoint?"},"content":{"rendered":"

\"Hey, \n

Hey, Scripting Guy! As part of a demonstration I do on a regular basis, I\u2019d like to be able to open an Excel spreadsheet, copy all the information on Sheet 1, and then paste that data into a new PowerPoint slide. How can I do all that using a script?
— JG<\/P>\"Spacer\"\"Hey,\"Script<\/A> \n

Hey, JG. You know, the Hey, Scripting Guy!<\/I> column is always written the day before it\u2019s published, which means that today\u2019s column is actually being written on a Monday. Seeing as how this is a Monday smack-dab in the middle of football season, you might expect today\u2019s column to start off by discussing some of the more exciting football games from the previous weekend. (And there were a bunch: Kentucky beating LSU; Boise State defeating Nevada 69-67; Oregon State knocking off Cal; etc.) But that\u2019s not going to happen; to tell you the truth, the Scripting Guy who writes this column doesn\u2019t feel much like discussing football, at least not today.<\/P>\n

Why not? Because all the Scripting Guys live in the state of Washington, that\u2019s why not.<\/P>\n\n<\/THEAD>\n\n\n
\n

Note<\/B>. OK, technically Dean Tsaltas lives in Halifax, Nova Scotia, which we\u2019re pretty sure is not<\/I> in the state of Washington. However, Dean wouldn\u2019t recognize a football if it hit him in the head, so we can safely ignore his feelings towards the previous weekend\u2019s football games.<\/P>\n

And, yes, now that you mention it, we\u2019ve wondered if maybe Dean has<\/I> been hit in the head with a football a time or two ourselves. That would<\/I> explain a lot, wouldn\u2019t it?<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n

<\/DIV>\n

What was so bad about this past weekend? Well, for starters, the Washington Huskies led undefeated Arizona State 17-13 at the half, only to be completely blown out in the third quarter. At halftime of their past four games the Huskies have:<\/P>\n\n\n\n\n\n\n
\u2022<\/TD>\n\n

Been tied with undefeated and top-ranked Ohio State.<\/P><\/TD><\/TR>\n

\u2022<\/TD>\n\n

Led UCLA, a team that\u2019s currently undefeated in Pac-10 Conference play. <\/P><\/TD><\/TR>\n

\u2022<\/TD>\n\n

Led 13th-ranked USC.<\/P><\/TD><\/TR>\n

\u2022<\/TD>\n\n

Led 12th-ranked Arizona State.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n

And how many of those games have the Huskies gone on to win? Let\u2019s put it this way: we don\u2019t feel much like discussing football, at least not today.<\/P>\n

And then there\u2019s the Seattle Seahawks. And don\u2019t worry; we can talk about the Seahawks, despite the fact that we don\u2019t want to talk about football today. After all, whatever the Seahawks are playing these days it\u2019s definitely not<\/I> football. A week after getting embarrassed by the Pittsburgh Steelers the Seahawks came home and got whipped by New Orleans, a team that hadn\u2019t won a game all year. Thanks to the Seahawks\u2019 generosity, there are only two winless teams left in the NFL: St. Louis and Miami. And seeing as how St. Louis gets to play the Seahawks next week, well \u2026.<\/P>\n

And then, just to add insult to injury, even the Washington State Cougars got into the act, being obliterated by Oregon 53-7.<\/P>\n\n<\/THEAD>\n\n\n
\n

Note<\/B>. OK, admittedly, the Scripting Guy who writes this column rarely sheds a tear when Washington State loses. But the Scripting Guy who writes this column \u2013 like all good-hearted people everywhere \u2013 never<\/I> wants to see Oregon win.<\/P>\n

Ever.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n

<\/DIV>\n

Of course, that does leave us with one problem: if we don\u2019t talk about football what can<\/I> we talk about? Well, there\u2019s always the baseball playoffs; how \u2018bout those Colorado Rockies, huh? Or maybe we could \u2013 hold on a second here \u2026.<\/P>\n

Hmmm; the Scripting Editor suggests we talk about how someone (maybe even you<\/I>) could write a script that can copy data from a Microsoft Excel spreadsheet and then paste that data into a Microsoft PowerPoint slide. (She said that\u2019s what everyone talked about at her last family reunion, and everyone had a great<\/I> time.) We\u2019re a bit skeptical about that (as well as extremely grateful that we\u2019ve never been invited to a Scripting Editor family reunion). But what the heck; let\u2019s give it a try:<\/P>

Const ppPasteOLEObject = 10\nConst ppLayoutBlank = 12<\/p>\n
Set objExcel = CreateObject(“Excel.Application”)\nobjExcel.Visible = True<\/p>\n
Set objWorkbook = objExcel.Workbooks.Open(“C:\\Scripts\\Test.xls”)<\/p>\n
Set objWorksheet = objWorkbook.Worksheets(1)\nobjWorksheet.Activate<\/p>\n
Set objRange = objWorkSheet.UsedRange\nobjRange.Copy<\/p>\n
Set objPPT = CreateObject(“PowerPoint.Application”)\nobjPPT.Visible = True<\/p>\n
Set objPresentation = objPPT.Presentations.Add\nSet objSlide = objPresentation.Slides.Add(1,1)<\/p>\n
objPresentation.Slides(1).Layout = ppLayoutBlank\nobjPPT.Windows(1).View.PasteSpecial ppPasteOLEObject<\/p>\n
objExcel.Quit\n<\/PRE>\n
OK, let\u2019s see if we can figure out how this script works. (Might as well; we have nothing else to do today.) To begin with, we define a pair of constants, ppPasteOLEObject and ppLayoutBlank. We\u2019ll use ppPasteOLEObject to tell the script that the Clipboard contains OLE data; we\u2019ll use ppLayoutBlank to create a blank slide (i.e., a slide with absolutely nothing on it.)<\/P>\n
After we define the two constants we create an instance of the Excel.Application<\/B> object and then set the Visible<\/B> property to True; that gives us a running instance of Microsoft Excel that we see onscreen. We next use the Open<\/B> method to open the file C:\\Scripts\\Test.xls, then use these two lines of code to bind to and activate the first worksheet in the file:<\/P>
Set objWorksheet = objWorkbook.Worksheets(1)\nobjWorksheet.Activate\n<\/PRE>\n
Once we\u2019ve made the connection to the worksheet our next step is to copy all the information (both the data and the formatting) to the Clipboard. Believe it or not, that requires just two little lines of code:<\/P>
Set objRange = objWorkSheet.UsedRange\nobjRange.Copy\n<\/PRE>\n
In the first line, we\u2019re simply creating an instance of Excel\u2019s Range<\/B> object, a range that encompasses all the data on the worksheet. How do we know<\/I> that this range encompasses all the data on the worksheet? That\u2019s easy; after all, we set the range equal to the worksheet\u2019s UsedRange<\/B> property. As you might recall from previous columns, the UsedRange starts in the first cell that has data in it, ends in the last cell that has data in it, and includes all the cells in-between those two endpoints.<\/P>\n
In other words, the complete range of cells that have been used to hold data.<\/P>\n
Once we\u2019ve defined our range we simply call the Copy<\/B> method in order to copy that data to the Clipboard.<\/P>\n
Now it\u2019s time to turn our attention to PowerPoint. For starters, we create an instance of the PowerPoint.Application<\/B> object and then set its Visible<\/B> property to True; now we have instances of both PowerPoint and Excel running onscreen. We then use these two lines of code to create a new presentation, and to add a new slide to that presentation:<\/P>
Set objPresentation = objPPT.Presentations.Add\nSet objSlide = objPresentation.Slides.Add(1,1)\n<\/PRE>\n
And because we decided (just for the heck of it) to make this first slide a blank slide, we use the following line of code to set the slide\u2019s Layout<\/B> property to the value of the constant ppLayoutBlank:<\/P>
objPresentation.Slides(1).Layout = ppLayoutBlank\n<\/PRE>\n
At that point we simply need to call the PasteSpecial<\/B> method followed by the constant ppPasteOLEObject:<\/P>
objPPT.Windows(1).View.PasteSpecial ppPasteOLEObject\n<\/PRE>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. Note that the PasteSpecial method actually belongs to the View<\/B> object, which is a child object of the Windows<\/B> collection, which, in turn, is a child object of the PowerPoint Application object. (Whew!) That\u2019s why we used the syntax objPPT.Windows(1).View.PasteSpecial<\/B>.<\/P>\n
Oh, and in case you\u2019re wondering, the 1<\/B> simply means that we\u2019re talking about the first (and, for this script, the only) PowerPoint window.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n
After we paste in the data we then call the Quit<\/B> method to dismiss our instance of Excel. It\u2019s a good idea not to get rid of Excel until you\u2019ve pasted in the data; otherwise you might get an error telling you that no data could actually be found on the Clipboard.<\/P>\n
Incidentally, this script pastes the Excel data in as an OLE-embedded object, just like the constant name ppPasteOLEObject implies. (Double-click on the pasted-in data and you\u2019ll start up an instance of Excel right inside your slide presentation.) If you\u2019d rather just have the data without dynamically linking to Excel then simply leave off the parameter when calling the PasteSpecial method:<\/P>
objPPT.Windows(1).View.PasteSpecial\n<\/PRE>\n
Try it both ways and see which one you like better.<\/P>\n
In the meantime, don\u2019t worry about the Scripting Guy who writes this column: he\u2019s already gearing up for this week\u2019s football games, including Washington vs. Oregon. Will the Huskies upset the Ducks on Saturday? You\u2019ll find out next Tuesday; if Tuesday\u2019s column is about system administration scripting, that can only mean one thing: Washington lost, and once again the Scripting Guy who writes this column doesn\u2019t feel like discussing football.<\/P>\n
Well, either that or he actually did<\/I> get invited to a Scripting Editor family reunion.<\/P><\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! As part of a demonstration I do on a regular basis, I\u2019d like to be able to open an Excel spreadsheet, copy all the information on Sheet 1, and then paste that data into a new PowerPoint slide. How can I do all that using a script?— JG Hey, JG. You know, […]<\/p>\n","protected":false},"author":595,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[710,48,129,49,3,5],"class_list":["post-63793","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-excel-spreadsheet","tag-microsoft-excel","tag-microsoft-powerpoint","tag-office","tag-scripting-guy","tag-vbscript"],"acf":[],"blog_post_summary":"
Hey, Scripting Guy! As part of a demonstration I do on a regular basis, I\u2019d like to be able to open an Excel spreadsheet, copy all the information on Sheet 1, and then paste that data into a new PowerPoint slide. How can I do all that using a script?— JG Hey, JG. You know, […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/63793","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/595"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=63793"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/63793\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=63793"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=63793"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=63793"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}