• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Switching Print Preview on and off in VBA

T

terrydennis

Member
Apologies if this subject has been dealt with before. It appears to me that the website does not have a search facility. Am I wrong ?


I have wanted to produce a macro within a spreadsheet which prints out the sheet without using the print preview which is ticked as a default. Having used the macro generator within Excel , the result is that the Print Preview IS switched off but you do not seem to be able to switch it back on again by using the macro generator !


Anyone got any thoughts on this one ?
 
Search function is located in top-right portion of the window. =)


The two choices for command lines could be:

[pre]
Code: 
'This line prints the worksheet w/ preview
ActiveSheet.PrintOut preview:=True

'This line prints the worksheet w/o preview
ActiveSheet.PrintOut preview:=False
[/pre]
For more info on PrintOut, click on the PrintOut word (in the VBE) and hit F1 to bring up the help dialogue.
 
Luke


Used your suggested line in conjunction what I had already which worked before , but the combination doesn't seem to work now.


Sub Print_Share_Dealing()

'

' Print_Share_Dealing Macro

'

ActiveSheet.PrintOut preview:=False

ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"


Range("A1").Select


End Sub


I also did not understand what you meant by the following , "For more info on PrintOut, click on the PrintOut word (in the VBE) and hit F1 to bring up the help dialogue. "
 
Hi, terrydennis!


About more info on PrintOut, do this:

- start Excel

- open the workbook

- go to the VBA editor (Alt-F11)

- go to the bottom right pane - Immediate Windows (if not visible, Ctrl-G first)

- then type "printout" without quotes

- click with the mouse over what typed (at any letter) or move left with left arrow key

- press F1


There you'll get the built-in Excel help.


Regards!
 
Thanks for that info. Something else I did not know nor have used !


Unfortunately , I am still no nearer resolving my problem.


I want to print out the current worksheet WITHOUT a PREVIEW and then re-set the default back to WITH PREVIEW.


My current coding is


Sub Print_preview_test()

ActiveSheet.PrintOut preview:=False

ActiveSheet.PrintOut


End Sub


What happens is that there is a Preview and after pressing Print ANOTHER preview appears to print ANOTHER copy.


What on earth is happening and how do I achieve my objective ?
 
Hi terrydennis,


ActiveSheet.PrintOut command is used to prinout the current sheet.

If you used the command two times, it will print two times.


if you used ActiveSheet.PrintOut without any parameter, it will never never ask you any other information, reagrding printing. exp.


* want to see Preview

* Which Printer

* print to file or printer.


if you need to only preview use ActiveSheet.PrintPreview

and if you want only print withoutpriew or with preview use ActiveSheet.PrintOut preview:=True / False


Dont use twice the line PrintOut. Please use two different button / macro, one for preview and one for Print. It will not change your any default setting. :)
 
Thanks for your reply but , having tried what you have said .... and having used ActiveSheet.Print preview :=False before ..... the instruction will not work. It shows a preview and required you to press 'Print' which I don't want ! Am I missing something ?


Have you tried it yourself ? If so , and it works , exactly what subroutine would you use to printout the current worksheet without a preview being shown?


Thanks
 
Hi terrydennis,

* Are you using Excel 2010?

* can you please check the below for me.

go to vba editior (Alt + F11)

in immediate window (press Ctrl + G) write

Code: 
? application.ActivePrinter

* if it return any printer's name then wrte again in the same window..

[code]? Applicaton.PrintCommunication

* if it gives you TRUE, then set it to FALSE , just write in the same Immediate Window..

Application.PrintCommunication = FALSE

then write again.. in the same immediate window

Activesheet.PrintOut[/code]

If it still ask you to press the PRINT button ..

