Excel VBA ExportAsFixedFormat Printing Too Large

[Aleoin]

All of the Excel documents that I print to pdf are standard 8.5x11 Letter size. When I print using File->Print with "PDF-XChange Standard" printer I get a perfectly formatted result reading 612.0pt wide by 792.0pt high that is then happy to print normally on Letter size paper. However, with "PDF-XChange Office" set as my computer's default printer (it is the only PDF-XChange option listed), when I use the ExportAsFixedFormat function in a VBA macro the result comes out as 941.5pt wide by 1218.5pt high and if you try to regular print the result from PDF-XChange on Letter/ANSI A paper it only prints the upper left 612pt x 792pt portion of the document which is unfortunately not acceptable.

In page setup within Excel the paper size is set to Letter/ANSI A, in the "PDF-XChange Standard" Printing Preferences the page size is set to Letter/ANSI A, and in the Edit Settings window from the PDF-XChange plugin/tab in Excel the page size is set to Letter/ANSI A.

If I switch my default printer to "Microsoft Print to PDF" the macro produces a correctly sized result, but the actually contents often don't print correctly so it's not really an option.

Aleoin

[Paul - Tracker Supp]

Hi Aleoin,

what version of PDF-XChange are you using? The first step in troubleshooting is always to test using the current build of our software, which as I write this is 8.0.333.0.

The other thought that occurred to me is that if you can print to the PDF-XChange Standard Printer properly without using the ExportAsFixedFormat function in a VBA macro, since the office Add-In is based on the same printer, can you not get the same result simply by not using that function?

Sorry if I have misunderstood what you are doing here.

[Aleoin]

Oh boy, I didn't think to check the version. We're only on 7.0 apparently, but it looks like IT here might have removed the updater. I might have to reach out to them for permission to update

And to clarify what I was doing, the principle goal was two small quality of life improvements - 1, defaulting the Save As window to the location of the excel file and 2, appending a numerical suffix to the default file name in the case that there is already a pdf of the same name in the folder. I implemented it in a macro and overwrote ctrl+p with it in excel. We have a fairly large file system and it's not uncommon to have to navigate 10+ folders to get from the last print-to-pdf location to the new project, and it's relatively easy to disseminate macros through the department as a little hotfix without trying to convince people to mess with the programs or defaults on their computer.

There are also a few small projects where I have to print 3 or 4 pdf's from one excel file systematically which was my original foray into ExportAsFixedFormat.

Thank you very much for your quick reply, and I'll post here if I manage to fix the issue with a version update.

[Aleoin]

Eh, I found the installers for previous versions so I went ahead and installed the newest version. I can just revert after /shrug

I did a range of tests on different excel files with both the regular and Lite printers (didn't have the Lite before), and the results are more confusing. For the regular printer, a couple files printed successfully but a bunch ran into the previous problem and one managed to print 3 different sized pages: a 941.5x1218.5 like before, an 864.3x1131.4, and 2 687.6x889.9, none of which were the correct 612.0x792.0.

With the Lite printer as my default I ran into the same thing as the Microsoft Print to PDF printer: the size seems to always be right but it didn't correctly display a couple of pages, though when printing normally to the Lite printer it came out correct. Odd addendum - using the pdf-xchange lite printer through the macro didn't produce a watermark like the normal print did.

I wish I could send you a copy of my biggest problem file but it has confidential data. If it helps, below is the macro I'm using:

EDIT: So I was just double checking that all the pages were set to Letter/ANSI A on that one major problem file, and I noticed that in the Scaling section of the Page Setup window, while not selected, the "Adjust to: " % entry was different for each sheet. Then, if you divide out the width or height of what an 8.5x11 file should be (612x792) by the size from the macro print, you get that Scaling factor for each sheet. So despite the "Fit to: 1 page wide by 1 tall" being selected, the scaling factor is coming into play. Unfortunately my tech experience with printing and page setup is extremely limited so I'm not sure what to do with this information :S

Code: Select all

Public Sub PrintToPDF()

    Dim wb As Workbook
    Dim fileName As String
    Dim fileFolder As String
    Dim pathFile As String
    Dim myFile As Variant
    
    On Error GoTo eh
    
    Set wb = ActiveWorkbook
    
    fileFolder = wb.Path
    
    If fileFolder = "" Then
        fileFolder = Application.DefaultFilePath
    End If

    fileFolder = fileFolder & "\"
    
    fileName = Split(wb.Name, ".")(0)
    
    pathFile = fileFolder & fileName & ".pdf"
    
    Dim i As Integer
        i = 0
        
    While bFileExists(pathFile)
        i = i + 1
        pathFile = fileFolder & fileName & " (" & i & ").pdf"
    Wend
    
    myFile = Application.GetSaveAsFilename( _
                InitialFileName:=pathFile, _
                FileFilter:="PDF Files (*.pdf), *.pdf", _
                Title:="Select Folder and file name to save as.")
    
    If myFile <> "False" Then
        ActiveSheet.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            fileName:=myFile, _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, _
            OpenAfterPublish:=True
    End If
    
exitHandler:
    Exit Sub
    
eh:
    MsgBox "Something went wrong.  You might want to just print normally.  If you have time, let ****** know what happened."
    Resume exitHandler

End Sub

Private Function bFileExists(rsFullPath As String) As Boolean
  bFileExists = CBool(Len(Dir$(rsFullPath)) > 0)
End Function

[TrackerSupp-Daniel]

Hello Aleoin,

While our lite printer does not offer these options, our PDF-XChange Standard Office addin's do:

It may be worth giving that option a try. If it works for your purposes, I can find the registry keys that you would need to push to enable this setting by default for your users. Then all then need to do is click the "convert to PDF" button on our toolbar in Office.

Kind regards,