Solved

programmatically set print area

Posted on 2004-09-22
15
351 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
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
Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

 
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 100 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

831 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