Updating printer to Microsoft Print to PDF before formatting

Joined
Nov 24, 2017
Messages
49
Office Version
  1. 365
Platform
  1. Windows
Hello, I have run into an issue when other people have attempted to run my macro. My computer's default printer is Microsoft Print to PDF so I didn't realize there was an issue until someone else attempted to run.

I believe the issue is because other people have a different printer default. I've tried to update the code to switch printers.
The macro works on my computer if I update my default to ScanSoft PDF Create! or even a printer that I'm not currently connected to. The macro runs as expected.
However when I try on another computer in my possession, it does not work and does not allow debugging. I have the same "Microsoft Print to PDF on Ne02:" on both computers.

I recorded the macro, but all I need it to do is:
Landscape
Tabloid size
Very narrow margins
Scaled to 1 page

On the second computer I get the error code:
Run-time error '1004':
Method 'ActivePrinter' of object 'Application' failed

1601529742288.png


Is there something I am doing incorrectly, or can someone suggest an alternative?

Thank you Crystal

VBA Code:
Sub Format_Output()

    Dim sCurrentPrinter As String
    Dim sPDFwriter As String

sPDFwriter = "Microsoft Print to PDF on Ne02:"

' save current printer name, then change to PDF writer
    sCurrentPrinter = Application.ActivePrinter
    Application.ActivePrinter = sPDFwriter

' continue with the rest of your macro

    With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    Application.PrintCommunication = True
    ActiveSheet.PageSetup.PrintArea = ""
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.2)
        .RightMargin = Application.InchesToPoints(0.2)
        .TopMargin = Application.InchesToPoints(0.25)
        .BottomMargin = Application.InchesToPoints(0.25)
        .HeaderMargin = Application.InchesToPoints(0)
        .FooterMargin = Application.InchesToPoints(0)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = 100
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = True
        .EvenPage.LeftHeader.Text = ""
        .EvenPage.CenterHeader.Text = ""
        .EvenPage.RightHeader.Text = ""
        .EvenPage.LeftFooter.Text = ""
        .EvenPage.CenterFooter.Text = ""
        .EvenPage.RightFooter.Text = ""
        .FirstPage.LeftHeader.Text = ""
        .FirstPage.CenterHeader.Text = ""
        .FirstPage.RightHeader.Text = ""
        .FirstPage.LeftFooter.Text = ""
        .FirstPage.CenterFooter.Text = ""
        .FirstPage.RightFooter.Text = ""
    End With
    Application.PrintCommunication = True
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    Application.PrintCommunication = True
    ActiveSheet.PageSetup.PrintArea = ""
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.2)
        .RightMargin = Application.InchesToPoints(0.2)
        .TopMargin = Application.InchesToPoints(0.25)
        .BottomMargin = Application.InchesToPoints(0.25)
        .HeaderMargin = Application.InchesToPoints(0)
        .FooterMargin = Application.InchesToPoints(0)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperTabloid
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = 100
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = True
        .EvenPage.LeftHeader.Text = ""
        .EvenPage.CenterHeader.Text = ""
        .EvenPage.RightHeader.Text = ""
        .EvenPage.LeftFooter.Text = ""
        .EvenPage.CenterFooter.Text = ""
        .EvenPage.RightFooter.Text = ""
        .FirstPage.LeftHeader.Text = ""
        .FirstPage.CenterHeader.Text = ""
        .FirstPage.RightHeader.Text = ""
        .FirstPage.LeftFooter.Text = ""
        .FirstPage.CenterFooter.Text = ""
        .FirstPage.RightFooter.Text = ""
    End With
    Application.PrintCommunication = True
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    Application.PrintCommunication = True
    ActiveSheet.PageSetup.PrintArea = ""
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.2)
        .RightMargin = Application.InchesToPoints(0.2)
        .TopMargin = Application.InchesToPoints(0.25)
        .BottomMargin = Application.InchesToPoints(0.25)
        .HeaderMargin = Application.InchesToPoints(0)
        .FooterMargin = Application.InchesToPoints(0)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperTabloid
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 1
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = True
        .EvenPage.LeftHeader.Text = ""
        .EvenPage.CenterHeader.Text = ""
        .EvenPage.RightHeader.Text = ""
        .EvenPage.LeftFooter.Text = ""
        .EvenPage.CenterFooter.Text = ""
        .EvenPage.RightFooter.Text = ""
        .FirstPage.LeftHeader.Text = ""
        .FirstPage.CenterHeader.Text = ""
        .FirstPage.RightHeader.Text = ""
        .FirstPage.LeftFooter.Text = ""
        .FirstPage.CenterFooter.Text = ""
        .FirstPage.RightFooter.Text = ""
    End With
    Application.PrintCommunication = True

 ActiveSheet.PageSetup.PrintComments = -4142
 
     'go back to the original printer
    Application.ActivePrinter = sCurrentPrinter

End Sub
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Dan_W

Active Member
Joined
Jul 11, 2018
Messages
254
Office Version
  1. 365
Platform
  1. Windows
Are you certain that the printer is on the same port on the other computer (ie., Ne02). That error will usually appear if you get the port number wrong.

