Automating .pdf creating from excel

[royaldank]

Everyday, someone in our office has to open a 16 page excel document. There are 16 tabs at the bottom. They have to go tab by tab and save each page. Filenames are consistant (ie. rates1.pdf, rates2.pdf, rates3.pdf, etc...).

Can anyone offer a suggestion or point me in the right direction? I'm looking to make her life easier and somehow automate this process.

 

[TSDible]

You can do this pretty easily I think. I'm assuming that you are using some type of PDF printer to do the job. Couple of questions before I get too far...

1. Which PDF printer are you using?

2. Can you automate the PDF printer that you are using, or do you have to manually enter the name of the file to save each and every time?

3. Is the PDF printer that you are using used for anything else?


I would consider creating an add-in that would do this automatically. Even a macro in the spreadsheet would get the job done, but if it something that you do every day, an add-in may be best.

 

[royaldank]

Not exactly sure what an add-in means exactly.

Basically, we have one computer that spits out our rate sheets. We end up getting a 16 tabbed excel sheet each morning. We save each page to .pdf by selecting the tabs one at a time and then hitting "Acrobat->Convert to .PDF" from the top toolbar. I guess this is using acrobat distiller as the printer.

Is it possible to just record the steps once and then run that each time? I guess that would fall under a macro? Start recording the macro, do the steps, and then end recording. I suppose that might work?

I mainly do web development so I'm not all that familar with excel or acrobat. But, since I'm the only guy here today, it my duty to figure out how to automate this if possible.

Also, thanks for helping out.

 

[TSDible]

I'm not sure about doing it with distiller.

I used a PDF printer driver and did it that way. It took me about 5 minutes to write the following macro. I'm sorry if it gets messed up due to formatting.

Basically, it does exactly what you want. I had to do the following to make it work.

1. Set up my FinePrint printer to print automatically to a folder without prompting.
2. Find out the names of my PDF Printer and my Default Printer (I used the macro recorder for this)

This can easily be turned into a button on the sheet to run the macro, or it could be saved as an add-in. I would prefer the latter if it were me.

Now, when I run the "PrintReport" routine, it nicely prints each tab to its own pdf with the following names

NameOfWorkbook_1.pdf
NameOfWorkbook_2.pdf

etc.

See if you have an adobe printer driver already installed and if that can be used. If not, you could even consider a free PDF printer like PDFCreator to use solely for this purpose. I have PDF Creator at home, and I will see if it works with it tonight.

Also, you can try to just "Record" a macro and do it once. I'm not sure if it will work the Adobe Distiller though. (Tools --> Macro --> Record New Macro)


-------------------------------------
Sub PrintReport()
Dim ws As Worksheet

'turns off screen updating tabs don't change during print
Application.ScreenUpdating = False

'sets the printer to the PDF printer
Application.ActivePrinter = "FinePrint pdfFactory on FPP1:"

'cycles through each worksheet and prints each one
For Each ws In Worksheets
With Sheets(ws.Name)
.PrintOut Copies:=1, Collate:=True
End With
Next ws

'sets the printer back to your default printer
Application.ActivePrinter = "Replace with name of default printer"

'turns screen updating back on
Application.ScreenUpdating = True

End Sub
-----------------------------------------

 

[royaldank]

Again, thanks for the help. At least I have something to start with. Doesn't look all that difficult.

 

[TSDible]

YHPM

 

[TSDible]

Updated my code for a silly error... oops.