VBA PDF Export of Large Spreadsheet

bifftannen

New Member
Joined
Mar 18, 2019
Messages
6
Good Day Gentleman,

I am hoping someone could help me out with the problem I am currently having - believe me that it would be beyond appreciated if anyone is willing to offer some of their time to help me with this.

Currently I have a very large spreadsheet(Approx 3080 Rows and with approx 908 columns) that I am working on exporting to PDF in 11x17 landscape mode.

Basically i've worked out how many columns and rows I can fit onto one page in Landscape @ 11x17 and my plan is to just break this document into sections going from top to bottom, and then move onto the next set of columns where I would go top to bottom all the way to the bottom row, then continue on.

Code:
Sub DebugPrint()
    
    With ActiveSheet.PageSetup
    .PrintArea = Range("A1:AN3080").Address
    
    .Orientation = xlLandscape
    .FitToPagesWide = 1
    .FitToPagesTall = False
    .PaperSize = xlPaper11x17
    End With
    
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:="C:\Test.pdf" _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=True
End Sub


Currently the code above works great. It will render 85 pages using columns A to AN on each page while incrementing the rows as the pages go on. So I get a great 11x17 landscape PDF document output as I want.

The problem I am having is I need to somehow move on to range "("AO1:CO3080")" and do the same thing so it builds 85 more pages to the same document with each page using columns AO to CO while incrementing the rows as the pages go on. And then after that i would do range ("CP1:DQ3080") etc, etc, etc.

Is there anyway to do this? I think one way of doing this would be running my function above, writing the PDF file, then changing the range to AO1:CO3080 and appending 85 more pages to that same PDF document, etc, etc. BUT the problem with that is I would need Adobe Acrobat Pro (which i cannot afford) in order to use their API to append documents.

Is there a way to do this using VBA only?

I've attached this quick drawing I made to kind of go along with my explanation (may or may not be helpful - if there is anything you need from me please let me know)

**Many thanks to anyone willing to spare some time to help me out here. Will pay it forward

 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

jolivanes

Well-known Member
Joined
Sep 5, 2004
Messages
1,624
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
Does this come close to your requirements?
Change the references as required.
Go for lunch while it is running!!!!!

Code:
Sub AAAAA()
Dim i As Long, ii As Long, j As Long, jj As Long, PDF As String
ii = InputBox(Prompt:="How many Columns do you want per Page?.", Title:="Columns per Page.")
jj = InputBox(Prompt:="How many Rows do you want per Page?.", Title:="Rows per Page.")
    For i = 1 To ActiveSheet.UsedRange.Columns.Count Step ii
        For j = 1 To ActiveSheet.UsedRange.Rows.Count Step jj
        ActiveSheet.PageSetup.PrintArea = Range(Cells(j, i), Cells(j + (jj - 1), i + (ii - 1))).Address
            PDF = "C:\Temp_Folder\" & "Column " & i & " To " & i + ii & " Rows " & j & " To " & j + (jj - 1) & ".pdf"    '<----- Change as required
                ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDF
        Next j
    Next i
End Sub
 

bifftannen

New Member
Joined
Mar 18, 2019
Messages
6
Does this come close to your requirements?
Change the references as required.
Go for lunch while it is running!!!!!

Code:
Sub AAAAA()
Dim i As Long, ii As Long, j As Long, jj As Long, PDF As String
ii = InputBox(Prompt:="How many Columns do you want per Page?.", Title:="Columns per Page.")
jj = InputBox(Prompt:="How many Rows do you want per Page?.", Title:="Rows per Page.")
    For i = 1 To ActiveSheet.UsedRange.Columns.Count Step ii
        For j = 1 To ActiveSheet.UsedRange.Rows.Count Step jj
        ActiveSheet.PageSetup.PrintArea = Range(Cells(j, i), Cells(j + (jj - 1), i + (ii - 1))).Address
            PDF = "C:\Temp_Folder\" & "Column " & i & " To " & i + ii & " Rows " & j & " To " & j + (jj - 1) & ".pdf"    '<----- Change as required
                ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDF
        Next j
    Next i
End Sub

Thank you very much sir! Im booting up my Windows Laptop in about 5 minutes and then Ill give it a try and let you know :)
 

bifftannen

New Member
Joined
Mar 18, 2019
Messages
6

ADVERTISEMENT

Does this come close to your requirements?
Change the references as required.
Go for lunch while it is running!!!!!

