Saving as .pdf

[ryanmhess]

Currently I have a macro that selects sheet 3 of my workbook, copies it, and
saves it as a new workbook in a seperate folder.

What I want is for it to save sheet 3 as a .pdf file instead of .xls

The code I have right now to save sheet 3 is as follows.

Sheets("Sheet3").Copy
Range("B13").Select
Sheets("Sheet3").SaveAs Filename:="blaahblaahfolder" & _
Range("B15").Value & ".xls"
ActiveWorkbook.Close

I was thinking easy so I changed it to

Sheets("Sheet3").Copy
Range("B13").Select
Sheets("Sheet3").SaveAs Filename:="blaahblaahfolder" & _
Range("B15").Value & ".pdf"
ActiveWorkbook.Close

I run the macro and it does run cleanly but when I go into the
"blaahblaahfolder" to open up the file I get an error message and am unable
to open the file.

Any help would be most appreciated.

Thank you!

[Jim Rech]

Excel does not have the ability to save PDF files. It has been announced
for Excel 12, so you're ahead of the curve.

If you have the PDFWriter installed your procedure should be to "print" a
PDF file. From the Print dialog pick the PDF driver.

--
Jim
"ryanmhess" <[email protected]> wrote in message
news:[email protected]...
| Currently I have a macro that selects sheet 3 of my workbook, copies it,
and
| saves it as a new workbook in a seperate folder.
|
| What I want is for it to save sheet 3 as a .pdf file instead of .xls
|
| The code I have right now to save sheet 3 is as follows.
|
| Sheets("Sheet3").Copy
| Range("B13").Select
| Sheets("Sheet3").SaveAs Filename:="blaahblaahfolder" & _
| Range("B15").Value & ".xls"
| ActiveWorkbook.Close
|
| I was thinking easy so I changed it to
|
| Sheets("Sheet3").Copy
| Range("B13").Select
| Sheets("Sheet3").SaveAs Filename:="blaahblaahfolder" & _
| Range("B15").Value & ".pdf"
| ActiveWorkbook.Close
|
| I run the macro and it does run cleanly but when I go into the
| "blaahblaahfolder" to open up the file I get an error message and am
unable
| to open the file.
|
| Any help would be most appreciated.
|
| Thank you!
|
|

[Mats Samson]

Hi Ryan,
I found this discussion between RC and Darcy from July last year in Excel
General Questions with the header “Macro printing to a file” and I managed to
solve the PDF saving problem. The original suggestion was to save numerous
files, I’m saving only one at a time. I’m running Excel 2003 and Acrobat 6.0.
The trick to get rid of the saving prompt was to change the printer
properties in the Control Panel – Printers and Faxes – Adobe PDF – Properties
– Printing defaults.
You cannot set these properties permanently within the properties in Excel,
as soon you have reset to your Standard Printer the options are selected
again.
Clear the selections for “Prompt for Adobe PDF filename” AND “Do not send
fonts to “Adobe PDF””. Both are important, the latter for the driver to make
the Postscript files that’ll be deleted after conversion to PDF-files.
Here’s my code:

'**** PDF Creator
Public Sub PrintPDF(DCSel, IXSel)
Dim pdfDist As New ACRODISTXLib.PdfDistiller
Dim pdfPrinter, pdfName
Set fs = CreateObject("Scripting.FileSystemObject")
StdPrinter = Application.ActivePrinter
pdfUser = "pdf" & Application.UserName
pdfPrinter = Range(pdfUser).Value
pdfFilePath = "P:\"
pdfName = Range(DCSel) & " - " & Range(IXSel) & " - " _
& Range("PN1") & " - " & Format(Range("DocDate"), "YYMMDD")
psFileName = pdfFilePath & "\" & pdfName & ".PS"
pdfFileName = Left(psFileName, Len(psFileName) - 2) & "pdf"
Sheets("PrintDoc").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, preview:=False,
ActivePrinter:=pdfPrinter, _
printtofile:=True, collate:=True, PrToFileName:=psFileName
pdfDist.FileToPDF psFileName, pdfFileName, ""
Set pdfDist = Nothing
fs.DeleteFile pdfFilePath & "\*.PS"
fs.DeleteFile pdfFilePath & "\*.LOG"
Application.ActivePrinter = StdPrinter
End Sub
'*** End PDF Creator

A couple of notes:
As my colleagues are using the same program but does not necessarily have
the same Adobe driver on the same port, (mine is “Adobe PDF on NE06:”)
I have put the various users printer names in the workbook and the program
looks the appropriate user up and select his/her printer.
Furthermore I created our special naming structure of the document set by
the programs previous selections/conditions. Please note that the pdfName
shall not have any “.pdf” extension.
Good Luck!


"Jim Rech" wrote:

> Excel does not have the ability to save PDF files. It has been announced
> for Excel 12, so you're ahead of the curve.
>
> If you have the PDFWriter installed your procedure should be to "print" a
> PDF file. From the Print dialog pick the PDF driver.
>
> --
> Jim
> "ryanmhess" <[email protected]> wrote in message
> news:[email protected]...
> | Currently I have a macro that selects sheet 3 of my workbook, copies it,
> and
> | saves it as a new workbook in a seperate folder.
> |
> | What I want is for it to save sheet 3 as a .pdf file instead of .xls
> |
> | The code I have right now to save sheet 3 is as follows.
> |
> | Sheets("Sheet3").Copy
> | Range("B13").Select
> | Sheets("Sheet3").SaveAs Filename:="blaahblaahfolder" & _
> | Range("B15").Value & ".xls"
> | ActiveWorkbook.Close
> |
> | I was thinking easy so I changed it to
> |
> | Sheets("Sheet3").Copy
> | Range("B13").Select
> | Sheets("Sheet3").SaveAs Filename:="blaahblaahfolder" & _
> | Range("B15").Value & ".pdf"
> | ActiveWorkbook.Close
> |
> | I run the macro and it does run cleanly but when I go into the
> | "blaahblaahfolder" to open up the file I get an error message and am
> unable
> | to open the file.
> |
> | Any help would be most appreciated.
> |
> | Thank you!
> |
> |
>
>
>

[Cindy]

Mats,

This post was a life saver for me. Now I have one more trick I want to try.

I'm calling my printpdf sub and I want to send it three things: The name of
the PostScript file to create, the name of the PDF file to create, and the
name of the Worksheet I want to use.

