The UK's Number 1 for Microsoft Office Training

Sitemap

Bookmark page

Printable version

Course updates
due to adverse
weather conditions

Customer: Sign in
Delegate: Sign in
Trainer: Log in

Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Pdf seprate works sheets & add to an email

	98.3 % recommend us
	More live stats...

Bookmark this page

Share this with a friend or colleague:

The UK's most regular
instructor-led training courses.

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions

Pdf seprate works sheets & add to an email

Resolved · High Priority · Version 2003

ReplyFri 5 Mar 2010, 16:46Delegate Daren said...

Pdf seprate works sheets & add to an email

Hi wonder if you can help

I have a workbook that consist of five worksheets.

I am trying to convert them in to seperate Pdf document's and attached them to an email automatically, or at least to a folder.

I cannot find a save option within excel that save's documents with a Pdf extention.

I have been able to get a sheet to print to a Pdf file but it then prompts you for a file name, I would like it to be all done at the operation of a button

Can you help

Thank you

Daren

For upcoming training course dates see: Pricing & availability

ReplyMon 8 Mar 2010, 13:41Trainer Anthony said...

RE: Pdf seprate works sheets & add to an email

Hi Daren, thanks for your query. I don't have any PDF drivers on my machine at the moment so I will try to come up with some code over night if that's OK. Attaching PDF's to an email will involve access the Outlook Object model and not something easily explained here, but what I can do is give you some code to export the worksheets as separate files and possibly PDF them at the same time. If automatic PDF'ing proves difficult in code, you can at least then run a batch PDF on the folder.

Bear with me until tomorrow and check the forum then.

All the best,

Anthony

ReplyMon 8 Mar 2010, 18:56Trainer Anthony said...

RE: Pdf seprate works sheets & add to an email

Hi Daren. I've had some success this end. Paste the following subroutine into a module attached to your workbook. Note the two comments at the start - you need to make sure Tools-References-Adobe Distiller is ticked and a second option is unticked in Adobe PDF properties.

Drop me a line back and let me know you've got it working. Here's the code:

Sub pdfme()

'need to check Reference to Acrobat Distiller in Tools --> References
'select file - print - adobe pdf - properties and untick "Do not send fonts to Adobe PDF"

Dim shtSheet As Worksheet
Dim myworksheetname As String
Dim PSFileName As String
Dim PDFFileName As String
Dim myPDF As PdfDistiller

Application.DisplayAlerts = False

Set myPDF = New PdfDistiller

For Each shtSheet In ActiveWorkbook.Worksheets

shtSheet.Select

myworksheetname = ActiveSheet.Name

PSFileName = "c:\mypdfs\" & myworksheetname & ".ps"
PDFFileName = "c:\mypdfs\" & myworksheetname & ".pdf"

'Print the Excel range to the postscript file

ActiveWindow.SelectedSheets.PrintOut copies:=1, preview:=False, ActivePrinter:="Adobe PDF", _
printtofile:=True, collate:=True, prtofilename:=PSFileName

'Convert the postscript file to .pdf
DoEvents
myPDF.FileToPDF PSFileName, PDFFileName, ""
DoEvents

Next shtSheet

Application.DisplayAlerts = True

End Sub

Hope this helps,

Anthony

ReplyFri 26 Mar 2010, 11:00Delegate Daren said...

RE: Pdf seprate works sheets & add to an email

Hi Anthony

Sorry I have taken so long to get back to you.

I have tried the programming you have sent, it works in thatit goes through all the works sheets and sends the to the PDF printer.

It produces an arror, and put them in a print que instead of a folder.

I have now found out that most of my team only have "Cute PDF" on there PC', this is abasic version that only allow them to create a PDF document

I have created VB that creats a PDF Documents but it brings up a saveas box I would like it to pull the file name from the works sheet and save it automatically. i.e sheet1, sheet2 etc

The Printer output being called is "CutePDF Writer on CPW2"

Below is the VB programing I have created can you help

-------------------------------------------------------

Sub CreatePDF()

On Error GoTo bypass

' Creates folder on PC and saves to save the open workook

MkDir "C:\Client Reports\PDF_Test_Folder"

bypass:

ActiveWorkbook.SaveAs FileName:= _
"C:\Client Reports\PDF_Test_Folder\Book1Test PDF.xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

' This makes the CutePDF output as the active printer

Application.ActivePrinter = "CutePDF Writer on CPW2:"

Sheets("Sheet1").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("Sheet2").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("Sheet3").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

End Sub

Mon 15 Mar 2010: Automatically marked as resolved.

Excel articles

· The Mysteries Of Excel Explained
· Excel: Save Time With AutoFill
· Inviting Investment Into Your Business Can Be Easier With Excel
· Preparing to Pivot: Why all Excel users should know about Pivot Tables
· Never Lose It In Translation With Excel

Please browse our web site to find out more about
microsoft excel course and other Microsoft training courses.

Excel tip:

Calculate age or service

The DATEDIF() function in Excel calculates the number of days, months, or years between two dates. So, this function makes it easy to calculate a person's age. To try this tip:

In a blank worksheet, type the birth date in cell A1, using slashes to separate day, month, and year.
In cell A2, type =DATEDIF(A1,TODAY(),"y") and press ENTER.

View all Excel hints and tips

Hi,

I need to change the way some date data is shown in access. It has been provided as a text field.

I currently have date ranges shown as yyyymm. I need to see it as 01/mm/dddd (currently have no "dd", therefore need to add a 01 at the..