Macro: print each sheet to pdf using sheet name as (Excel 97 sr-2 Win95)

Topic

New Reply

Has anybody a macro that will print each sheet in a workbook to a seperate file using the sheet name as the file name? I need to produce a pdf file for each sheet. I have full Acrobat and at present am printing each sheet by hand to PDFMaker.

Many thanks,

David

Reply | Quote

Replies

First, you must find out the exact name of the PDFMaker “printer”. In any workbook, select File/Print… and set the printer to PDFMaker. You don’t have to click OK, you can cancel the command.
Now, activate the Visual Basic Editor and open the Immediate window (Ctrl+G). Type
? Application.ActivePrinter
Write down or copy the exact name. It will probably be something like “PDFMaker on LPT0:” (without the quotes)

Create a macro:

Sub PrintSheetsToPDF()
Dim wbk As Workbook
Dim sht As Worksheet
Set wbk = ActiveWorkbook ‘ or any workbook
For Each sht In wbk.Worksheets
SendKeys “C:” & sht.Name & “{ENTER}”
sht.PrintOut PrintToFile:=True, ActivePrinter:=”PDFMaker on LPT0:”
Next sht
End Sub

Substitute the name you wrote down or copied for the ActivePrinter argument.

Note: SendKeys is used as a workaround because Microsoft states that you can’t supply the file name in VBA.

Reply | Quote

Many thanks Hans – worked a treat. I just have to fine-tune the SendKeys to get then in the right directory.

However I am going to be sent a large number of workbooks every month to convert – is there any way of tying the macro to Excel instead of a particular workbook – as in normal.dot in Word? I don’t want to have to paste the macro into every workbook.

David

Reply | Quote

put it in the “personal.xls” file in XLStart

Steve

Reply | Quote

To have a macro available all the time, you can put it in Personal.xls. See the Personal.xls Tutorial by Legare Coleman.

If you wish, you can assign the macro to a toolbar button.

Reply | Quote

Thanks to everybody for the help – I’ve just rattled through about 500 sheets producing 2-page pdf files in a couple of minutes.

David

Reply | Quote