I can't seem to figure out how to set the worksheet. Won't the
ActiveWindow.Selected.Sheets.Printout just pick the active sheet? (Pardin my
ignorance...I'm new at this.)

I don't see any parameters available to set. I'm sure I'm just missing the
obvious here.

Again, thanks for the post.

Regards,

Cindy W.

"Mats Samson" wrote:

> Hi Ryan,
> I found this discussion between RC and Darcy from July last year in Excel
> General Questions with the header “Macro printing to a file” and I managed to
> solve the PDF saving problem. The original suggestion was to save numerous
> files, I’m saving only one at a time. I’m running Excel 2003 and Acrobat 6.0.
> The trick to get rid of the saving prompt was to change the printer
> properties in the Control Panel – Printers and Faxes – Adobe PDF – Properties
> – Printing defaults.
> You cannot set these properties permanently within the properties in Excel,
> as soon you have reset to your Standard Printer the options are selected
> again.
> Clear the selections for “Prompt for Adobe PDF filename” AND “Do not send
> fonts to “Adobe PDF””. Both are important, the latter for the driver to make
> the Postscript files that’ll be deleted after conversion to PDF-files.
> Here’s my code:
>
> '**** PDF Creator
> Public Sub PrintPDF(DCSel, IXSel)
> Dim pdfDist As New ACRODISTXLib.PdfDistiller
> Dim pdfPrinter, pdfName
> Set fs = CreateObject("Scripting.FileSystemObject")
> StdPrinter = Application.ActivePrinter
> pdfUser = "pdf" & Application.UserName
> pdfPrinter = Range(pdfUser).Value
> pdfFilePath = "P:\"
> pdfName = Range(DCSel) & " - " & Range(IXSel) & " - " _
> & Range("PN1") & " - " & Format(Range("DocDate"), "YYMMDD")
> psFileName = pdfFilePath & "\" & pdfName & ".PS"
> pdfFileName = Left(psFileName, Len(psFileName) - 2) & "pdf"
> Sheets("PrintDoc").Select
> ActiveWindow.SelectedSheets.PrintOut Copies:=1, preview:=False,
> ActivePrinter:=pdfPrinter, _
> printtofile:=True, collate:=True, PrToFileName:=psFileName
> pdfDist.FileToPDF psFileName, pdfFileName, ""
> Set pdfDist = Nothing
> fs.DeleteFile pdfFilePath & "\*.PS"
> fs.DeleteFile pdfFilePath & "\*.LOG"
> Application.ActivePrinter = StdPrinter
> End Sub
> '*** End PDF Creator
>
> A couple of notes:
> As my colleagues are using the same program but does not necessarily have
> the same Adobe driver on the same port, (mine is “Adobe PDF on NE06:”)
> I have put the various users printer names in the workbook and the program
> looks the appropriate user up and select his/her printer.
> Furthermore I created our special naming structure of the document set by
> the programs previous selections/conditions. Please note that the pdfName
> shall not have any “.pdf” extension.
> Good Luck!
>
>
> "Jim Rech" wrote:
>
> > Excel does not have the ability to save PDF files. It has been announced
> > for Excel 12, so you're ahead of the curve.
> >
> > If you have the PDFWriter installed your procedure should be to "print" a
> > PDF file. From the Print dialog pick the PDF driver.
> >
> > --
> > Jim
> > "ryanmhess" <[email protected]> wrote in message
> > news:[email protected]...
> > | Currently I have a macro that selects sheet 3 of my workbook, copies it,
> > and
> > | saves it as a new workbook in a seperate folder.
> > |
> > | What I want is for it to save sheet 3 as a .pdf file instead of .xls
> > |
> > | The code I have right now to save sheet 3 is as follows.
> > |
> > | Sheets("Sheet3").Copy
> > | Range("B13").Select
> > | Sheets("Sheet3").SaveAs Filename:="blaahblaahfolder" & _
> > | Range("B15").Value & ".xls"
> > | ActiveWorkbook.Close
> > |
> > | I was thinking easy so I changed it to
> > |
> > | Sheets("Sheet3").Copy
> > | Range("B13").Select
> > | Sheets("Sheet3").SaveAs Filename:="blaahblaahfolder" & _
> > | Range("B15").Value & ".pdf"
> > | ActiveWorkbook.Close
> > |
> > | I run the macro and it does run cleanly but when I go into the
> > | "blaahblaahfolder" to open up the file I get an error message and am
> > unable
> > | to open the file.
> > |
> > | Any help would be most appreciated.
> > |
> > | Thank you!
> > |
> > |
> >
> >
> >

[Mats Samson]

Hello Cindy,
you’re right, but my project has a main procedure calling several
sub-procedures prior to the PrintPDF; selecting the right document, printing
on paper and setting original and number of copies. The proper sheet is
already selected and the printing to the PDFPrinter is the last procedure.
The sheet is selected via: Worksheets("PrintDoc").Select

If I get you right your have CALLED the PrintPDF from another procedure,
where you already decided about the name of the files. You can simply call
the PrintPDF together with these parameters as follows:

Sub Print ()
Sheets(“PrintDoc”).Select
psFileName= “MyPSFile”
pdfFileName = “MyPDFFile”
Call PrintPDF (psFileName, pdfFileName)
End Sub

Sub PrintPDF(psFileName, pdfFileName)
......do the PDF stuff
Worksheets(“System”).Activate
End Sub

I’ve added a new last parameter so the originating worksheet always is
activated again. I hope this will work for you, otherwise turn back!
Regards
Mats


"Cindy" wrote:

> Mats,
>
> This post was a life saver for me. Now I have one more trick I want to try.
>
> I'm calling my printpdf sub and I want to send it three things: The name of
> the PostScript file to create, the name of the PDF file to create, and the
> name of the Worksheet I want to use.
>
> I can't seem to figure out how to set the worksheet. Won't the
> ActiveWindow.Selected.Sheets.Printout just pick the active sheet? (Pardin my
> ignorance...I'm new at this.)
>
> I don't see any parameters available to set. I'm sure I'm just missing the
> obvious here.
>
> Again, thanks for the post.
>
> Regards,
>
> Cindy W.
>
> "Mats Samson" wrote:
>
> > Hi Ryan,
> > I found this discussion between RC and Darcy from July last year in Excel
> > General Questions with the header “Macro printing to a file” and I managed to
> > solve the PDF saving problem. The original suggestion was to save numerous
> > files, I’m saving only one at a time. I’m running Excel 2003 and Acrobat 6.0.
> > The trick to get rid of the saving prompt was to change the printer
> > properties in the Control Panel – Printers and Faxes – Adobe PDF – Properties
> > – Printing defaults.
> > You cannot set these properties permanently within the properties in Excel,
> > as soon you have reset to your Standard Printer the options are selected
> > again.
> > Clear the selections for “Prompt for Adobe PDF filename” AND “Do not send
> > fonts to “Adobe PDF””. Both are important, the latter for the driver to make
> > the Postscript files that’ll be deleted after conversion to PDF-files.
> > Here’s my code:
> >
> > '**** PDF Creator
> > Public Sub PrintPDF(DCSel, IXSel)
> > Dim pdfDist As New ACRODISTXLib.PdfDistiller
> > Dim pdfPrinter, pdfName
> > Set fs = CreateObject("Scripting.FileSystemObject")
> > StdPrinter = Application.ActivePrinter
> > pdfUser = "pdf" & Application.UserName
> > pdfPrinter = Range(pdfUser).Value
> > pdfFilePath = "P:\"
> > pdfName = Range(DCSel) & " - " & Range(IXSel) & " - " _
> > & Range("PN1") & " - " & Format(Range("DocDate"), "YYMMDD")
> > psFileName = pdfFilePath & "\" & pdfName & ".PS"
> > pdfFileName = Left(psFileName, Len(psFileName) - 2) & "pdf"
> > Sheets("PrintDoc").Select
> > ActiveWindow.SelectedSheets.PrintOut Copies:=1, preview:=False,
> > ActivePrinter:=pdfPrinter, _
> > printtofile:=True, collate:=True, PrToFileName:=psFileName
> > pdfDist.FileToPDF psFileName, pdfFileName, ""
> > Set pdfDist = Nothing
> > fs.DeleteFile pdfFilePath & "\*.PS"
> > fs.DeleteFile pdfFilePath & "\*.LOG"
> > Application.ActivePrinter = StdPrinter
> > End Sub
> > '*** End PDF Creator
> >
> > A couple of notes:
> > As my colleagues are using the same program but does not necessarily have
> > the same Adobe driver on the same port, (mine is “Adobe PDF on NE06:”)
> > I have put the various users printer names in the workbook and the program
> > looks the appropriate user up and select his/her printer.
> > Furthermore I created our special naming structure of the document set by
> > the programs previous selections/conditions. Please note that the pdfName
> > shall not have any “.pdf” extension.
> > Good Luck!
> >
> >
> > "Jim Rech" wrote:
> >
> > > Excel does not have the ability to save PDF files. It has been announced
> > > for Excel 12, so you're ahead of the curve.
> > >
> > > If you have the PDFWriter installed your procedure should be to "print" a
> > > PDF file. From the Print dialog pick the PDF driver.
> > >
> > > --
> > > Jim
> > > "ryanmhess" <[email protected]> wrote in message
> > > news:[email protected]...
> > > | Currently I have a macro that selects sheet 3 of my workbook, copies it,
> > > and
> > > | saves it as a new workbook in a seperate folder.
> > > |
> > > | What I want is for it to save sheet 3 as a .pdf file instead of .xls
> > > |
> > > | The code I have right now to save sheet 3 is as follows.
> > > |
> > > | Sheets("Sheet3").Copy
> > > | Range("B13").Select
> > > | Sheets("Sheet3").SaveAs Filename:="blaahblaahfolder" & _
> > > | Range("B15").Value & ".xls"
> > > | ActiveWorkbook.Close
> > > |
> > > | I was thinking easy so I changed it to
> > > |
> > > | Sheets("Sheet3").Copy
> > > | Range("B13").Select
> > > | Sheets("Sheet3").SaveAs Filename:="blaahblaahfolder" & _
> > > | Range("B15").Value & ".pdf"
> > > | ActiveWorkbook.Close
> > > |
> > > | I run the macro and it does run cleanly but when I go into the
> > > | "blaahblaahfolder" to open up the file I get an error message and am
> > > unable
> > > | to open the file.
> > > |
> > > | Any help would be most appreciated.
> > > |
> > > | Thank you!
> > > |
> > > |
> > >
> > >
> > >

[Cindy]

Mats,

Thank you! Thank You! Thank You!!!

It worked like a charm! I like the idea of returning the user to the
original activated sheet.

Again, you saved me hours of time! I really appreciate it!

Cindy W.

"Mats Samson" wrote:

> Hello Cindy,
> you’re right, but my project has a main procedure calling several
> sub-procedures prior to the PrintPDF; selecting the right document, printing
> on paper and setting original and number of copies. The proper sheet is
> already selected and the printing to the PDFPrinter is the last procedure.
> The sheet is selected via: Worksheets("PrintDoc").Select
>
> If I get you right your have CALLED the PrintPDF from another procedure,
> where you already decided about the name of the files. You can simply call
> the PrintPDF together with these parameters as follows:
>
> Sub Print ()
> Sheets(“PrintDoc”).Select
> psFileName= “MyPSFile”
> pdfFileName = “MyPDFFile”
> Call PrintPDF (psFileName, pdfFileName)
> End Sub
>
> Sub PrintPDF(psFileName, pdfFileName)
> .....do the PDF stuff
> Worksheets(“System”).Activate
> End Sub
>
> I’ve added a new last parameter so the originating worksheet always is
> activated again. I hope this will work for you, otherwise turn back!
> Regards
> Mats
>
>
> "Cindy" wrote:
>
> > Mats,
> >
> > This post was a life saver for me. Now I have one more trick I want to try.
> >
> > I'm calling my printpdf sub and I want to send it three things: The name of
> > the PostScript file to create, the name of the PDF file to create, and the
> > name of the Worksheet I want to use.
> >
> > I can't seem to figure out how to set the worksheet. Won't the
> > ActiveWindow.Selected.Sheets.Printout just pick the active sheet? (Pardin my
> > ignorance...I'm new at this.)
> >
> > I don't see any parameters available to set. I'm sure I'm just missing the
> > obvious here.
> >
> > Again, thanks for the post.
> >
> > Regards,
> >
> > Cindy W.
> >
> > "Mats Samson" wrote:
> >
> > > Hi Ryan,
> > > I found this discussion between RC and Darcy from July last year in Excel
> > > General Questions with the header “Macro printing to a file” and I managed to
> > > solve the PDF saving problem. The original suggestion was to save numerous
> > > files, I’m saving only one at a time. I’m running Excel 2003 and Acrobat 6.0.
> > > The trick to get rid of the saving prompt was to change the printer
> > > properties in the Control Panel – Printers and Faxes – Adobe PDF – Properties
> > > – Printing defaults.
> > > You cannot set these properties permanently within the properties in Excel,
> > > as soon you have reset to your Standard Printer the options are selected
> > > again.
> > > Clear the selections for “Prompt for Adobe PDF filename” AND “Do not send
> > > fonts to “Adobe PDF””. Both are important, the latter for the driver to make
> > > the Postscript files that’ll be deleted after conversion to PDF-files.
> > > Here’s my code:
> > >
> > > '**** PDF Creator
> > > Public Sub PrintPDF(DCSel, IXSel)
> > > Dim pdfDist As New ACRODISTXLib.PdfDistiller
> > > Dim pdfPrinter, pdfName
> > > Set fs = CreateObject("Scripting.FileSystemObject")
> > > StdPrinter = Application.ActivePrinter
> > > pdfUser = "pdf" & Application.UserName
> > > pdfPrinter = Range(pdfUser).Value
> > > pdfFilePath = "P:\"
> > > pdfName = Range(DCSel) & " - " & Range(IXSel) & " - " _
> > > & Range("PN1") & " - " & Format(Range("DocDate"), "YYMMDD")
> > > psFileName = pdfFilePath & "\" & pdfName & ".PS"
> > > pdfFileName = Left(psFileName, Len(psFileName) - 2) & "pdf"
> > > Sheets("PrintDoc").Select
> > > ActiveWindow.SelectedSheets.PrintOut Copies:=1, preview:=False,
> > > ActivePrinter:=pdfPrinter, _
> > > printtofile:=True, collate:=True, PrToFileName:=psFileName
> > > pdfDist.FileToPDF psFileName, pdfFileName, ""
> > > Set pdfDist = Nothing
> > > fs.DeleteFile pdfFilePath & "\*.PS"
> > > fs.DeleteFile pdfFilePath & "\*.LOG"
> > > Application.ActivePrinter = StdPrinter
> > > End Sub
> > > '*** End PDF Creator
> > >
> > > A couple of notes:
> > > As my colleagues are using the same program but does not necessarily have
> > > the same Adobe driver on the same port, (mine is “Adobe PDF on NE06:”)
> > > I have put the various users printer names in the workbook and the program
> > > looks the appropriate user up and select his/her printer.
> > > Furthermore I created our special naming structure of the document set by
> > > the programs previous selections/conditions. Please note that the pdfName
> > > shall not have any “.pdf” extension.
> > > Good Luck!
> > >
> > >
> > > "Jim Rech" wrote:
> > >
> > > > Excel does not have the ability to save PDF files. It has been announced
> > > > for Excel 12, so you're ahead of the curve.
> > > >
> > > > If you have the PDFWriter installed your procedure should be to "print" a
> > > > PDF file. From the Print dialog pick the PDF driver.
> > > >
> > > > --
> > > > Jim
> > > > "ryanmhess" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > | Currently I have a macro that selects sheet 3 of my workbook, copies it,
> > > > and
> > > > | saves it as a new workbook in a seperate folder.
> > > > |
> > > > | What I want is for it to save sheet 3 as a .pdf file instead of .xls
> > > > |
> > > > | The code I have right now to save sheet 3 is as follows.
> > > > |
> > > > | Sheets("Sheet3").Copy
> > > > | Range("B13").Select
> > > > | Sheets("Sheet3").SaveAs Filename:="blaahblaahfolder" & _
> > > > | Range("B15").Value & ".xls"
> > > > | ActiveWorkbook.Close
> > > > |
> > > > | I was thinking easy so I changed it to
> > > > |
> > > > | Sheets("Sheet3").Copy
> > > > | Range("B13").Select
> > > > | Sheets("Sheet3").SaveAs Filename:="blaahblaahfolder" & _
> > > > | Range("B15").Value & ".pdf"
> > > > | ActiveWorkbook.Close
> > > > |
> > > > | I run the macro and it does run cleanly but when I go into the
> > > > | "blaahblaahfolder" to open up the file I get an error message and am
> > > > unable
> > > > | to open the file.
> > > > |
> > > > | Any help would be most appreciated.
> > > > |
> > > > | Thank you!
> > > > |
> > > > |
> > > >
> > > >
> > > >