?
Solved

programmatically set print area

Posted on 2004-09-22
15
Medium Priority
?
451 Views
Last Modified: 2008-02-01
how can i programmatically set the print area of a worksheet only including cells that is not empty, so that when i print the file blank pages would be eliminated?
0
Comment
Question by:4ceReconSniper
  • 7
  • 4
12 Comments
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 12130380
Provided the workbook is open:

Dim xlApp As Excel.Application
Dim wkbk As Excel.Workbook
Dim ws As Excel.Worksheet

Set xlApp = GetObject(,"Excel.Application")
Set wkbk = wkbk.Workbooks("C:\File1.xls")
Set ws = wkbk.Worksheetd("Sheet1")
With ws.PageSetup
 .PrintArea = "A1:D16"
End With

Set xlApp = Nothing
Set wkbk = Nothing
Set ws = Nothing

Mike

0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 12130393
Correction...

 .PrintArea = .Range("A1").SpecialCells(xlCellTypeLastCell)


0
 
LVL 3

Author Comment

by:4ceReconSniper
ID: 12130520
kindly explain your program?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 3

Author Comment

by:4ceReconSniper
ID: 12130634
Also for other experts interested helping me

can it handle this situation?

i have an excel file, worksheet one, cell A1 to A10, cell B1 to B10, cell C1 to C10, cell E11 to E20, cell F11 to F20, cell H1 to H5 has something in it,

manually i would select by dragging from A1 to C10 then press control while dragging H1 to H5, then holding control while dragging E11 to F20, therefore i got 3 highlighted areas and i go to File/print area/set print area. now i have those cells ready for printing and i didnt printed the blank cells. I would like to do it programmatically in the program
0
 
LVL 26

Expert Comment

by:EDDYKT
ID: 12132196
Have you tried to use record the macro from tools->macro menu?


After you have finished, press Alt-F11 to see code
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 12133382
The code posted is to be executed from a vb environment (not from excel, with excel it needs little modification):

Dim xlApp As Excel.Application                 '<-- defines variables
Dim wkbk As Excel.Workbook
Dim ws As Excel.Worksheet

Set xlApp = GetObject(,"Excel.Application")      '<-- set xlApp (Excel Application)
Set wkbk = wkbk.Workbooks("C:\File1.xls")     '<-- set wkbk (workbook type)
Set ws = wkbk.Worksheetd("Sheet1")             '<-- set wkbk (worksheet type)

With ws.PageSetup
 .PrintArea = .Range("A1").SpecialCells(xlCellTypeLastCell)   '<-- from A1 to last cell containing somthing
 <now, we need to add a code to makes the blank columns invisible>  
End With

Set xlApp = Nothing                  <-- release the variables
Set wkbk = Nothing
Set ws = Nothing

Mike
0
 
LVL 3

Author Comment

by:4ceReconSniper
ID: 12148796
yeah how do i eliminate them in code
0
 
LVL 34

Accepted Solution

by:
Mike Eghtebas earned 200 total points
ID: 12151869
My appology for delay.  I wasn't sure about that step so I used macro recorder to come up with the following solution.  Please try it to see if it would work.  Have you ever tried macro recorder to produce codes?  Here is the code:

Dim xlApp As Excel.Application                 '<-- defines variables
Dim wkbk As Excel.Workbook
Dim ws As Excel.Worksheet

Set xlApp = GetObject(,"Excel.Application")      '<-- set xlApp (Excel Application)
Set wkbk = wkbk.Workbooks("C:\File1.xls")     '<-- set wkbk (workbook type)
Set ws = wkbk.Worksheetd("Sheet1")             '<-- set wkbk (worksheet type)

With ws.PageSetup

    .PrintArea = .Range("A1").SpecialCells(xlCellTypeLastCell)   '<-- from A1 to last cell containing somthing
   
    Columns("E:E").Select: Selection.EntireColumn.Hidden = True    '<-- this hides column E only
    Columns("H:I").Select: Selection.EntireColumn.Hidden = True    '<-- this hides columns H through I
 
End With

Set xlApp = Nothing                  <-- release the variables
Set wkbk = Nothing
Set ws = Nothing

Also, post another question how to hide bland columns in an excel application.  Code will determine last cell column and row first and then look to see what clomns are blank to hide them.  If I had the time, I will take part in the answer.

Regards,

Mike
0
 
LVL 3

Author Comment

by:4ceReconSniper
ID: 12293994
eghtebas - you didnt answer the question but your code helped me a little and you've been attending to my question always for that i would give you 100 pts from the original point value
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 12294015
Thank you.
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 12294033
EE is a great place to lean.  Happy to be here.
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 12294037
learn (how to write and spell).  LOL

I have lean instead of learn in the last post.

Mike
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question