Solved printing multiple worksheets in excel in colo

Microsoft Excel 2003 (full product)
September 9, 2009 at 09:21:29
Specs: Windows XP
hello there,

I have an issue when printing multiple excel worksheets in colour. I'm using Excel 2003.


The default driver setting for our printer is set to Black & White although it can do both colour and B & W. When trying to print multiple worksheets in a workbook if you go to the printer properties and change the setting to colour only the first sheet prints in colour the rest comes out as B&W.

It appears that the only method for printing all worksheets in colour is to select each worksheet individually and change its properties to colour. This is quite frustrating if you have a large workbook.

I have tried different print setting and worksheet options of selecting all worksheets together and grouping them or using the 'entire workbook' button.

Any advice or possibly scripts to overcome this would be appreciated.

thanks


See More: printing multiple worksheets in excel in colo

Report •


✔ Best Answer
September 10, 2009 at 11:00:16
Hi,

Try printing to file first.

Then send the file to the printer.

I used a freeware program 'PrintFile' to send the file to the printer - see www.lerup.com/printfile/

I had two worksheets with coloured text, then printed the 'Entire Workbook' with Print To File Checked.

Name the file C:\ Anything.prn or whatever suits you
Open the PrintFile program
Set it up with the Printer you want to use
Then drag the file onto PrintFile's Main Window

When you drag the Anything.prn file onto the PrintFile Program's Main Window, it allows you to set printer options. I selected Monochrome, and both sheets in my Workbook printed in B & W

I obviously don't have the same printer setup as you, but logically it sounds as though it might work for you.

Regards

PS there may be other ways to send the file to the printer rather than using this freeware program.



#1
September 9, 2009 at 09:30:33
Have you tried:

File
Page Setup
Sheet Tab
There is a check box in the middle under Print, for Black & White
Make sure it is Unchecked.

MIKE

http://www.skeptic.com/


Report •

#2
September 9, 2009 at 12:30:31
Hi Mike,

Yep, have checked there as well and it's 'unchecked'


Report •

#3
September 9, 2009 at 12:32:52
I was able to duplicate the issue, but in reverse. Since my corporate printers default to color, I did this:

- I selected a group of 3 sheets that all had color.
- I chose Print and set the properties for the printer to B&W.
- When I printed the 3 sheets, only the first one was in B&W.

I'll need to play around with this a bit.


Report •

Related Solutions

#4
September 9, 2009 at 12:43:00
Can you duplicate the printer and make one default to B&W and the other to Color? That's what I do at home.

Each system's default printer is the B&W version and we have to physically chose the Color printer to get color.


Report •

#5
September 9, 2009 at 13:21:54
This is the tricky bit.... we have a generic universal print driver in use for both colour and B&W multi-function devices. This is due to a security and cost saving initiative called "follow-me" printing.

'follow-me' printing allows you to print to a single print queue and then release your jobs at whichever device you choose.
Because there is only one print queue there is only one driver and this is defaulted to B&W.

So if a user has a doc they wanted printed in colour instead of a user having to select either a colour or B&W queue from their printers and faxes list, there is the one queue and then they change the print driver settings to either print in colour or leave it in B&W. From there they walk to a colour device and release the job.

This works perfectly well...until you try to print multiple Excel worksheets.

To me it seems that Excel interprets each worksheet as a separate file with independant settings.


Report •

#6
September 9, 2009 at 14:18:23
OK, try:

Open your workbook
Right Click the first sheet tab
Then choose Select All Sheets
Then do a File, Print

and as a last desperate attempt.

Open a blank Excel sheet,
Then from the task bar select
File
Open
Select but do not open your target workbook,

Select Tools from the "Open Window" task bar
Select Print.

MIKE

http://www.skeptic.com/


Report •

#7
September 10, 2009 at 09:54:54
I just wanted to let you know that I've tried a few things both at home and at work and there does not seem to be a workaround for this.

I tried using the .PageSetup.BlackAndWhite = False in VBA, but even that doesn't help.

Just for fun, I just tried changing *2* properties before printing on a laser printer at work. I changed the Color option to B&W and I chose Tray 1 (manual feed) instead of Tray 2 which is the default.

The first sheet took paper from Tray 1 and printed in B&W, while Sheets 2 & 3 printed in Color from Tray 2.

So it appears that each sheet is indeed being treated as a separate "file" when printing and the propeties have to be set individually, and manually, for each sheet.

Sorry!


Report •

#8
September 10, 2009 at 10:50:17
I also was able to duplicate the issue, but in reverse.
I have a HP Deskjet 3650 that is connected via USB, no network involved.

- I selected a group of 3 sheets that all had color.
- I chose Print and set the properties for the printer to B&W.
- When I printed the 3 sheets, only the first one was in B&W the rest came out in color.