Code:
Sub AAAAA()
Dim i As Long, ii As Long, j As Long, jj As Long, PDF As String
ii = InputBox(Prompt:="How many Columns do you want per Page?.", Title:="Columns per Page.")
jj = InputBox(Prompt:="How many Rows do you want per Page?.", Title:="Rows per Page.")
    For i = 1 To ActiveSheet.UsedRange.Columns.Count Step ii
        For j = 1 To ActiveSheet.UsedRange.Rows.Count Step jj
        ActiveSheet.PageSetup.PrintArea = Range(Cells(j, i), Cells(j + (jj - 1), i + (ii - 1))).Address
            PDF = "C:\Temp_Folder\" & "Column " & i & " To " & i + ii & " Rows " & j & " To " & j + (jj - 1) & ".pdf"    '<----- Change as required
                ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDF
        Next j
    Next i
End Sub

Hey Jolivanes,

So I tested the code and I think you are very close. This is exactly what I needed to do BUT instead of all in separate pdf files - i need it to be all the same PDF file. Is there any sort of code that can now combine all of those files? Or is there a way to do exactly what you just did there - but output all of it into the same file? Thanks a lot for taking the time to help!
 

jolivanes

Well-known Member
Joined
Sep 5, 2004
Messages
1,624
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
Sorry about that.
I misunderstood, or maybe didn't read it right, your question.
Can't you copy the 2nd and 3rd, and maybe more, Range of Rows under one another and print it?
 

jolivanes

Well-known Member
Joined
Sep 5, 2004
Messages
1,624
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows

ADVERTISEMENT

Are the columns different widths because you mention A to AN, AO to CO and CP to DQ
A to AN = Column 1 to Column 40
AO to CO = Column 41 to Column 93
CP to DQ = Column 94 to Column 121
 

bifftannen

New Member
Joined
Mar 18, 2019
Messages
6
No problem!! That would also work and might actually be faster to do one publish to PDF. Any idea how I could do that? I.e copy them all to a new sheet so the original isn’t modified?

Also sorry about the columns. I wasn’t being precise and was just kind of guessing quickly. They are all in fact the same width
 

jolivanes

Well-known Member
Joined
Sep 5, 2004
Messages
1,624
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
Try this with a copy of your original.
Change references if and where required.
Code:
Sub Copy_And_Print()
Dim lr As Long, lc As Long, i As Long, j As Long, PDF As String
lr = Cells(Rows.Count, 1).End(xlUp).Row
lc = Cells(1, Columns.Count).End(xlToLeft).Column
Application.ScreenUpdating = False
j = InputBox(Prompt:="How many Columns do you want per Page?.", Title:="Columns per Page.")
    For i = 1 + j To lc Step j
        Range(Cells(1, i), Cells(lr, i + (j - 1))).Copy Cells(Rows.Count, 1).End(xlUp).Offset(1)
    Next i
        ActiveSheet.PageSetup.PrintArea = Range(Cells(1, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 1)).Resize(, j).Address
            PDF = "C:\Test\" & "Printout" & ".pdf"    '<---- Change as required
                ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDF
ActiveSheet.Range(Cells(lr + 1, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 1)).Resize(, j).ClearContents
Application.ScreenUpdating = True
End Sub
 
Last edited:

bifftannen

New Member
Joined
Mar 18, 2019
Messages
6
Try this with a copy of your original.
Change references if and where required.
Code:
Sub Copy_And_Print()
Dim lr As Long, lc As Long, i As Long, j As Long, PDF As String
lr = Cells(Rows.Count, 1).End(xlUp).Row
lc = Cells(1, Columns.Count).End(xlToLeft).Column
Application.ScreenUpdating = False
j = InputBox(Prompt:="How many Columns do you want per Page?.", Title:="Columns per Page.")
    For i = 1 + j To lc Step j
        Range(Cells(1, i), Cells(lr, i + (j - 1))).Copy Cells(Rows.Count, 1).End(xlUp).Offset(1)
    Next i
        ActiveSheet.PageSetup.PrintArea = Range(Cells(1, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 1)).Resize(, j).Address
            PDF = "C:\Test\" & "Printout" & ".pdf"    '<---- Change as required
                ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDF
ActiveSheet.Range(Cells(lr + 1, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 1)).Resize(, j).ClearContents
Application.ScreenUpdating = True
End Sub


Very close!!

Basically what you did works great - I can fit about 78 columns and 76 rows from my document onto a 11x17 page in Landscape view - so with your example I put in 78 as my number and it outputs 55 pages (amount of pages to get all the way to the bottom of the sheet). BUT what I was hoping to accomplish would be to enter something like "156" columns and have it it generate 110 pages total. 55 of the pages would be columns 1 to 78 top to bottom, and then pages 56 to 105 would be columns 79 to 156. Etc etc.

However when I put in something like 156, it just fits 156 columns onto one page, which when printed just isn't visible/readable.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,001
Messages
5,526,196
Members
409,686
Latest member
Tori83

This Week's Hot Topics

Top