Have you tried:
VBA Code:
Application.ActivePrinter = "Microsoft Print to PDF on Ne01:"
 
Joined
Nov 24, 2017
Messages
49
Office Version
  1. 365
Platform
  1. Windows
I did the debug first to get the accurate printer name and it was the same on both computers. I just double checked.
I did not know what the Ne01 portion was. If that is a port will this printer switching work if the other person is using a different port? How can a simple format error because of the default printer?

edit: that number should not be 02 lol let me try it again. thank you for pointing that out.
 
Last edited:
Joined
Nov 24, 2017
Messages
49
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

ok the port IS different on both computers. Any ideas how I can make this work if the port has to be apart of the name?
 

Dan_W

Active Member
Joined
Jul 11, 2018
Messages
254
Office Version
  1. 365
Platform
  1. Windows
Sure. We just need to get it to select the appropriate printer variable depending on the computer by using the following:
VBA Code:
host=ENVIRON("computername")
This will help you identify the computer, and then if its only going to be one of two possible computers, then you could then just add the following:
VBA Code:
If host = "NAME OF COMPUTER" then 
     myPrinter = "NAME OF PRINTER AND PORT"
Else
    myPrinter = "NAME OF PRINTER AND OTHER PORT"
End If
Application.ActivePrinter = myPrinter
Hope that works...
 
Joined
Nov 24, 2017
Messages
49
Office Version
  1. 365
Platform
  1. Windows
Hi, there are more than two possibilities as many people will be using this report. I think I've found a solution. This function finds the printer and port and I incorporated that into my initial macro. It has worked on both computers.

It sets printer default to Microsoft Print PDF and returns to previous default once the macro has completed.

Whoot!

VBA Code:
Function FindPrinter(ByVal PrinterName As String) As String

'Written: November 28, 2009
'Author:  Leith Ross
'Summary: Finds a printer by name and returns the printer name and port number.

 'This works with Windows 2000 and up
 
  Dim Arr As Variant
  Dim Device As Variant
  Dim Devices As Variant
  Dim Printer As String
  Dim RegObj As Object
  Dim RegValue As String
  Const HKEY_CURRENT_USER = &H80000001
      
    Set RegObj = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\default:StdRegProv")
    RegObj.enumvalues HKEY_CURRENT_USER, "Software\Microsoft\Windows NT\CurrentVersion\Devices", Devices, Arr
    
      For Each Device In Devices
        RegObj.getstringvalue HKEY_CURRENT_USER, "Software\Microsoft\Windows NT\CurrentVersion\Devices", Device, RegValue
        Printer = Device & " on " & Split(RegValue, ",")(1)
        If InStr(1, Printer, PrinterName, vbTextCompare) > 0 Then
           FindPrinter = Printer
           Exit Function
        End If
      Next
      
End Function

VBA Code:
Sub Format_Output()

    Dim sCurrentPrinter As String

    Dim sPDFwriter As String

' perform whatever tasks need to be done prior to printing

' make sure you also initialize the sPDFwriter string with

' the name of your PDF writer

MyPrinter = FindPrinter("Microsoft Print to PDF")

sPDFwriter = MyPrinter

' save current printer name, then change to PDF writer

    sCurrentPrinter = Application.ActivePrinter

    Application.ActivePrinter = sPDFwriter

