I have the current code which selects the PDF printer of whatever PC it is used on, however depending on the screen resolution of the PC the margins will change so the printed PDF looks different depending on which PC is used.
I am looking for a way to print the same PDF on whatever PC or resolution the spreadsheet is opened on.
Any help greatly appreciated.
I am looking for a way to print the same PDF on whatever PC or resolution the spreadsheet is opened on.
VBA Code:
Function FindPrinter(ByVal PrinterName As String) As String
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
Sub Printallpages()
Dim filenamex As String
Dim sCurrentPrinter As String
sCurrentPrinter = Application.ActivePrinter
Application.ActivePrinter = FindPrinter("Microsoft Print to PDF")
ThisWorkbook.Sheets(Array("Sheet1", "Sheet2")).Select
Path = CreateObject("WScript.Shell").specialfolders("Desktop")
filenamex = Path & "" & "Document"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
filenamex, Quality:=xlQualityStandard, IncludeDocProperties:=False, _
IgnorePrintAreas:=False, OpenAfterPublish:=True
'print document, then go back to the original printer
Application.ActivePrinter = sCurrentPrinter
End Sub
Any help greatly appreciated.