MS Excel VBA code to switch printer to manual Feed

[JohnZ1156]

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.

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

 

[johnnyL]

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]

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]

Yep word Allows:

With ActiveDocument.PageSetup
.FirstPageTray = wdPrinterManualFeed
.OtherPagesTray = wdPrinterManualFeed
End With

Excel, not so much.

 

[JohnZ1156]

Yep word Allows:

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]

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]

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]

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]

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.