I hunted around on the MS site, but could find not info on this particular feature?

MIKE

http://www.skeptic.com/


Report •

#9
September 10, 2009 at 11:00:16
✔ Best Answer
Hi,

Try printing to file first.

Then send the file to the printer.

I used a freeware program 'PrintFile' to send the file to the printer - see www.lerup.com/printfile/

I had two worksheets with coloured text, then printed the 'Entire Workbook' with Print To File Checked.

Name the file C:\ Anything.prn or whatever suits you
Open the PrintFile program
Set it up with the Printer you want to use
Then drag the file onto PrintFile's Main Window

When you drag the Anything.prn file onto the PrintFile Program's Main Window, it allows you to set printer options. I selected Monochrome, and both sheets in my Workbook printed in B & W

I obviously don't have the same printer setup as you, but logically it sounds as though it might work for you.

Regards

PS there may be other ways to send the file to the printer rather than using this freeware program.


Report •

#10
September 10, 2009 at 11:02:55
Wow! There must be an echo in here.

See Response Number 3


Report •

#11
September 10, 2009 at 11:27:25
Wow! There must be an echo in here.

Not exactly an echo, because when I did my little experiment I forced Excel Sheet1 to print on two sheets of paper and both the first and second sheets of paper printed B & W it was not until the second Excel Sheet2 started printing that it reverted back to the default color printing, which indicates to me that probably imbedded at the beginning of each Excel Sheet there is a printer reset code.
The only way to be sure would be to run the print stream thru a Hex editor and see what exactly Excel is sending along with the data.

MIKE

http://www.skeptic.com/


Report •

#12
September 10, 2009 at 11:52:47
thanks all for the comments and suggestions.

I found some similar info on using the VB script DerbyDad and had the same problem. Recorded a page setup macro and it changes other page setup features but not the colour driver setting. Very frustrating.

Humar, I'll give that a try to see what happens however the process to follow seems like it may take a similar amount of time.

I agree Mark, there's some weird coding in there that would require some detailed investigation however I feel this won't be something that Microsoft would entertain....although interesting to see what Excel 2010 has to offer for this issue?


Report •

#13
September 10, 2009 at 12:48:25
Mike...just FYI...

I tried the same thing(s)

- Multiple pages per sheet
- Default Color set to B&W
- Default B&W set to Color
- Default Tray 2 set to Tray 1
- Networked and USB connected printers
- Inkjets and Lasers

In every case, only the first sheet in the workbook adhered to the Print...Properties that were set prior to printing.

I may try Humar's suggestion when I get home, but my concern is graphs and charts and images and how they retain their integrity when printed to a .prn file. I've seen some strange things happen when printing Excel files to pdf conversion software. Hopefully the Print to File method works.


Report •

#14
September 10, 2009 at 19:34:08
I tried Humar's Print-To-File method and it appears to work.

I inserted a picture, a chart, and some WordArt on different sheets and they all came out fine and in B&W.

Nice find, Humar!


Report •

#15
September 11, 2009 at 00:40:52
Interesting, just about to give it a go myself.

In the meantime, I found this posting on a google groups site which explains things a bit more

http://groups.google.com/group/micr...


Report •

#16
September 11, 2009 at 01:19:57
Wow! thanks Humar!

It works a treat.

Only slight problem is when you print to file you have to manually input the file name and there isn't a browse option, but this is a microsoft issue and not the app's problem.


Report •

#17
September 11, 2009 at 05:19:49
I just reviewed the google/groups thread and it reminded me that I once wrote a SendKeys script to handle a printing issue a few years back.

I assume SendKeys could be used in this situation, but that would mean installing the macro in a personal.xls file for every user so it would be available for all users, all files. That might not be a viable option, but worth a discussion. If it is just one file that needs this functionality, that might be a little easier.

I should also add that I have heard rumors that SendKeys is not 100% reliable, but I don't use them enough to agree or disagree.


Report •

#18
September 11, 2009 at 05:33:32
re: ...when you print to file you have to manually input the file name and there isn't a browse option

If you are willing to use a macro, you could ask for a filename or hard code it into the macro. If you are interested in creating a "browse" workaround by using the GetSaveAsFilename method or something similar...

Hardcode Option:

ActiveWindow.SelectedSheets.PrintOut _
    Copies:=1, _
    PrintToFile:=True, _
    PrToFileName:="C:\Documents and Settings\UserName\Desktop\Book2.prn", _
    Collate:=True

Browse Option:

pFileName = Application.GetSaveAsFilename
  ActiveWindow.SelectedSheets.PrintOut _
    Copies:=1, _
    PrintToFile:=True, _
    PrToFileName:=pFileName, _
    Collate:=True


Report •

