wrong PDF output size with ExportAsFixedFormat in Excel VBA

Question

I use Excel 2010 with VBA.

I have the following code :

mySh.PageSetup.PaperSize = xlPaperLetter
mySh.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:=filePathTmp, _
    Quality:=xlQualityStandard, IncludeDocProperties:=False, _
    IgnorePrintAreas:=False, OpenAfterPublish:=False

It generates a PDF without error but the size is not correct. I get a 22.26 x 28.80 cm instead of the 21.59 x 27.94 cm that I would like to obtain.

Any ideas ?

Thanks for your help

Solution 1

I use Excel 2010 and had this problem. I fixed it by changing the printer in Excel's dropdown list on the Print section of the File tab. My default printer is a Konica Minolta copy machine. I changed it to Local Printer (which doesn't actually exist) and it worked! I now have the VBA code returning an 8.5x11 PDF! Now I don't have to use our clunky old laptop with an old Office to get the right size.

Note that picking the Adobe PDF printer did not fix the problem. Just try messing with the different printer choices, or make a fake printer in your computer's Devices and Printers and use that. I hope it works for others with the same issue!

Solution 2

I set the print area where I wanted it: View-Page Break Preview. Then File-Print-Scaling = Fit Sheet on One Page Or File-Print-Page Setup-Fit to: 1 pages wide by 1 tall.

Hope it helps someone.

Comments

[Winand]

Xerox 7835 does the same thing. HP 5100, Fax, XPS, OneNote printers don't.

[Winand]

Just in case, all printers can be found in SOFTWARE\Microsoft\Windows NT\CurrentVersion\Devices

[wayneh]

Been beating my head against the wall for three days on this one. Setting the default printer to MS XPS or Fax works for me. I've verified it on 4 machines, 3 OSs (WIn 7Pro 64, Server 2008 sp2, Server 2012), 2 versions of Excel (201/2016)

[wayneh]

@SethReuter I'm not completely sure why it works - I think it has something to do with the Windows printer driver.

[DrMarbuse]

Thank You @Crystal, How crazy is that. We will celebrate a 10 year anniversary of that behaviour soon! In Windows 10 the installed printers are to be found in HKLM\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Print\Printers.

[DrMarbuse]

Microsoft found out and describes the problem without solution. See question 70656341 on how to find printer names on the system.