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!

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" wrote in message
...
| 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!
|
|

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, Im saving only one at a time. Im 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 thatll be deleted after conversion to PDF-files.
Heres 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" wrote in message
...
| 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,

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, Im saving only one at a time. Im 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 thatll be deleted after conversion to PDF-files.
Heres 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" wrote in message
...
| 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!
|
|

Hello Cindy,
youre 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

Ive 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, Im saving only one at a time. Im 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 thatll be deleted after conversion to PDF-files.
Heres 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" wrote in message
...
| 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!
|
|