5

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

Improve this question
2
  • I have had this issue previously but cannot remember if I fixed it or hacked it. Have you investigated how the zoom affects results? Commented Feb 26, 2013 at 11:04
  • I set the zoom to 100% in my PageSetup subroutine. Basically, when I print as PDF using the Excel interface, it works fine but not with ExportAsFixedFormat which is supposed to be a mirror of the Excel functionnality). Commented Feb 27, 2013 at 0:40

4 Answers 4

Reset to default
8

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!

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Xerox 7835 does the same thing. HP 5100, Fax, XPS, OneNote printers don't.
Just in case, all printers can be found in SOFTWARE\Microsoft\Windows NT\CurrentVersion\Devices
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)
@SethReuter I'm not completely sure why it works - I think it has something to do with the Windows printer driver.
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.
|
1

We had the same problema and soved it changing at devices and printers the default printing options:

-Control Panel->Devices and Printers->Select the printer by clicking once to it.

-Second mouse button->Printer properties->Advanced->Printing defaults->Advanced.

-Choose paper size and click OK.

Why not to change it at excel? Because somthing related to permissions between Excel and the service running spool or...

That default setting makes the macro to export to the paper size correctly.

If it Works for you, is thanks to our IT department, not to me :P

Improve this answer

Comments

1

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.

Improve this answer

Comments

0

I also use Excel 2010 and had the same problem. When using ExportAsFixedFormat, the page won't scale. From what I understand, it is the same as using the Save As with file type pdf. This means that you need to need to scale your workbook to fit the page that you require.

If you:

-Open printer settings and select 'No Scaling'

-Page Layout > Print Area > Clear Print Area

-Select View > Page Break View

It will show you the page area. If you scale your worksheet to this it will save to pdf using ExportAsFixedFormat properly.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.