Using Pages or pagebreaks to save PDFs

[kwread]

I have an excel worksheet with ~20 different pages that i would like to save individually as pdfs to specific folders. This is a process that I have to repeat often with fresh data.

My 1st idea is a macro that uses the ExportAsFixedFormat function, where i can use variables to update the location/filename each week.

I would like to use the pages themselves as an object to export but the example i followed to create a 'page' object:

Dim objPage As Page

Set objPage = ActiveWorkbook.ActiveWindow _
.Panes(1).Pages.Item(1)

Is giving me a Run-time 438 error (Object doesnt support this property or method)
Any thoughts on this?


Alternatively i could use the the VPageBreak option to create ranges for each page but im having trouble turning vpagebreak locations into ranges.

I am a relative newbie when it comes to VBA so sample code would be very helpful. Below is the test spreadsheet with my (very simplified) data structure, where the pages split before Entity 1, entity 2, etc



[TABLE="class: grid, width: 192"]
<colgroup><col span="3"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Entity 1 [/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]data[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]data[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]data[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]data[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]data[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]THINGS[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]data[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]data[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Entity 2 [/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]data[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]data[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]data[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]data[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]data[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]data[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]THINGS [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]data[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]data[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]data[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]data[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]data[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[kwread]

Just as an update, i found a workaround by creating little 'Entity 1 End' tags that go below each set of data, just outside the print area. The below is working (note some of that code is for testing purposes). Its not the elegant solution based off print area i was hoping for but seems to be quite functional.




Sub ByLenderExport()
'
' Macro that prints each lenders section to pdf and saves as new excel
'This is the test version


' Create the Range and lender variables
Dim rng As Range


Dim CurLender(1 To 2) As String
CurLender(1) = "Entity 1"
CurLender(2) = "Entity 2"


'Iterate through each lender
For i = 1 To 2


'create the start cell and ending cell for each lenders range
startcell = "A" & Application.WorksheetFunction _
.Match(CurLender(i), Worksheets(1).Range("A:A"), 0)
endcell = "c" & Application.WorksheetFunction _
.Match(CurLender(i) & " End", Worksheets(1).Range("A:A"), 0) - 1

'set the range based on the cells
Set rng = Application.Range(Cell1:=startcell, Cell2:=endcell)

'export the range as a pdf, to the folder and name based on lender
rng.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\kread\Desktop\PDF split test" & CurLender(i), Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

'export the range as an excel
rng.Copy
Workbooks.Add
ActiveSheet.Paste
ActiveWorkbook.SaveAs Filename:="C:\Users\kread\Desktop\PDF split test" & CurLender(i)
Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True

'MsgBox startcell
'MsgBox endcell


Next i






End Sub

 

[Domenic]

I was just about to log off when I noticed your post. So I haven't really read through it, but see if this helps...

http://www.mrexcel.com/forum/excel-questions/982261-creating-pdf-files.html

 

[kwread]

Thank you i think that would get at exactly what i was trying to do:

Specifically the HPageBreaks location is think is the element i was looking for.

Thanks!!
EndRow = ActiveSheet.HPageBreaks(PageNum).Location.Row - 1