Q: Everyday, someone in our office is opening a 16 page Excel document. There are 16 tabs at the bottom. They have tab by tab and every page to save. File names in line (ie rates1.pdf, rates2.pdf, rates3.pdf, etc. .) Can anyone have a suggestion or 0.
me in the right direction? Im looking for her life easier and somehow automate this process.
Best Answer: The following is a guess based on looking at the Object Browser with the one pdf creating add in I am aware of but have never used.
With the workbook open, press Alt+F11 to view the VBAProject code
If the Project – VBAProject window contains an entry for
AcrobatPDFMaker(PDFMaker.xla) then
In your macro, before starting the process that opens the print dialog box, try setting a string (say sFile) to the full path and file name of the file you want to create and the enter
AcrobatPDFMaker. AutoExecNew. finalPDFFileName = sFile
(Remove the two spaces after the periods – they were inserted so you could see all of the code in Answers)
When the print dialog box opens, look at the values there to see if you need to adjust the value of sFile (e.g. the path may not be required.)
Good luck, as I said, this is just a guess and may not work at all. But I think it is worth a try while you are waiting to see if anyone else really knows and is willing to talk.
Re:Updated my code for a silly error… oops.
Re:Again, thanks for the help. At least I have something to start with. Doesn't look all that difficult.
Re: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
—————————————–
Re: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.
Re: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.
0 Comments.