' continue with the rest of your macro

    With ActiveSheet.PageSetup

        .PrintTitleRows = ""

        .PrintTitleColumns = ""

    End With

    Application.PrintCommunication = True

    ActiveSheet.PageSetup.PrintArea = ""

    Application.PrintCommunication = False

    With ActiveSheet.PageSetup

        .LeftHeader = ""

        .CenterHeader = ""

        .RightHeader = ""

        .LeftFooter = ""

        .CenterFooter = ""

        .RightFooter = ""

        .LeftMargin = Application.InchesToPoints(0.2)

        .RightMargin = Application.InchesToPoints(0.2)

        .TopMargin = Application.InchesToPoints(0.25)

        .BottomMargin = Application.InchesToPoints(0.25)

        .HeaderMargin = Application.InchesToPoints(0)

        .FooterMargin = Application.InchesToPoints(0)

        .PrintHeadings = False

        .PrintGridlines = False

        .PrintComments = xlPrintNoComments

        .PrintQuality = 600

        .CenterHorizontally = False

        .CenterVertically = False

        .Orientation = xlLandscape

        .Draft = False

        .PaperSize = xlPaperLetter

        .FirstPageNumber = xlAutomatic

        .Order = xlDownThenOver

        .BlackAndWhite = False

        .Zoom = 100

        .PrintErrors = xlPrintErrorsDisplayed

        .OddAndEvenPagesHeaderFooter = False

        .DifferentFirstPageHeaderFooter = False

        .ScaleWithDocHeaderFooter = True

        .AlignMarginsHeaderFooter = True

        .EvenPage.LeftHeader.Text = ""

        .EvenPage.CenterHeader.Text = ""

        .EvenPage.RightHeader.Text = ""

        .EvenPage.LeftFooter.Text = ""

        .EvenPage.CenterFooter.Text = ""

        .EvenPage.RightFooter.Text = ""

        .FirstPage.LeftHeader.Text = ""

        .FirstPage.CenterHeader.Text = ""

        .FirstPage.RightHeader.Text = ""

        .FirstPage.LeftFooter.Text = ""

        .FirstPage.CenterFooter.Text = ""

        .FirstPage.RightFooter.Text = ""

    End With

    Application.PrintCommunication = True

    Application.PrintCommunication = False

    With ActiveSheet.PageSetup

        .PrintTitleRows = ""

        .PrintTitleColumns = ""

    End With

    Application.PrintCommunication = True

    ActiveSheet.PageSetup.PrintArea = ""

    Application.PrintCommunication = False

    With ActiveSheet.PageSetup

        .LeftHeader = ""

        .CenterHeader = ""

        .RightHeader = ""

        .LeftFooter = ""

        .CenterFooter = ""

        .RightFooter = ""

        .LeftMargin = Application.InchesToPoints(0.2)

        .RightMargin = Application.InchesToPoints(0.2)

        .TopMargin = Application.InchesToPoints(0.25)

        .BottomMargin = Application.InchesToPoints(0.25)

        .HeaderMargin = Application.InchesToPoints(0)

        .FooterMargin = Application.InchesToPoints(0)

        .PrintHeadings = False

        .PrintGridlines = False

        .PrintComments = xlPrintNoComments

        .PrintQuality = 600

        .CenterHorizontally = False

        .CenterVertically = False

        .Orientation = xlLandscape

        .Draft = False

        .PaperSize = xlPaperTabloid

        .FirstPageNumber = xlAutomatic

        .Order = xlDownThenOver

        .BlackAndWhite = False

        .Zoom = 100

        .PrintErrors = xlPrintErrorsDisplayed

        .OddAndEvenPagesHeaderFooter = False

        .DifferentFirstPageHeaderFooter = False

        .ScaleWithDocHeaderFooter = True

        .AlignMarginsHeaderFooter = True

        .EvenPage.LeftHeader.Text = ""

        .EvenPage.CenterHeader.Text = ""

        .EvenPage.RightHeader.Text = ""

        .EvenPage.LeftFooter.Text = ""

        .EvenPage.CenterFooter.Text = ""

        .EvenPage.RightFooter.Text = ""

        .FirstPage.LeftHeader.Text = ""

        .FirstPage.CenterHeader.Text = ""

        .FirstPage.RightHeader.Text = ""

        .FirstPage.LeftFooter.Text = ""

        .FirstPage.CenterFooter.Text = ""

        .FirstPage.RightFooter.Text = ""

    End With

    Application.PrintCommunication = True

    Application.PrintCommunication = False

    With ActiveSheet.PageSetup

        .PrintTitleRows = ""

        .PrintTitleColumns = ""

    End With

    Application.PrintCommunication = True

    ActiveSheet.PageSetup.PrintArea = ""

    Application.PrintCommunication = False

    With ActiveSheet.PageSetup

        .LeftHeader = ""

        .CenterHeader = ""

        .RightHeader = ""

        .LeftFooter = ""

        .CenterFooter = ""

        .RightFooter = ""

        .LeftMargin = Application.InchesToPoints(0.2)

        .RightMargin = Application.InchesToPoints(0.2)

        .TopMargin = Application.InchesToPoints(0.25)

        .BottomMargin = Application.InchesToPoints(0.25)

        .HeaderMargin = Application.InchesToPoints(0)

        .FooterMargin = Application.InchesToPoints(0)

        .PrintHeadings = False

        .PrintGridlines = False

        .PrintComments = xlPrintNoComments

        .PrintQuality = 600

        .CenterHorizontally = False

        .CenterVertically = False

        .Orientation = xlLandscape

        .Draft = False

        .PaperSize = xlPaperTabloid

        .FirstPageNumber = xlAutomatic

        .Order = xlDownThenOver

        .BlackAndWhite = False

        .Zoom = False

        .FitToPagesWide = 1

        .FitToPagesTall = 1

        .PrintErrors = xlPrintErrorsDisplayed

        .OddAndEvenPagesHeaderFooter = False

        .DifferentFirstPageHeaderFooter = False

        .ScaleWithDocHeaderFooter = True

        .AlignMarginsHeaderFooter = True

        .EvenPage.LeftHeader.Text = ""

        .EvenPage.CenterHeader.Text = ""

        .EvenPage.RightHeader.Text = ""

        .EvenPage.LeftFooter.Text = ""

        .EvenPage.CenterFooter.Text = ""

        .EvenPage.RightFooter.Text = ""

        .FirstPage.LeftHeader.Text = ""

        .FirstPage.CenterHeader.Text = ""

        .FirstPage.RightHeader.Text = ""

        .FirstPage.LeftFooter.Text = ""

        .FirstPage.CenterFooter.Text = ""

        .FirstPage.RightFooter.Text = ""

    End With

    Application.PrintCommunication = True

 ActiveSheet.PageSetup.PrintComments = -4142

     'go back to the original printer

    Application.ActivePrinter = sCurrentPrinter

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,114,035
Messages
5,545,648
Members
410,696
Latest member
JTrehan
Top