MS Excel VBA code to switch printer to manual Feed

JohnZ1156

New Member
Joined
Apr 10, 2021
Messages
36
Office Version
  1. 2010
Platform
  1. Windows
I have an HP InkJet Color Printer and a Brother MFC-L2750DW Laser Printer.
I can "select" the Brother printer via VBA, but I also need to change it to manual feed.
I can't figure out or find how to select manual Feed using Excel VBA.

Here's what I have so far.
Please don't laugh.

VBA Code:
Sub PrintCheck()
'
Dim myprinter As String
Dim printer_name As String
printer_name = "Brother MFC-L2750DW series"

Application.ActivePrinter = "Brother MFC-L2750DW series on NE06:"
'MsgBox (Application.ActivePrinter)

    Application.Goto Reference:="Check"
    ActiveSheet.PageSetup.PrintArea = "$D$9:$L$19"
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False

'    printer_name = Application.ActivePrinter
   
    Worksheets("PNC_Checking").Select

Application.ActivePrinter = "HP Officejet Pro 8600 (Network) on NE02:"
'MsgBox (Application.ActivePrinter)

End Sub
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
2,103
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
Excel is not very accommodating with setting manual feed. If you know how to set via your printer settings, you may want to install the printer again and save its name as 'manual' or something like that. Set that newly installed version up for manual feed and then you won't have to worry about changing the setting in excel, just print to that newly installed printer that is already set up for manual feed.

That way you will still have the first install of the printer that you can use normally, but when you want to use excel with manual feed, you can choose the second installed version.
 

JohnZ1156

New Member
Joined
Apr 10, 2021
Messages
36
Office Version
  1. 2010
Platform
  1. Windows
Excel is not very accommodating with setting manual feed. If you know how to set via your printer settings, you may want to install the printer again and save its name as 'manual' or something like that. Set that newly installed version up for manual feed and then you won't have to worry about changing the setting in excel, just print to that newly installed printer that is already set up for manual feed.

That way you will still have the first install of the printer that you can use normally, but when you want to use excel with manual feed, you can choose the second installed version.

It's funny, but I was able to write a VBA macro in MS Word that instructs the same printer to print in "manual feed" mode when I print from a document.
I'm pretty shocked, and frankly find it hard to believe, that I can't do a similar command in MS Excel.
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
2,103
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
Yep word Allows:

VBA Code:
With ActiveDocument.PageSetup
.FirstPageTray = wdPrinterManualFeed
.OtherPagesTray = wdPrinterManualFeed
End With

Excel, not so much. :(
 

JohnZ1156

New Member
Joined
Apr 10, 2021
Messages
36
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Yep word Allows:

VBA Code:
With ActiveDocument.PageSetup
.FirstPageTray = wdPrinterManualFeed
.OtherPagesTray = wdPrinterManualFeed
End With

Excel, not so much. :(

Well, I'm unhappy with the message, but I'm not going to blame the messenger.
Thanks for your help !!!:(
 

Gokhan Aycan

Active Member
Joined
Aug 8, 2021
Messages
421
Office Version
  1. 365
Platform
  1. Windows
Just a weird idea, what if you paste the Excel data into Word and print from there (using VBA of course)? Would that work?
 

JohnZ1156

New Member
Joined
Apr 10, 2021
Messages
36
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Just a weird idea, what if you paste the Excel data into Word and print from there (using VBA of course)? Would that work?
Hi Gokhan,
Thank you for your suggestion.
But, I found out that, if I put a check into the manual feeder slot of my printer BEFORE I issue the print command, the printer will automatically load from the manual feed tray.

Had I been able to have Excel VBA select manual feed, I would have been able to issue the command, then my printer would beep and wait for me to insert the check into the manual feed slot.

As my father always used to say, 6 of 1, half a dozen of the other.
or, it's like the egg before the chicken. :)
 

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,484
Office Version
  1. 2016
Platform
  1. Windows
I am not very familiar with printing coding so I am not sure if changes made to the printer settings will be global and persistent.
If that is the case, then you could programmatically launch an invisible instance of MS Word behind the scenes from your excel code, change the printer settings, do your excel-printing stuff ,restore the initial printer settings when done and close Word.
 

JohnZ1156

New Member
Joined
Apr 10, 2021
Messages
36
Office Version
  1. 2010
Platform
  1. Windows
I am not very familiar with printing coding so I am not sure if changes made to the printer settings will be global and persistent.
If that is the case, then you could programmatically launch an invisible instance of MS Word behind the scenes from your excel code, change the printer settings, do your excel-printing stuff ,restore the initial printer settings when done and close Word.
Thank you for your suggestion, but I think I'm going to follow the procedure above.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,194
Messages
5,768,777
Members
425,492
Latest member
blueexcel123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top