[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

programmatically set print area

Posted on 2004-09-22
15
Medium Priority
?
419 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
15 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
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.

 
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

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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

650 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