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
Excel Document Printing with VBA
-
- Administrator
- Posts: 80038
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Excel Document Printing with VBA
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.
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
Hans
-
- 5StarLounger
- Posts: 739
- Joined: 27 Jun 2021, 10:46
Re: Excel Document Printing with VBA
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.
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.
-
- Administrator
- Posts: 80038
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Excel Document Printing with VBA
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.
For some reason, these port numbers kept changing, causing the code to fail.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 121
- Joined: 10 Oct 2022, 02:52
Re: Excel Document Printing with VBA
Thanks All