MS Excel VBA code to switch printer to manual Feed

JohnZ1156

Board Regular
Joined
Apr 10, 2021
Messages
152
Office Version
  1. 2021
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

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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.
 
Upvote 0
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.
 
Upvote 0
Yep word Allows:

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

Excel, not so much. :(
 
Upvote 0
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 !!!:(
 
Upvote 0
Just a weird idea, what if you paste the Excel data into Word and print from there (using VBA of course)? Would that work?
 
Upvote 0
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. :)
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

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
Back
Top