please be patient.. someone from this site will surely comeback to you.. :(
 
I am using Excel 2007.


After I had typed in ? Application.ActivePrinter and pressed 'Return' , it gave Canon iP4900 on Ne02


Typing in ? Application.PrintCommunication and pressing 'Return' gave nothing apart from an error message

Runtime error '438'

Object does not support this property or method


Where do I go now ?
 
Hi, terrydennis!


Sorry for arriving at desserts time. I read your original question and totally agree with Luke M's suggestion. In fact, I've just tried it and it works for me: a copy is sent to the printer and immediately a preview is display at the screen. If that's what you were looking for, this is the code:

-----

[pre]
Code: 
Option Explicit

Sub UndecidedPreviewPrinting()
ActiveSheet.PrintOut Preview:=False
ActiveSheet.PrintOut Preview:=True
End Sub
[/pre]
-----


Regards!
 
WEll , I'm afraid it doesn't work for me .... in fact I get TWO previews as I will try and show with a PrintScreen. I get NO printout AT ALL.


I am running Excel 2007 and I would ask you to look at Debraj Roy submission above as it seems to conflict with yours.


As I said above , my requirement is as follows


"Unfortunately , I am still no nearer resolving my problem.

I want to print out the current worksheet WITHOUT a PREVIEW and then re-set the default back to WITH PREVIEW."


I have taken a screen shot but do not know how to attach it to this post. Is it possible ?


Please have another look at my problem becasue I am getting thoroughly frustrated !
 
Hi, terrydennis!

I'll try Debraj Roy submission and I'll let you know. Please refer to the second green sticky post a this forums main page for uploading guidelines.

Regards!
 
Hi, terrydennis!


I have no physical printer installed in this machine, just the defaults installed either by Windows 7 Ultimate x64 and Office Professional Plus 2010.

Here's a copy of my Immediate window:

-----

?application.printcommunication

Verdadero -----> in english: True

?application.ActivePrinter

Enviar a OneNote 2010 en nul: -----> in english: Send to OneNote 2010 in nul:

-----


Running this macro I see first a little window saying "Printing...", then it closes and the preview window of active worksheet appears. Here's the link to the file, just in case:

https://dl.dropbox.com/u/60558749/Switching%20Print%20Preview%20on%20and%20off%20in%20VBA%20%28for%20terrydennis%20at%20chandoo.org%29.xlsm


Regards!
 
I do not know how much clearer I can make things !

I want to have a macro to print a worksheet WITHOUT ANY PREVIEW ! All things that have been recommended to me so far have failed !


Now , specfically in reply to SirJB7's last email.

I downloaded your test worksheet with the accompanying macro.

In the immediate window , I typed in


? Application.PrintCommunication and pressing 'Return' gave nothing apart from an error message

Runtime error '438'

Object does not support this property or method


Next , I had typed in ? Application.ActivePrinter and pressed 'Return' , it gave Canon iP4900 on Ne02


Now , on to your macro. When I ran it , I see first a little window saying "Printing...", then it closed ----- just what you saw ! After that , I had the Canon Preview window of the active worksheet asking me to 'Print' or 'Cancel printing'. I selcted 'Cancel Printing' and I was left with the Microsoft Excel Print Preview Window.


SO , I have still not achieved a printout without having preview window(s) displayed and having to click 'Print' to get the result.


Does the fact that I have Excel 2007 and am running Windows XP have anything to do with things ?

If I do not get success with my query this time , I am afraid that I will have to accept defeat !
 
Code: 
? Application.PrintCommunication is introduce in Excel 2010. That's why I ask ' are you using 2010?'


in immediate window, write

[code]ActiveSheet.PrintOut Preview:=False
or only ActiveSheet.PrintOut[/code]

I tried and it never ask me to press print button or shows me PrintPreview Screen.


I Google for 'Canon iP4900' and found this page..

http://support-au.canon.com.au/contents/AU/EN/0100347402.html


I know you are not using MAC but this page gives some CAUTION regarding preview..


Please check your Printer setting.. and search for [Do not show again.] checkbox..


Best of luck...
 
I don't understand why you are Googling Canon iP4900 ! My printer is a Canon Pixma iP4950 so I would expect that to be indicated from Application.ActivePrinter !


Apart from that , ActiveSheet.PrintOut Preview:=False shows me the Canon IJ Preview window and will not print out the worksheet without pressing the print button.


The rest of your email does not help me , I'm afraid !
 
Hi, terrydennis!


I think I know understand what you asked for: just printing without preview, and not printing without preview and after that preview. If I'm right, then delete the second line in my code and keep it like this:

-----

Option Explicit


Sub UndecidedPreviewPrinting()

ActiveSheet.PrintOut Preview:=False

End Sub

-----


I hope it'd work for you.


And about XP and Office 2007, I can only access to Win 7 machines for such a test, with Office 2010 mostly. If I find any issue related to 2007 version I'll post it here.


Regards!
 
Sorry , SirJB7 , that doesn't work either ! I STILL get the Canon Preview window displayed with the option of 'Print' or 'Cancel print'


I hope that you will understand when I say , I give up ! It does not appear to be possible !


Thanks for trying.
 
Its not Excel's fault.. Its your printer setting which forcefully display the preview screen.

Please check your Printer setting..

To open Printers and Faxes, click Start, click Control Panel, click Printers and Other Hardware, and then click Printers and Faxes. > "Your Printer" > Properties..

and search for [Do not show again.] checkbox..


Hope, it may now works..
 
After days of trial and error , I believe that I have eventually come to fully understand the background to the situation. Contributors have helped but none had FULLY explained the scenario.

The conclusion that I have come to is as follows :


BOTH the Printer AND EXCEL have a PrintView facility.

In order for EXCEL to Print WITHOUT a Preview , the PRINTER Preview setting must be switched OFF.

So ,

with PRINTER PREVIEW = OFF , macro ActiveSheet.PrintOut Preview:=False PRINTS WITH NO PREVIEW


with PRINTER PREVIEW = OFF , macro ActiveSheet.PrintOut Preview:=True PRINTS WITH EXCEL PREVIEW ON


with PRINTER PREVIEW = OFF , macro ActiveSheet.PrintOut PRINTS WITH NO PREVIEW


with PRINTER PREVIEW = ON , macro ActiveSheet.PrintOut PRINTS WITH NO EXCEL PREVIEW BUT WITH PRINTER PREVIEW


The problem with all this is that one would normally run a printer with the Preview ON - well I do anyway - and if you are running an Excel Worksheet which has a macro to operate with or without preview , you must remember to set the PRINTER to PREVIEW OFF before you use the worksheet.

As far as I can tell , Excel cannot switch off the PRINTER PREVIEW SETTING (which would be the ideal scenario) ..... unless someone can tell me otherwise !


Thanks again to all contributors !
 
Hi Teerydennis,


Your Printer(Canon Pixma iP4950) is a High quality Printer. It can print CD cover, Photo and it has a special facility to set multiple type of Paper at single time..

So Preview Feature is essential when you want to print photo or CD. but for normal printer, this facility was not available..


Thanks...
 
Hi Debraj ,


You may be right ; however , I have one of the cheapest printers , the Epson stylus T11 , and in its Printing Preferences section , there is a check-box called Print Preview.


Narayan
 
Hui


To get to Printer Preview setting


Either from Desktop

Start/Settings/Control Panel/Printer- Faxes/Canon iP4900Series/Printer/Printer Preferences/Main/... then Checkbox "Preview before Printing"


OR from within Excel

Page Layout/Page Setup/Options/Main ...... then

Checkbox "Preview before Printing"
 
You must log in or register to reply here.
Back
Top