0

I have a number of questions on this topic:

  1. If I print using Application.ActivePrinter = "Microsoft Print to PDF on Ne01:" then how can I pass a specific file name and path to the printer? (Rather than having it prompt me?)
  2. Since I want others to use this file, how can I check that Microsoft PDF is always on Ne01? What if theirs is on a different port?
  3. Many suggest using a Findprinter command, but I always get the sub or function not defined error
  4. Many suggest using the built-in ability to save as a PDF, instead of printing to PDF, but I find the resulting PDF has huge margins and the wrong size (not A4)
braX
  • 11,506
  • 5
  • 20
  • 33
Floyd
  • 119
  • 4

2 Answers2

1

You can save Excel sheets as PDF directly. Try below macro. Change C:\ to save your file to your desired location.

Sub SavePDF()
Dim pdfName As String

    pdfName = "MyFileName"

    Sheets("MyPDFsheet").ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    "C:\" & pdfName & ".pdf", Quality:= _
    xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=True, _
    OpenAfterPublish:=False

End Sub
Harun24hr
  • 30,391
  • 4
  • 21
  • 36
0

  1. PDF printouts go to a "Port" that port can have a filename same as any printing device. The user normally is tasked with select device/folder/portname.pdf thus there is a virtual PORTPROMPT: device (that is set via the print prompt dialog)
    A) Set a duplicate driver to a port as a fixed filename in a watch folder. see Print to PDF with powershell while suppressing the "Save As" Prompt and https://stackoverflow.com/a/69169728/10802527

  2. A) You cannot, this Windows 11 just Like Other Windows versions currently does not have an MS PDF printer, it is a windows optional extra I am not allowed to install.

  3. A) Too variable, Default 1st NEtwork printer may be a Fax or in this case nothing so should return NULL

  4. A) Save should have options. see https://superuser.com/questions/1064214/create-pdf-from-an-excel-file-without-white-margins for other suggestions, However for PDF Print there are many reasons for no fixed size, Windows will keep changing printers to "last used" unless told other wise, and last used can be any paperkind or margins. However, In my case I frequently set default to my region (A4 Portrait) and as often as I can I set "No margins" but windows Edge will frequently reset that to big "Default". Perhaps I should set default to A4zerOmargin

K J
  • 8,045
  • 3
  • 14
  • 36