How Can I Set the Print Area in an Excel Spreadsheet?

ScriptingGuy1

Hey, Scripting Guy! How can I set the print area in an Excel spreadsheet?

— PRP

Hey, PRP. You know, before the Scripting Guy who writes this column wrote his very first column he took a sacred vow: he vowed never to take a sacred vow, at least not when it came to writing the Hey, Scripting Guy! column. That’s because he didn’t want to make any work-related commitments that he wouldn’t be able to live up to.

At any rate, had he chosen to take such a vow the Scripting Guy who writes this column would likely have vowed to never take shortcuts; to always work extremely hard when answering a question; to never take, say, 3 minutes to write a script and then try to pass that off as a full column. In his defense, he usually tries to live up to those ideals anyway. But sometimes the Scripting Son plays in a baseball tournament, and the Scripting Guy who writes this column needs to sneak out of the office by 11:00 AM or so if he wants to attend the game. (Does that mean he came in at 3:00 AM in order to put in a full 8-hour day? Well, kind of ….)

On top of that, sometimes the Scripting Car works and sometimes it doesn’t; that means the Scripting Guy who writes this column has to deal with mechanics, weigh the pros and cons of buying a new car, and has to venture out of Scripting Guys headquarters to actually look at new cars. And then there are the weeds that need to be pulled, the doughnuts that need to be bought and, well, here’s the deal: you can’t really break a promise if you never actually made that promise, now can you? With that in mind, the Scripting Guy who writes this column feels only a little guilty about the following script, one that sets the print area in an Excel spreadsheet:

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

Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)
For x = 1 to 10
    For y = 1 to 10
        objExcel.Cells(x, y).Value = x + y
    Next
Next
objWorksheet.PageSetup.PrintArea = “B2:D4”

As you can see, this isn’t the most complicated script in the world. In fact, the bulk of the script doesn’t even have anything to do with setting the print area; that code is there simply to add some data to the spreadsheet, making it a little more obvious what it means to set the print area.

To begin with, we create an instance of the Excel.Application object and then set the Visible property to True; long-time readers of this column know that this simply gives us a running instance of Excel that we can view onscreen. We then use these two lines of code to add a new workbook to our instance of Excel, and then to bind us to the first worksheet in that workbook:

Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)

And yes, those two lines of code are very important: when you programmatically create an instance of Excel all you get is an instance of Excel; you don’t get a workbook or any worksheets. This might simply be a personal preference on the part of the Scripting Guys, but we don’t think there’s much fun in having an instance of Excel unless you also have a worksheet or two to play with.

Once we do have a workbook and once we do bind to the first worksheet in that workbook we then use this block of code to add a little data to the pot:

For x = 1 to 10
    For y = 1 to 10
        objExcel.Cells(x, y).Value = x + y
    Next
Next

This really isn’t that big a deal; if you run the script you should be able to figure out what it does and how it does that.

So now that we have some data on our worksheet how do we go about assigning a print area to that worksheet? Why, like this, of course:

objWorksheet.PageSetup.PrintArea = “B2:D4”

You can see why we felt a little guilty about today’s column; after all, it takes only a single line of code to set the print area. All we have to do is assign a value to the PrintArea property, a property of the PageSetup object (which happens to be a child object of the Excel Worksheet object). The only thing even remotely tricky about this line of code is the need to use the so-called “A1-style” cell addresses; note that we set the print area to B2:D4 and not something like objExcel.Cells(2, 2):objExcel.Cells(4, 4). Depending on what you’re doing that could be a little tricky; by default you typically don’t work with A1-style addresses in a script. Fortunately, though, there’s an alternate way to specify a print area. If you’ve assigned a Range in your spreadsheet you can then specify the print area using code like this:

Set objRange = objExcel.Range(“B2:D4”)
objWorksheet.PageSetup.PrintArea = objRange.Address

Needless to say, we didn’t really knock ourselves out with this bit of code, either: after creating a range we then assign the value of the range’s Address property as the print area. As you’ve probably already guessed, the Address property gives us A1-type cell addresses.

Which, by the way, is a handy little tip to keep in mind.

That’s all we need to do. If you later decide to remove the print area simply set the value of the PrintArea property to an empty string, like so:

objWorksheet.PageSetup.PrintArea = “”

We hope that answers your question, PRP; it better, because it’s just about time to pack up and head for the baseball game. (And, for once at least, it looks like the weather’s going to be absolutely gorgeous.) As a general rule, this is the time we’d likely vow to work a little harder and put in a little more effort on the next column. Unfortunately, though, we can’t do that. After all, we did make a sacred vow never to make a sacred vow. And it just wouldn’t be like the Scripting Guy who writes this column not to live up to his commitments.

ScriptingGuy1

Follow