Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Sep 2005
    Location
    Cambridge, Ontario, Canada
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    printing to pdf (XP Office2003)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: printing to pdf (XP Office2003)

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

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: printing to pdf (XP Office2003)

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

    Steve

  4. #4
    New Lounger
    Join Date
    Sep 2005
    Location
    Cambridge, Ontario, Canada
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: printing to pdf (XP Office2003)

    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.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: printing to pdf (XP Office2003)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •