Hi all,

I have an Excel worksheet that consists of several pages. I need code to
print each individual page to PDF files. I have code to print to PDF via
Adobe Distiller but cannot figure out how to extract each individual page. I
am able to count vertical page breaks using VPageBreaks.Count, but how do I
extract \ print each page? Any help would be appreciated.

Thanks.

Hi Martin

Maybe this macro is useful and you can use a part of it
http://www.rondebruin.nl/hpagebreaks.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Martin" wrote in message ...
Hi all,

I have an Excel worksheet that consists of several pages. I need code to
print each individual page to PDF files. I have code to print to PDF via
Adobe Distiller but cannot figure out how to extract each individual page. I
am able to count vertical page breaks using VPageBreaks.Count, but how do I
extract \ print each page? Any help would be appreciated.

Thanks.

Hi Martin

Sub PDFTest()
On Error Resume Next
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False
Dim PSFileName As String, PDFFileName As String
Dim myPDF As PdfDistiller, x As String, i As Integer
x = Left(ActiveWorkbook.FullName, Len(ActiveWorkbook.FullName) - 4)
PSFileName = x & ".ps"
Set myPDF = New PdfDistiller
For i = 1 To ExecuteExcel4Macro("Get.Document(50)")
PDFFileName = x & " (Sheet" & i & ").pdf"
ActiveSheet.PrintOut From:=i, To:=i, prtofilename:=PSFileName
myPDF.FileToPDF PSFileName, PDFFileName, ""
Kill Left(PDFFileName, Len(PDFFileName) - 3) & "log"
Kill (PSFileName)
Next i
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayAlerts = True
End Sub


--

Regards

William

XL2003




"Martin" wrote in message
...
Hi all,

I have an Excel worksheet that consists of several pages. I need code to
print each individual page to PDF files. I have code to print to PDF via
Adobe Distiller but cannot figure out how to extract each individual page.
I
am able to count vertical page breaks using VPageBreaks.Count, but how do
I
extract \ print each page? Any help would be appreciated.

Thanks.

Thank you both for your help.

William - Awsome code! How can I get the value of a cell in each page and
add to the file name?

Thanks again.

"William" wrote in message
...
Hi Martin

Sub PDFTest()
On Error Resume Next
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False
Dim PSFileName As String, PDFFileName As String
Dim myPDF As PdfDistiller, x As String, i As Integer
x = Left(ActiveWorkbook.FullName, Len(ActiveWorkbook.FullName) - 4)
PSFileName = x & ".ps"
Set myPDF = New PdfDistiller
For i = 1 To ExecuteExcel4Macro("Get.Document(50)")
PDFFileName = x & " (Sheet" & i & ").pdf"
ActiveSheet.PrintOut From:=i, To:=i, prtofilename:=PSFileName
myPDF.FileToPDF PSFileName, PDFFileName, ""
Kill Left(PDFFileName, Len(PDFFileName) - 3) & "log"
Kill (PSFileName)
Next i
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayAlerts = True
End Sub


--

Regards

William

XL2003




"Martin" wrote in message
...
Hi all,

I have an Excel worksheet that consists of several pages. I need code to
print each individual page to PDF files. I have code to print to PDF via
Adobe Distiller but cannot figure out how to extract each individual page.
I
am able to count vertical page breaks using VPageBreaks.Count, but how do
I
extract \ print each page? Any help would be appreciated.

Thanks.

Hi Martin,

change
PSFileName = x & ".ps"

to
PSFileName = x & "_" & TRIM(RANGE("A1").value) & ".ps"

if you are including a date be sure to format with year first, perhaps like:
PSFileName = x & "_" & TEXT(RANGE("A1").value,"yyyy_mmdd" & ".ps"

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Martin" wrote in message ...
Thank you both for your help.

William - Awsome code! How can I get the value of a cell in each page and
add to the file name?

Thanks again.

"William" wrote in message
...
Hi Martin

Sub PDFTest()
On Error Resume Next
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False
Dim PSFileName As String, PDFFileName As String
Dim myPDF As PdfDistiller, x As String, i As Integer
x = Left(ActiveWorkbook.FullName, Len(ActiveWorkbook.FullName) - 4)
PSFileName = x & ".ps"
Set myPDF = New PdfDistiller
For i = 1 To ExecuteExcel4Macro("Get.Document(50)")
PDFFileName = x & " (Sheet" & i & ").pdf"
ActiveSheet.PrintOut From:=i, To:=i, prtofilename:=PSFileName
myPDF.FileToPDF PSFileName, PDFFileName, ""
Kill Left(PDFFileName, Len(PDFFileName) - 3) & "log"
Kill (PSFileName)
Next i
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayAlerts = True
End Sub


--

Regards

William

XL2003




"Martin" wrote in message
...
Hi all,

I have an Excel worksheet that consists of several pages. I need code to
print each individual page to PDF files. I have code to print to PDF via
Adobe Distiller but cannot figure out how to extract each individual page.
I
am able to count vertical page breaks using VPageBreaks.Count, but how do
I
extract \ print each page? Any help would be appreciated.

Thanks.

David thanks. The cell value will actually change from page to page. The row
is defined under Page Setup Print titles as $1:$8 so I need to pull the
value off of each cell in this row.

Thanks.

"David McRitchie" wrote in message
...
Hi Martin,

change
PSFileName = x & ".ps"

to
PSFileName = x & "_" & TRIM(RANGE("A1").value) & ".ps"

if you are including a date be sure to format with year first, perhaps like:
PSFileName = x & "_" & TEXT(RANGE("A1").value,"yyyy_mmdd" & ".ps"

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Martin" wrote in message
...
Thank you both for your help.

William - Awsome code! How can I get the value of a cell in each page and
add to the file name?

Thanks again.

"William" wrote in message
...
Hi Martin

Sub PDFTest()
On Error Resume Next
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False
Dim PSFileName As String, PDFFileName As String
Dim myPDF As PdfDistiller, x As String, i As Integer
x = Left(ActiveWorkbook.FullName, Len(ActiveWorkbook.FullName) - 4)
PSFileName = x & ".ps"
Set myPDF = New PdfDistiller
For i = 1 To ExecuteExcel4Macro("Get.Document(50)")
PDFFileName = x & " (Sheet" & i & ").pdf"
ActiveSheet.PrintOut From:=i, To:=i, prtofilename:=PSFileName
myPDF.FileToPDF PSFileName, PDFFileName, ""
Kill Left(PDFFileName, Len(PDFFileName) - 3) & "log"
Kill (PSFileName)
Next i
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayAlerts = True
End Sub


--

Regards

William

XL2003




"Martin" wrote in message
...
Hi all,

I have an Excel worksheet that consists of several pages. I need code to
print each individual page to PDF files. I have code to print to PDF via
Adobe Distiller but cannot figure out how to extract each individual
page.
I
am able to count vertical page breaks using VPageBreaks.Count, but how
do
I
extract \ print each page? Any help would be appreciated.

Thanks.