Search code examples
excelselectprintingdefault

VBA Select Printer when Printing files within a folder

I have this script which prints all files within a folder but it always selects the default printer, is there a way to specify the name of the printer that I would like it to print to within the VBA code?

Sub CommandButton1_Click()
Dim wb As Workbook, ws As Worksheet
Dim FileName As String, Path As String
Set wb = ActiveWorkbook
Set ws = ActiveSheet

Path = "Z:\Customer Operations\2021\Despatches\*.csv"

FileName = Dir(Path, vbNormal)
Do Until FileName = ""
Application.DisplayAlerts = False
Workbooks.Open Left(Path, Len(Path) - 5) & FileName
Columns("A:H").AutoFit
With ActiveSheet.PageSetup
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
Set wb = ActiveWorkbook
For Each ws In wb.Worksheets
ws.PrintOut
Next
wb.Close
FileName = Dir()
Loop
End Sub

Any help much appreciated :)

Solution

  • If you want to over-ride the default at run time, incldue this line in your code:

    Application.Dialogs(xlDialogPrinterSetup).Show