Change printer properties in excel macro

12
votes

Is there any way I can change printer properties to color rather than black and white in an Excel macro/Excel VBA? I would like to print in color from the macro but every time I exit excel it sets the color to black and white. I would like for the macro to set the color back to color every time I run it. This is the code I am using to print:

Workbooks("Book1.xlsm").Worksheets("Sheet3").PrintOut from:=1, To:=(i / 2) - 0.5

This prints every page effected by the macro but it does it in black and white. I have already tried this but it didn't change anything:

Workbooks("Book1.xlsm").Worksheets("Sheet3").PageSetup.BlackAndWhite = False

If it isn't possible to do this in vba I would be fine with an API solution as well.

1
vbaexcelprinting
Record a macro and see what it does. This should be easy. Not sure of your statement syntax Workbooks("Book1.xlsm").Worksheets("DoNotDelete").PageSetup.BlackAndWhite = False – Peekay
I am surprised. Look at this. Seems to be useful. mrexcel.com/forum/excel-questions/… – Peekay
As much as I understand to get around the default settings on a system driver is more or less tricky. What you would need to do to change the default setting on a printer is to use Microsoft/Printer vendor APIs to get functionality to control the driver parameters. I might be totally wrong, but to me it seems that it's going to be a long night without a sleep to code a this functionality, and if a person is going to use some other printer with different Vendor drivers it'll fall flat again. A good article about this – Han Soalone
You can use WMI to tell if the printer is configured in black and white or colour - but although tantalisingly close, not to change it. technet.microsoft.com/en-us/windows/aa394364%28v=vs.71%29.aspx – brettdj
@mehow that still doesn't do it because the print settings are still at b&w instead of color but i need them to be color and i don't want to do that manually every time before i run the macro. – Tim.DeVries

1 Answers

6
votes

Rather than using:

Workbooks("Book1.xlsm").Worksheets("Sheet3").PrintOut from:=1, To:=(i / 2) - 0.5

You could just use:

Workbooks("Book1.xlsm").Worksheets("Sheet3").PrintOut

and you can skip:

Workbooks("Book1.xlsm").Worksheets("DoNotDelete").PageSetup.BlackAndWhite = False

Because it only changes the worksheet settings, not the print settings.

But as far as printing in color goes you are probably best off just creating a shortcut the same printer twice in the control panel and set one to default color and another to default black and white. This way you can specify color or black/white just by which printer you choose.