Excel Document Printing with VBA

Priyantha
2StarLounger
Posts: 121
Joined: 10 Oct 2022, 02:52

Excel Document Printing with VBA

Post by Priyantha »

Dear All,

I receive excel documents & some dodument one page some two pages, some document may 3 or four pages. I have set page setup propey (Pages A4, Every Margins). I wish to print, if document may be one page it print one page one side, document may be two or more pages it should print both side in pages. I tried bellow code but not working correctly. any one can help me to correct this.

Sub Print()
Dim ws As Worksheet
Dim pageCount As Integer

For Each ws In ThisWorkbook.Worksheets
ws.Activate
pageCount = Application.ExecuteExcel4Macro("Get.Document(50)")
If pageCount = 1 Then
With ActiveSheet.PageSetup
.CenterHorizontally = True
.CenterVertically = True
End With

ActiveSheet.PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False

ElseIf pageCount > 1 Then
With ActiveSheet.PageSetup
.CenterHorizontally = True
.CenterVertically = True
End With
ActiveSheet.PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False, Duplex:=xlDuplexLongEdge
End If
Next ws
End Sub

BR,

Priyantha

User avatar
HansV
Administrator
Posts: 80038
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Excel Document Printing with VBA

Post by HansV »

Duplex is not an argument of the PrintOut method.
As far as I know, there is no direct support for specifying single-sided or double-sided printing in VBA - it is a printer setting that may vary from printer to printer.
Best wishes,
Hans

User avatar
SpeakEasy
5StarLounger
Posts: 739
Joined: 27 Jun 2021, 10:46

Re: Excel Document Printing with VBA

Post by SpeakEasy »

Yep there's no straightforward way of doing this. Microsoft's go to solution (indicates it is for Word, but is a general solution to the problem)is

https://www.betaarchive.com/wiki/index. ... ive/230743

but it isn't exactly straightforward (particularly if you are not API savvy). There's also the Win32_PrinterConfiguration WMI class - but there is no obvious way of writing the configuration, just reading it.

The solution I have used in the past is to create a new queue to the printer you want to use, but with a different name (this used to be pretty straighforward, but Microsoft in their wisdom have made it harder over the years), adjust the duplex settings for that queue, and then just use the ActivePrinter property to switch between them at will.

User avatar
HansV
Administrator
Posts: 80038
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Excel Document Printing with VBA

Post by HansV »

I once did some work for a company that wanted to use different printer configurations. I assigned names to these configurations, but it didn't work well - Excel uses printer names with network ports, e.g. "Label Printer on Ne03:".
For some reason, these port numbers kept changing, causing the code to fail.
Best wishes,
Hans

Priyantha
2StarLounger
Posts: 121
Joined: 10 Oct 2022, 02:52

Re: Excel Document Printing with VBA

Post by Priyantha »

Thanks All