Solved

programmatically set print area

Posted on 2004-09-22
15
344 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
ScreenConnect 6.0 Free Trial

Check out the updates in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI that improves session organization and overall user experience. See the enhancements for yourself!

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to debug this code 7 61
Using "ScreenUpdating" 6 64
Publisher:   Unknown     VB.exe Application 1 21
VBA - If Bookmark = "XXBOOKMARKXX" then 15 28
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
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 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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

773 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