Am trying to convert microsoft excel file with large number of columns (70+) into pdf using a Excel VBA code.
In active workbook, am trying to save 'Sheet1' to PDF format at required path. I have the following code.
Sub GetSaveAsFilename()
Dim fileName As String
fileName = Application.GetSaveAsFilename(InitialFileName:="", _
FileFilter:="PDF Files (*.pdf), *.pdf", _
Title:="Select Path and FileName to save")
If fileName <> "False" Then
With ActiveWorkbook
.Worksheets("Sheet1").ExportAsFixedFormat Type:=xlTypePDF, fileName:= _
fileName, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End With
End If
End Sub
When am running the VBA code and saving the pdf file, I see that; the whole excelsheet is not fitted in same page. It's displaying some content in next page.
(Only few columns appear in first page, remaining appear on next page and so on..).
I checked with How to publish a wide worksheet in PDF format?.
But, setting page layout to landscape and converting excel file manually to PDF; also displays some columns in next pages.
There are many Free Excel to PDF Converters available online, which give me same results.
Is there any function available in VBA, through which I can fit all the columns in a single page of PDF?
Best Answer
The problem is with the Page Setup settings, I have done some minor changes to your code and added a procedure to perform the page setup settings, when launching the procedure you can select the paper size, however be aware the minimum zoom allowed is 10% (see PageSetup Members (Excel)). Therefore, if even at 10% the Print Area does not fit in one page I suggest to chose a larger paper size (i.e. A3) to generate an one page PDF, then when printing the Pdf select fit to page. The procedure also gives you the change to play with the margins, when generating PDF's I set all margins at 0, but you can changed as it fits your goals.