printing to pdf (XP Office2003)

Topic

New Reply

I don’t know if this is possible to do or not, so I hope somebody will be able to help.

I have a spreadsheet with a simple macro created in it that basically goes to a worksheet which contains a pivot table; selects a company number, and prints another sheet to my local desktop printer. Running the macro will automatically print 60 different companies – my question: can I have the same macro print to a pdf file? Currently if I print to pdf, it stops to ask you to name the file, after you’ve done that, it continues the pdf creation. I don’t mind saving each company if necessary in the initial set-up of the macro as I can easily merge all the separate files afterwards. I did try to create a new macro do this as a test and I got a runtime error. If I change my printer to pdf and try to run the original macro, I get also get a runtime error and that “Autosort must be turned off to move items in a field”.

Is this at all possible to do or does any one have any suggestions? My final project includes many pages from several different files and file types. Being able to place them all in a single pdf would greatly decrease the amount of time that it currently takes printing and collating everything from several different locations , not to mention several other benefits it offers.

Thanks,
Goodtogo

Reply | Quote

Replies

Are you using Adobe Acrobat to create PDF files, or another application, and if the latter, which one?

Reply | Quote

Yes I am using Adobe Acrobat Professional 7.0 to create the PDF.

Below is part of the print macro that has been used. The only thing that changes is the number in quotes (ie. “1001”). I am not terribly versed in VBA so I hope this can help.

Sub PrintForms()
‘
‘ PrintForms Macro
‘ Macro recorded 11/19/98 by
‘
Sheets(“Pivot Table”).Select
Range(“A5”).Select
ActiveCell.FormulaR1C1 = “1001”
Sheets(“Page 1″).Select
Application.Goto Reference:=”Form”
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Application.Goto Reference:=”Form2″
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

Sheets(“Pivot Table”).Select
Range(“A5”).Select
ActiveCell.FormulaR1C1 = “1002”
Sheets(“Page 1″).Select
Application.Goto Reference:=”Form”
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Application.Goto Reference:=”Form2″
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

Sheets(“Pivot Table”).Select
Range(“A5”).Select
ActiveCell.FormulaR1C1 = “1003”
Sheets(“Page 1″).Select
Application.Goto Reference:=”Form”
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Application.Goto Reference:=”Form2″
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

Sheets(“Pivot Table”).Select
Range(“A5”).Select
ActiveCell.FormulaR1C1 = “1004”
Sheets(“Page 1″).Select
Application.Goto Reference:=”Form”
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Application.Goto Reference:=”Form2″
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

Sheets(“Pivot Table”).Select
Range(“A5”).Select
ActiveCell.FormulaR1C1 = “1005”
Sheets(“Page 1″).Select
Application.Goto Reference:=”Form”
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Application.Goto Reference:=”Form2″
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

Thanks.

Reply | Quote

You can probably use a loop to avoid all that repetition, and code from one of the posts Steve (sdckapr) referred to:

Dim lngNum As Long

For lngNum = 1001 To 1005
Sheets(“Pivot Table”).Range(“A5″) = lngNum
Application.Goto Reference:=”Form”
ActiveSheet.PrintOut Copies:=1, Collate:=True, ActivePrinter:=”Adobe PDF”, _
PrintToFile:=True, PrToFileName:=”Form_” & lngNum & “.xls”
Application.Goto Reference:=”Form2″
ActiveSheet.PrintOut Copies:=1, Collate:=True, ActivePrinter:=”Adobe PDF”, _
PrintToFile:=True, PrToFileName:=”Form2_” & lngNum & “.xls”
Next lngNum

Change the upper bound 1005 as needed, and change “Adobe PDF” to the name of the Adobe “printer” on your PC.

Reply | Quote

Do the threads listed in Re: .xls to .pdf (Excel 2000 (9.0.6926 SP-3)) Help at all?

Steve

Reply | Quote