Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Mar 2001
    Location
    St Georges du Bois, Pays de la Loire
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

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

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

    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.

  3. #3
    Star Lounger
    Join Date
    Mar 2001
    Location
    St Georges du Bois, Pays de la Loire
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  4. #4
    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: Macro: print each sheet to pdf using sheet name as (Excel 97 sr-2 Win9

    put it in the "personal.xls" file in XLStart

    Steve

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

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

    To have a macro available all the time, you can put it in Personal.xls. See the <!post=Personal.xls Tutorial, 118382>Personal.xls Tutorial<!/post> by Legare Coleman.

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

  6. #6
    Star Lounger
    Join Date
    Mar 2001
    Location
    St Georges du Bois, Pays de la Loire
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

Posting Permissions

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