Solved

programmatically set print area

Posted on 2004-09-22
15
332 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 33

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 33

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

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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 3

Author Comment

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

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 33

Expert Comment

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

Expert Comment

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

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
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…

759 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now