All excelsheet columns are not installed on one pdf page; when converting using Excel VBA

Question

I am trying to convert excel microsoft file with many columns (70+) to pdf using Excel VBA code.

In the active book, I am trying to save "Sheet1" in PDF format on the right 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 I run VBA code and save the PDF file, I see this; the entire special sheet is not installed on one page. It displays some content on the next page.

(Only a few columns are displayed on the first page, the rest are displayed on the next page, etc.).

I checked with How to publish a large worksheet in PDF? .

But, by customizing the page layout for the landscape and converting the excel file manually to PDF; also displays some columns on the following pages.

There are many free Excel to PDF Converter available on the Internet that give me the same results.

Is there any function available in VBA through which I can put all columns on one PDF page?

Answer 1

The problem is the Page Setup settings, I made some minor changes to your code and added a procedure to configure the page settings, when you start the procedure you can choose the paper size, but keep in mind that the minimum scaling is 10% (see PageSetup Members ( Excel) ). Therefore, even if 10% of the print area does not fit on one page, I suggest choosing a larger paper size (for example, A3) to create a PDF page for one page, and then when fitting Pdf to the page. This procedure also gives you the opportunity to play with fields, when creating a PDF, I set all the fields to 0, but you can change them as it suits your goals.

 Sub Wsh_LargePrintArea_To_Pdf() Dim WshTrg As Worksheet Dim sFileName As String sFileName = Application.GetSaveAsFilename( _ InitialFileName:="", _ FileFilter:="PDF Files (*.pdf), *.pdf", _ Title:="Select Path and FileName to save") If sFileName <> "False" Then Rem Set Worksheet Target Set WshTrg = ActiveWorkbook.Worksheets("Sheet1") Rem Procedure Update Worksheet Target Page Setup 'To Adjust the Page Setup Zoom select the Paper Size as per your requirements 'Call Wsh_Print_Setting_OnePage(WshTrg, xlPaperLetter) 'Call Wsh_Print_Setting_OnePage(WshTrg, xlPaperA4) 'To Adjust the Page Setup Zoom select the Paper Size as per your requirements 'If the Print Still don't fit in one page then use a the largest Paper Size (xlPaperA3) 'When printing the Pdf you can still selet to fix to the physical paper size of the printer. 'Call Wsh_Print_Setting_OnePage(WshTrg, xlPaperA3) 'This is the largest paper i can see in my laptop is 86.36 cm x 111.76 cm Call Wsh_Print_Setting_OnePage(WshTrg, xlPaperEsheet) Rem Export Wsh to Pdf WshTrg.ExportAsFixedFormat _ Type:=xlTypePDF, _ fileName:=sFileName, _ Quality:=xlQualityStandard, _ IncludeDocProperties:=True, _ IgnorePrintAreas:=False, _ OpenAfterPublish:=False End If End Sub Sub Wsh_Print_Setting_OnePage(WshTrg As Worksheet, ePaperSize As XlPaperSize) On Error Resume Next Application.PrintCommunication = False With ActiveSheet.PageSetup .LeftMargin = Application.InchesToPoints(0) .RightMargin = Application.InchesToPoints(0) .TopMargin = Application.InchesToPoints(0) .BottomMargin = Application.InchesToPoints(0) .HeaderMargin = Application.InchesToPoints(0) .FooterMargin = Application.InchesToPoints(0) '.Orientation = xlLandscape .Orientation = xlPortrait .PaperSize = ePaperSize .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = 1 End With Application.PrintCommunication = True End Sub 

Answer 2

add this to your code, it will make everything print on one sheet in width, but still let it print on several sheets in height

 With Worksheets("Sheet1").PageSetup .FitToPagesWide = 1 .FitToPagesTall = False End With 

also set your Narrow fields

Answer 3

First, select the range that you want to print, and set it as PrintArea. And then run this code, this work for me with a 79 column sheet

 Sub saveAsPDF() Dim MyPath Dim MyFolder With Sheet1.PageSetup '.CenterHorizontally = True .CenterVertically = True .Orientation = xlLandscape .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = 1 .BottomMargin = 0 .TopMargin = 0 .RightMargin = 0 .LeftMargin = 0 End With MyPath = ThisWorkbook.Path MyFolder = Application.GetSaveAsFilename(MyPath, "PDF Files (*.pdf),*.pdf") If MyFolder = False Then Exit Sub Sheet1.ExportAsFixedFormat Type:=xlTypePDF, _ Filename:=MyFolder, _ Quality:=xlQualityStandard, _ IncludeDocProperties:=True, _ IgnorePrintAreas:=False, _ OpenAfterPublish:=False End Sub 

Answer 4

The problem is that you need to select UsedRange and then use Selection.ExportAsFixedFormat

 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 'Selecting the Used Range in the Sheet ActiveWorkbook.Worksheets("Sheet1").UsedRange.Select 'Saving the Selection - Here is where the problem was Selection.ExportAsFixedFormat Type:=xlTypePDF, fileName:=fileName, _ Quality:=xlQualityStandard, IncludeDocProperties:=False, _ IgnorePrintAreas:=False, OpenAfterPublish:=True End If End Sub 

EDIT:

The problem was PageSetup , because each page size has a maximum pixel limit when you are sent to your comment.

The page size is set to Oversize A0, which should more than satisfy 100x1500 UsedRange . Here you resize the page using FitToPages... = 1 to verify that your Range is within the print lines. A.

FitToPagesWide and FitToPagesTall should all fit on one page.

 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 'Suspending Communicaiton with Printer to Edit PageSetup via Scripting Application.PrintCommunication = False 'Setting Page Setup With ActiveSheet.PageSetup .FitToPagesWide = 1 .FitToPagesTall = 1 ' Setting Page Size to 92x92 inch Should cater for your data .PaperSize = 159 End With 'Enabling Communicaiton with Printer Application.PrintCommunication = True 'Selecting the Used Range in the Sheet ActiveWorkbook.Worksheets("Sheet1").UsedRange.Select 'Saving the Selection - Here is where the problem was Selection.ExportAsFixedFormat Type:=xlTypePDF, fileName:=fileName, _ Quality:=xlQualityStandard, IncludeDocProperties:=True, _ IgnorePrintAreas:=True, OpenAfterPublish:=True End If End Sub 

Please note that the page will be blank, you will need to zoom in to view the data