Hey, Scripting Guy! How Can I Copy Data from Excel to PowerPoint?

ScriptingGuy1

Hey, Scripting Guy! Question

Hey, Scripting Guy! As part of a demonstration I do on a regular basis, I’d 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

SpacerHey, Scripting Guy! AnswerScript Center

Hey, JG. You know, the Hey, Scripting Guy! column is always written the day before it’s published, which means that today’s 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’s 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’s not going to happen; to tell you the truth, the Scripting Guy who writes this column doesn’t feel much like discussing football, at least not today.

Why not? Because all the Scripting Guys live in the state of Washington, that’s why not.

Note. OK, technically Dean Tsaltas lives in Halifax, Nova Scotia, which we’re pretty sure is not in the state of Washington. However, Dean wouldn’t recognize a football if it hit him in the head, so we can safely ignore his feelings towards the previous weekend’s football games.

And, yes, now that you mention it, we’ve wondered if maybe Dean has been hit in the head with a football a time or two ourselves. That would explain a lot, wouldn’t it?

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:

Been tied with undefeated and top-ranked Ohio State.

Led UCLA, a team that’s currently undefeated in Pac-10 Conference play.

Led 13th-ranked USC.

Led 12th-ranked Arizona State.

And how many of those games have the Huskies gone on to win? Let’s put it this way: we don’t feel much like discussing football, at least not today.

And then there’s the Seattle Seahawks. And don’t worry; we can talk about the Seahawks, despite the fact that we don’t want to talk about football today. After all, whatever the Seahawks are playing these days it’s definitely not football. A week after getting embarrassed by the Pittsburgh Steelers the Seahawks came home and got whipped by New Orleans, a team that hadn’t won a game all year. Thanks to the Seahawks’ 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 ….

And then, just to add insult to injury, even the Washington State Cougars got into the act, being obliterated by Oregon 53-7.

Note. 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 – like all good-hearted people everywhere – never wants to see Oregon win.

Ever.

Of course, that does leave us with one problem: if we don’t talk about football what can we talk about? Well, there’s always the baseball playoffs; how ‘bout those Colorado Rockies, huh? Or maybe we could – hold on a second here ….

Hmmm; the Scripting Editor suggests we talk about how someone (maybe even you) 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’s what everyone talked about at her last family reunion, and everyone had a great time.) We’re a bit skeptical about that (as well as extremely grateful that we’ve never been invited to a Scripting Editor family reunion). But what the heck; let’s give it a try:

Const ppPasteOLEObject = 10
Const ppLayoutBlank = 12

Set objExcel = CreateObject(“Excel.Application”) objExcel.Visible = True

Set objWorkbook = objExcel.Workbooks.Open(“C:\Scripts\Test.xls”)

Set objWorksheet = objWorkbook.Worksheets(1) objWorksheet.Activate

Set objRange = objWorkSheet.UsedRange objRange.Copy

Set objPPT = CreateObject(“PowerPoint.Application”) objPPT.Visible = True

Set objPresentation = objPPT.Presentations.Add Set objSlide = objPresentation.Slides.Add(1,1)

objPresentation.Slides(1).Layout = ppLayoutBlank objPPT.Windows(1).View.PasteSpecial ppPasteOLEObject

objExcel.Quit

OK, let’s 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’ll use ppPasteOLEObject to tell the script that the Clipboard contains OLE data; we’ll use ppLayoutBlank to create a blank slide (i.e., a slide with absolutely nothing on it.)

After we define the two constants we create an instance of the Excel.Application object and then set the Visible property to True; that gives us a running instance of Microsoft Excel that we see onscreen. We next use the Open 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:

Set objWorksheet = objWorkbook.Worksheets(1)
objWorksheet.Activate

Once we’ve 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:

Set objRange = objWorkSheet.UsedRange
objRange.Copy

In the first line, we’re simply creating an instance of Excel’s Range object, a range that encompasses all the data on the worksheet. How do we know that this range encompasses all the data on the worksheet? That’s easy; after all, we set the range equal to the worksheet’s UsedRange 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.

In other words, the complete range of cells that have been used to hold data.

Once we’ve defined our range we simply call the Copy method in order to copy that data to the Clipboard.

Now it’s time to turn our attention to PowerPoint. For starters, we create an instance of the PowerPoint.Application object and then set its Visible 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:

Set objPresentation = objPPT.Presentations.Add
Set objSlide = objPresentation.Slides.Add(1,1)

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’s Layout property to the value of the constant ppLayoutBlank:

objPresentation.Slides(1).Layout = ppLayoutBlank

At that point we simply need to call the PasteSpecial method followed by the constant ppPasteOLEObject:

objPPT.Windows(1).View.PasteSpecial ppPasteOLEObject

Note. Note that the PasteSpecial method actually belongs to the View object, which is a child object of the Windows collection, which, in turn, is a child object of the PowerPoint Application object. (Whew!) That’s why we used the syntax objPPT.Windows(1).View.PasteSpecial.

Oh, and in case you’re wondering, the 1 simply means that we’re talking about the first (and, for this script, the only) PowerPoint window.

After we paste in the data we then call the Quit method to dismiss our instance of Excel. It’s a good idea not to get rid of Excel until you’ve pasted in the data; otherwise you might get an error telling you that no data could actually be found on the Clipboard.

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’ll start up an instance of Excel right inside your slide presentation.) If you’d rather just have the data without dynamically linking to Excel then simply leave off the parameter when calling the PasteSpecial method:

objPPT.Windows(1).View.PasteSpecial

Try it both ways and see which one you like better.

In the meantime, don’t worry about the Scripting Guy who writes this column: he’s already gearing up for this week’s football games, including Washington vs. Oregon. Will the Huskies upset the Ducks on Saturday? You’ll find out next Tuesday; if Tuesday’s 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’t feel like discussing football.

Well, either that or he actually did get invited to a Scripting Editor family reunion.

0 comments

Discussion is closed.

Feedback usabilla icon