Updating printer to Microsoft Print to PDF before formatting

crystalneedshelpplzthnx

crystalneedshelpplzthnx

Board Regular
Joined
Nov 24, 2017
Messages
55
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
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Sort by date Sort by votes
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:"
 
Upvote 0
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:
Upvote 0
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?
 
Upvote 0
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...
 
Upvote 0
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
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

willow1985
  • Solved
PrintArea = "TableName" but visible cells only
Replies
2
Views
382
willow1985
willow1985
J
  • Question
Excel VBA error 400 while link to another sheet
Replies
3
Views
386
rollis13
rollis13
L
  • Question
Macro recorded print comments anyway
Replies
1
Views
434
sijpie
sijpie
C
  • Solved
VBA to sort Columns from named ranges stopped working
Replies
4
Views
425
cbarryb
C
P
  • Question
Macro to automatically print
Replies
1
Views
1K
pure vito
P

Forum statistics

Threads
1,214,590
Messages
6,120,423
Members
448,961
Latest member
nzskater

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top