All columns of excelsheet are not fitted in same page of pdf; while converting using Excel VBA

Question

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?

Solution 1

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.

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

Solution 2

First select the range you want to print and set it as PrintArea. And then run this code, this work for me with an 79 columns 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

Solution 3

add this to your code, it will force everything to print on one sheet wide, but still let it print over multiple sheets tall

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

also set your margins to "Narrow"

Solution 4

The problem is that you need to Select the 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 the PageSetupbecasue each page size has a maximum pixel limit as you were heading towards in your comment.

The Page Size is set to Oversize A0 which should more than cater for your 100x1500 UsedRange. Here you change the page size with the FitToPages... = 1 to check that your Range is within the print lines.

The FitToPagesWide and FitToPagesTall is to fit everything onto 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

Note that the Page will appear Blank, you will need to Zoom in alot to view the data

View more solutions

Comments

[A.S.H]

Did you check the Page Setup of your Worksheet? It is possible that you have set large margins, a not-appropriate page size, etc. You can also control the print area of the sheet, by selecting View --> Page Layout

[Charvi]

My Page size is A4 and page layout --> Orientation is set to Landscape Thanks for the advise. Now I'll also check with other page setup, such as scale, will check whether I can convert excel manually to PDF and will come back to you

[Scott Holtzman]

Yes, sometimes its just about trial and error until you get it exactly as you need it ... :)

[Charvi]

Ya @ScottHoltzman. I tried with decreasing scale till 10% and I was able to publish almost 55 columns in a single page of pdf. Still, I have to publish few more columns in the same page :) And excel automatically changes scale to 10% if I try to lower scale than it :)

[Scott Holtzman]

can you publish it on two pages (at 10% isn't really unreadable anyway?) if you keep some columns on the left to print for each sheet to identify the row of data on the 2nd sheet?

[Grade 'Eh' Bacon]

There is a printing option to force your data to be 1 page wide and/or 1 page high. When you click this [under page layout -> scale to fit], it shows you how much it shrinks your data to fit it on 1 page. If you record a macro of you doing this, it will provide you the VBA code to set that option.

[Charvi]

Ya. Many places were unreadable. To keep some columns on the left to print for each sheet to identify the row of data on the 2nd sheet is a good idea. Only thing is that, I have to adjust such columns manually. If I do not get any other solution, am surely going for it. :)

[Avidan]

Thank you @neuralgroove for the answer. :) Tried with Trial and error by adding the above code. Tried with creating using two with blocks and also adding the above code in previous with block (combining both the blocks in single with block). Also set margins to 'narrow'. I get same results as prior. Can you tell me, How I can incorporate it with earlier code ?

[Avidan]

Thank you for this answer and explanation. Though still am able to export upto only 50-60 columns (after selecting A3 page size), it helped me to learn how to record a macro to adjust page setup. +1 for it. Few of the columns exported are unreadable; is it possible to widen the width of page? So that, I would be able to read those correctly? (considering - am not going to print generated PDF on paper and I need it only to view as a soft copy)

[EEM]

If printing it on A3 paper with minimum zoom (i.e. 10%) still cannot fit everything in one page, then what you intent is not doable. Your original question is about having the worksheet printed in one page, and that what this answer is about. Now you are showing concern about readability, then you need to play with the .FitToPagesWide and .FitToPagesTall and decide what's the best compression that allows readability.

[EEM]

@Avidan apologies did not realized the original question is not actually yours. As I said earlier the minimum zoom possible is 10%, try with the adjust page setting and let us know. Questions, 1) how wide is your print area no only in columns number but also addition of the width of all columns; 2) how many rows has the print area and 3) what font size is used. Need this to try to replicate your case. However it will better if you can share your file...

[Avidan]

Thank you @EEM for the guidance. I'll check with properties, .FitToPagesWide and .FitToPagesTall - whether I'll be able to fit our data into a wider page. At this point, even I do believe in your sentence: what I intent is not doable., but still am curious, whether it's possible by widening the size(width) of page. (I don't have to print it physically; only in a softcopy - PDF)

[Avidan]

I gone through this post: stackoverflow.com/questions/5116828/… and 1) Width of all columns together is 2750 pixels. (total no. of columns - 100). 2) There are around 1500 rows. 3) Arial and Calibri, Font size 11; and am sorry, I won't be able to share the file as It has some sensitive data. ...and you don't have to be apologised. :) I found this question similar to my requirement, so thought of offering bounty instead of asking a new question. Regards,

[Avidan]

Buddy, Thanks for the answer. This gives me still the same result though. Am sorry, yesterday someone has downvoted the answer.

[Jean-Pierre Oosthuizen]

@Avidan have a look at the script now, works every time with me

[Avidan]

Thank you for the edit :) Still I get same results. I guess, What am trying is may not be doable As, 1) Width of all columns together is 2750 pixels. 2) Total no. of columns - 100. Is it so? stackoverflow.com/questions/33127693/…

[EEM]

I checked further and there a much bigger paper size called E Size which is 86.36 cm x 111.76 cm as per the measurements your page should be portrait instead of landscape. I'm updating the procedure to show this alternatives. try and let me know.

[Avidan]

Let us continue this discussion in chat.

[Avidan]

Thanks for the new edit. I was able to export more columns than the prior. (same as other answer on this question, almost 85 columns. Still 15 columns couldn't fit in the page) . +1 for it. Also, few of my columns are not readable. Hopefully, using paper size as 'E' will solve this problem. stackoverflow.com/questions/33127693/…

[Jean-Pierre Oosthuizen]

Page Size 92x92 is 233cm x 233cm which is bigger than size E. What printer is set as your default printer? The printer itself could be preventing you if it's a really printer. In my testing the script worked for a total column width of more than 2700pxl

[Avidan]

Thanks buddy for the information. :) I won't be printing the exported PDF on paper. I just need it to view as a soft copy. Is it possible to adjust this size of paper in Microsoft excel properties?

[Jean-Pierre Oosthuizen]

Page layout > Page Setup > Size. What I mean is, the avaiable sizes in PageSize is dependant on what printer is selected. Regardless if you are going to print the page or not. I have PDFCreator set as my default printer to avoid this issue when saving as PDF

[Avidan]

Let us continue this discussion in chat.

[Avidan]

Thank u buddy for the answer. +1, am able to export bit less columns than 79 as for me pixel size is more now.