Search code examples
excelvbapdfprintingsave

Excel Macro VBA - Change active printer when saving with button

I got this

Sub SaveCells()
    Dim FilePath As String
    Dim FileName As String
    FilePath = "C:\Users\USER\Documents\"
    FileName = FilePath & Format(Now(), "DD-MM-YYYY hh-mm") & " Report"
    ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, FileName:=FileName
End Sub

to save data as PDF. The problem is that I need to keep the default printer set to my label printer. When I use this button like that, it's saving only the size of the label which is really small. So I need this button to also set the active printer to Microsoft Print to PDF. I do it manually now but have it on this button would be so nice.

Thank you for all your help.

Solution

  • 'Try this out. I've had to do this in many projects

    Sub Change_Default_Printer(defaultPrinter as string, tempPrinter as string)
    Set mynetwork = CreateObject("WScript.network")
    mynetwork.setdefaultprinter tempPrinter   
    Call SaveCells() 'your existing routine
    mynetwork.setdefaultprinter defaultPrinter  
End Sub