How to set Print_Area to NULL or Nothing?

[ronwessel]

I have a macro that cycles thru Excel sheets and performs a print. Some sheets need only certain areas printed, and some sheets do NOT NEED PRINTED AT ALL.
The first portion of this is easy enough to handle by using the "Print_Area" name set to the areas to be printed, however, I cannot figure out how to
suppress printing on the sheet entirely. For example, if I define "Print_Area" as NULL, TRUE, FALSE or even a "nothing range" such as =OFFSET(Sheet1!$A$1,0,0,0,0), the
ENTIRE sheet is printed. It would be valuable if the PRINT_AREA could be set to SUPPRESS printing, can this be done?

 

[shg]

Why not just not print the sheet?

 

[ronwessel]

Almost all complex Excel files have "intermediate calculations", or "secondary information" that is not desired for printout. If Excel is going to allow a "Print_Area" to "step over" these areas of secondary importance (for printing), then why not allow the entire sheet to be indicated as "don't print anything". Seems perfectly logical to me that if "Print_Area" were null, or set to something like =OFFSET(Sheet1!$A$1,0,0,0,0), it would indicate that nothing should be printed. If Excel is going to give users a feature like Print_Area, and then why in the world do they not take it to the logical conclusion such that if Print_Area is empty, then don't print anything. That would be logical.

 

[shg]

If print_area is a null string, Excel prints the used range.

 

[ronwessel]

Exactly, which is NOT desirable. How do you suppress the printing? That is my original question.

 

[mikerickson]

The counter question is Why have such a setting.
One has to press Print for each sheet being printed. If you don't want to print a sheet, don't press Print.
If you do accidentaly press Print, press Cancel on the preview screen.

Or are you asking for a Print All Sheet routine that skips specified sheets?

 

[ronwessel]

Excel should have such a setting because the real-world is NOT always driven by people "clicking things".

For example, the following VBS routine will allow an Excel file to be printed from the command-prompt. Problem is, the "oWkbk.PrintOut" line causes all sheets to be printed UNLESS THEY ARE HIDDEN or EMPTY. I would like to set the Print_Area to NULL, in order to control which sheets will NOT print at all.

Now, I don't intend to be rude, but if someone has a solution to my problem, please submit it. Otherwise, please refrain from telling me that I shouldn't really need what I have requested, or telling me what I already know. Many thanks for trying though.


Const xlDoNotSaveChanges = False

Dim fso, oXL, oWkbk

Set fso = CreateObject("Scripting.FileSystemObject")
Set oXL = CreateObject("Excel.Application")

oXL.Visible = False

If WScript.Arguments.Count = 0 Then
WScript.Quit
Else
For A = 0 To (WScript.Arguments.Count - 1)
If fso.FileExists(WScript.Arguments.Item(A)) Then
Set oWkbk = oXL.Workbooks.Open(WScript.Arguments.Item(A))
oWkbk.PrintOut
oWkbk.Close xlDoNotSaveChanges
End If
Next
End If

oXL.Quit
Set fso = Nothing
Set oXL = Nothing

 

[ChrisGT7]

If I am not mistaken, the PageSetup.PrintArea is not responsible for canceling the printing. Here is the Microsoft Help for this property and as shg wrote above, by setting it to False or "" the entire worksheet will be printed.

I believe there are some solutions to your problem but not with the way you are trying to solve it. Better try using the Workbook_BeforePrint event or if you insist to use PrintArea, then set it to False or "" and with a If..Then..Else you could check the validation of PrintArea.

 

[ronwessel]

The problem is, a mass number of files are being processed, and I do not have the luxury of opening each and populating a Workbook_BeforePrint event into them all. Again, the most logical would be someway that I could simply control which sheets get printed thru the facility that ALREADY EXISTS, namely the Print_Area. If Print_Area set to a range throttles the print to that area, then why not set Print_Area to NULL, and have it suppress printing altogether? Seems very logical to me, but apparently Microsoft didn't see it this way.

 

[mikerickson]

You could set an Application level WorkbookBeforePrint event that would effect every workbook that is opened, without having to code for particular workbook.

But then you would have to add a "do not print this page" signal into every worksheet that you don't want printed.