{"id":64503,"date":"2007-07-07T00:37:00","date_gmt":"2007-07-07T00:37:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2007\/07\/07\/how-can-i-set-the-print-area-in-an-excel-spreadsheet\/"},"modified":"2007-07-07T00:37:00","modified_gmt":"2007-07-07T00:37:00","slug":"how-can-i-set-the-print-area-in-an-excel-spreadsheet","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/how-can-i-set-the-print-area-in-an-excel-spreadsheet\/","title":{"rendered":"How Can I Set the Print Area in an Excel Spreadsheet?"},"content":{"rendered":"

\"Hey, \n

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

— PRP <\/P>\"Spacer\"\"Hey,\"Script<\/A> \n

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!<\/I> column. That\u2019s because he didn\u2019t want to make any work-related commitments that he wouldn\u2019t be able to live up to. <\/P>\n\n<\/THEAD>\n\n\n
\n

Note<\/B>. Not that the Scripting Guy who writes this column has ever<\/I> made a work-related commitment that he wasn\u2019t able to live up to, mind you.<\/P>\n

Regardless of what the Scripting Editor might tell you.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n

<\/DIV>\n

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 \u2026.) <\/P>\n

On top of that, sometimes the Scripting Car works and sometimes it doesn\u2019t; 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\u2019s the deal: you can\u2019t really break a promise if you never actually made<\/I> that promise, now can you? With that in mind, the Scripting Guy who writes this column feels only a little<\/I> guilty about the following script, one that sets the print area in an Excel spreadsheet:<\/P>

Set objExcel = CreateObject(“Excel.Application”)\nobjExcel.Visible = True<\/p>\n
Set objWorkbook = objExcel.Workbooks.Add()\nSet objWorksheet = objWorkbook.Worksheets(1)<\/p>\n
For x = 1 to 10\n    For y = 1 to 10\n        objExcel.Cells(x, y).Value = x + y\n    Next\nNext<\/p>\n
objWorksheet.PageSetup.PrintArea = “B2:D4”\n<\/PRE>\n
As you can see, this isn\u2019t the most complicated script in the world. In fact, the bulk of the script doesn\u2019t 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. <\/P>\n
To begin with, we create an instance of the Excel.Application <\/B>object and then set the Visible<\/B> 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:<\/P>
Set objWorkbook = objExcel.Workbooks.Add()\nSet objWorksheet = objWorkbook.Worksheets(1)\n<\/PRE>\n
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\u2019t get a workbook or any worksheets. This might simply be a personal preference on the part of the Scripting Guys, but we don\u2019t think there\u2019s much fun in having an instance of Excel unless you also have a worksheet or two to play with.<\/P>\n
Once we do<\/I> have a workbook and once we do<\/I> bind to the first worksheet in that workbook we then use this block of code to add a little data to the pot:<\/P>
For x = 1 to 10\n    For y = 1 to 10\n        objExcel.Cells(x, y).Value = x + y\n    Next\nNext\n<\/PRE>\n
This really isn\u2019t that big a deal; if you run the script you should be able to figure out what it does and how it does that.<\/P>\n
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:<\/P>
objWorksheet.PageSetup.PrintArea = “B2:D4”\n<\/PRE>\n
You can see why we felt a little guilty about today\u2019s 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<\/B> property, a property of the PageSetup<\/B> object (which happens to be a child object of the Excel Worksheet<\/B> object). The only thing even remotely tricky about this line of code is the need to use the so-called \u201cA1-style\u201d cell addresses; note that we set the print area to B2:D4<\/B> and not something like objExcel.Cells(2, 2):objExcel.Cells(4, 4)<\/B>. Depending on what you\u2019re doing that could be a little tricky; by default you typically don\u2019t work with A1-style addresses in a script. Fortunately, though, there\u2019s an alternate way to specify a print area. If you\u2019ve assigned a Range<\/B> in your spreadsheet you can then specify the print area using code like this:<\/P>
Set objRange = objExcel.Range(“B2:D4”)\nobjWorksheet.PageSetup.PrintArea = objRange.Address\n<\/PRE>\n
Needless to say, we didn\u2019t really knock ourselves out with this bit of code, either: after creating a range we then assign the value of the range\u2019s Address<\/B> property as the print area. As you\u2019ve probably already guessed, the Address property gives us A1-type cell addresses.<\/P>\n
Which, by the way, is a handy little tip to keep in mind.<\/P>\n
That\u2019s 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:<\/P>
objWorksheet.PageSetup.PrintArea = “”\n<\/PRE>\n
We hope that answers your question, PRP; it better, because it\u2019s just about time to pack up and head for the baseball game. (And, for once at least, it looks like the weather\u2019s going to be absolutely gorgeous.) As a general rule, this is the time we\u2019d likely vow to work a little harder and put in a little more effort on the next column. Unfortunately, though, we can\u2019t do that. After all, we did<\/I> make a sacred vow never to make a sacred vow. And it just wouldn\u2019t be like the Scripting Guy who writes this column not to live up to his commitments.<\/P>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. Yes, we know: the Script Center is filled-to-overflowing with promises the Scripting Guy who writes this column made but \u2013 for various reasons \u2013 was never able to keep. But don\u2019t worry: you don\u2019t have to write in and point these out to him. The Scripting Editor \u2013 who never nags, by the way \u2013 continually \u2026 reminds \u2026 him of these things.<\/P>\n
Over and over and over again \u2026.<\/P><\/TD><\/TR><\/TBODY><\/TABLE><\/p>\n","protected":false},"excerpt":{"rendered":"
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, […]<\/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,49,3,5],"class_list":["post-64503","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-excel-spreadsheet","tag-microsoft-excel","tag-office","tag-scripting-guy","tag-vbscript"],"acf":[],"blog_post_summary":"
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, […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/64503","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=64503"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/64503\/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=64503"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=64503"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=64503"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}