#19
September 11, 2009 at 05:47:28
Hi,

Glad that the print to file works. I agree that having to manually enter a filename and path is a pain.

Excel does allow access to the 'Save As' dialog in VBA

The following code will bring up the Save As box, navigate to where you want to save the print file, give it a name or select an old one to overwrite, click save and the selected path/filename will be used for your print file.

Sub PrintToFile()
'
' Routine to get a 'save as' dialog box to get the location
' and filename for a print file for all Worksheets
' in the active workbook
' Humar 11 September 2009
'
Dim strFN As String
strFN = Application.GetSaveAsFilename
ActiveWorkbook.PrintOut Copies:=1, PrintToFile:=True, _
Collate:=True, PrToFileName:=strFN
End Sub

Put this in a standard module in Personal.xls
Note that the code starting 'ActiveWorkbook.PrintOut' to '=strFN' is all one line

Create a button on a suitable toolbar and link it to this Macro.

If you are going to print to the same place all the time you could dispense with the GetSaveAsFilename and hard code the path and filename
e.g. strFN="C:\FilePrint.prn"
or you could use the time/date to make a filename.
strFN = "C:\" & Format(Now, "hhmm") & ".prn"
will give you a filename that looks like this
C:\0840.prn

Regards
PS I'm not in my office, so I haven't tested that it keeps the colour setting!!!


Report •

#20
September 11, 2009 at 07:00:45
Well, quite interesting - it did NOT keep the colour settings.
One page B & W, one page colour !

Report •

#21
September 11, 2009 at 10:02:10
What did not keep the settings? Using the macro?

I'll have to test this at home since I can not install the "PrintFile" app on my corporate image.

I know PrintFile worked when I manually printed the file created by the Print To File option, so why wouldn't the macro process work?

I'll test it tonight.


Report •

#22
September 11, 2009 at 11:23:56
Hello DerbyDad03,

When I used the macro to print to file, one page printed in colour and one page in B & W.

I have tried a number of options including iterating through the individual sheets in the Workbook, but still the same problem.

It appears that if you print a single worksheet using the standard print command, and select colour or B &W, the page retains that setting (colour or B & W), and ignores the printer setting when printing the whole workbook - whether you print it from the standard print command or using a macro to print directly to printer.

This suggests that a colour/B & W setting is applied to each sheet.

Each worksheet has a PageSetup.BlackAndWhite property, and setting these to True forced the printouts (using a macro) to print B & W, but setting the property to False resulted in two pages printing in colour and one still in B & W.

Confusing!

This was the macro I used to print each sheet and change the BlackAndWhite property


Sub PrintSheets()

Dim objWS As Worksheet

For Each objWS In Application.ActiveWorkbook.Worksheets
    objWS.PageSetup.BlackAndWhite = False
    objWS.PrintOut Copies:=1, PrintToFile:=False, Collate:=True
Next

End Sub


Report •

#23
September 11, 2009 at 12:49:34
Ah, I see.

However, that is not what I was surprised about.

I thought you had tried the code that we both suggested related to using the GetSaveAsFilename method and then using PrintToFile:=True and PrToFileName:= something.

That is where I would be very surprised to not get all B&W printouts when using the PrintFile app you suggested.

I had already tested the .PageSetup.BlackAndWhite = False code in various forms and in various modules and, as noted in Response # 7, found that it not help.

If you visit the site offered in Response # 15, you see a brief explanation of the issue near the bottom of the thread.


Report •

#24
September 18, 2009 at 14:07:35
Hello I have another variation to add to this issue. When trying to print multiple sheets on one page, this problem also occurs. For example, I would like to print two sheets to one side of paper, but only the first sheet actually prints half size, and the rest are printed on individual paper, full size. I have to go to each sheet manually to set it multiple sheets per page. Annoying on a 35 sheet document.

This seems to confirm that excel prints each page as a seperate file. Any workaround for this one is appreciated.


Report •

#25
September 18, 2009 at 14:15:26
Hi,

Have you tried response Number 9.
It appeared to work for the colour printing issue.

Apart from that I have no other suggestions.

Regards


Report •

#26
October 23, 2009 at 15:05:45
Yes, I have tried that with some success. The problem is, I have 15 tab workbook, and I get 15 different print files.

I tried printing to PDF and i get one PDF with 14 pages, and one PDF with 1 page.

Any suggestions?


Report •

#27
October 23, 2009 at 19:18:19
Hi,

At the moment I can't think of another solution and I don't know why you get the two pdf files.

Sorry.

Could you repost this as a new post with a more detailed description of the problem.

What are you printing ?
Worksheets - if so what ranges, or charts on each worksheet.

If you are printing from multiple worksheets how do you call the print command.

The more info. the better!

Regards


Report •


Ask Question