Print pages

[Martin]

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.

[Ron de Bruin]

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" <[email protected]> wrote in message news:%[email protected]...
> 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.
>
>
>

[William]

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

[email protected]


"Martin" <[email protected]> wrote in message
news:%[email protected]...
> 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.
>
>
>

[Martin]

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" <[email protected]> wrote in message
news:[email protected]...
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

[email protected]


"Martin" <[email protected]> wrote in message
news:%[email protected]...
> 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 McRitchie]

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" <[email protected]> wrote in message news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> 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
>
> [email protected]
>
>
> "Martin" <[email protected]> wrote in message
> news:%[email protected]...
> > 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.
> >
> >
> >
>
>

[Martin]

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" <[email protected]> wrote in message
news:%[email protected]...
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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> 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
>
> [email protected]
>
>
> "Martin" <[email protected]> wrote in message
> news:%[email protected]...
> > 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.
> >
> >
> >
>
>