Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    batch print to pdf (97+)

    I have a folder with a large number of .xls files. I want to convert all files (batch mode) in specific "xls" folder without opening them one by one to ".pdf" by means of Adobe distiller which is installed and recognised as a printer.
    Tia
    Smbs

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: batch print to pdf (97+)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> smbs

    OK think about VBA. This task I am sure has been done many times before, and if you are new to VBA, the Loungers can help.

    Basicaly it goes like this:

    1) Set the path to the folder where your workbooks are. Also set the active printer to be the PDF writer.
    2) Count how many files you have.
    3) Loop from 1 to the number of files you have in step 2. You may need some other method to distinguish what files are to be printed or skipped.
    4) Have MS-Excel open the workbook, go to the worksheet you want to print, print it and then close the workbook without saving it.

    If you are OK with VBA you can start the project and the Loungers will help you, if you are not OK with VBA, then tell me if the 4 steps need to be adjusted in any way, and I'll give you the code needed.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  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: batch print to pdf (97+)

    Just some added comments to your "scheme":

    For your steps 1-3 you could either do an entire folder (that the user selects at run time) or use the multiselect in GetOpenFileName method to choose the files that you want to work on at runtime, or even have list in a particular range already "chosen" to work on.

    Additional steps might be to loop through each sheet to print them or to select all the sheets and print as one (depending on how you want the pages numbered if they are numbered in excel.)

    Printing one sheet at a time will have each sheet start at page 1.
    Printing all the sheets at once will have the first sheet start at page 1 and the rest will just continue.

    You could also have code set up to print only particular sheets if desired or even only particular ranges.

    Steve

  4. #4
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: batch print to pdf (97+)

    Its me Again <img src=/S/hello.gif border=0 alt=hello width=25 height=29>

    I have give you a sample example of what can be done. Not having your configuration, I had to makde due with what I have.

    So...

    After downloading and saving the attached .BAS file, remove the .txt extension. I had to use this so that the Lounge will accept it.

    1) Start MS-Excel.
    2) Press ALT+F11 to activate the Visual Basic Editor.
    3) From the File Menu choose "Import File... CTRL+M"
    4) Navigate the where you saved the .BAS file attached, and Import it.
    5) Read all the code and the Green comments and change what applies to you.
    6) Save the workbook, and try and run the code.

    If you have any diffculties please let me know. This code is sent as is without any warrenty yadi yadi yadi you know what I am talking about.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  5. #5
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: batch print to pdf (97+)

    Thanx Wassim and Steve for your quick replies-- will be trying code out immediately-but just 1 question --In order to print an excel file does it have to be physically opened and displayed ---is there no way to displaying to /dev/null (unix) for instance or something in the background without actually seeing file contents ??
    Thanx
    Smbs

  6. #6
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: batch print to pdf (97+)

    Wassim --- your code works but........
    I have to input each pdf file name manually--doing a "Google search" have found the following:-
    ActivePrinter:="Acrobat Distiller", printtofile:=True, collate:=True, prttofilename:=FileName
    Wassim using your code I am not sure how to incorporate above code into your code so that the converted pdf would have original xls filename after conversion I have tried a few possibilties but none seem to work
    Tia
    Smbs

  7. #7
    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: batch print to pdf (97+)

    Using your line of code you could create the filename to be the current name replacing the .xls with .pdf

    filename = activeworkbook.name
    filename = left(filename, len(filename)-4)&".pdf"
    ActivePrinter:="Acrobat Distiller", printtofile:=True, collate:=True, prttofilename:=FileName

    Steve

  8. #8
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: batch print to pdf (97+)

    thanx Steve
    Smbs

  9. #9
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: batch print to pdf (97+)

    Ok smbs

    I guess you got your answer(s)...

    I am not sure if you can print a worksheet without having the workbook open. If this is not what you want, then I guess we have to rework my bit of code.

    But as you see the screen updating is turned off, so that the opening and closing of the workbooks will not slow down the process, since each open/close need to be drawn on the screen and this slows down the printing.

    Let me know if you need any help.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  10. #10
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: batch print to pdf (97+)

    Thanx Wassim
    Still have a few problems but trying to sort them out by myself!
    Will let u know if I need help
    Thanx again
    Smbs

  11. #11
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    San Jose, California, USA
    Posts
    290
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: batch print to pdf (97+)

    Smbs

    This is a problem I've been trying to solve, too. Wassim's code was helpful but I had the same problem as you with having to name each pdf file. Can you post the code you are now using? Thanks

  12. #12
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: batch print to pdf (97+)

    Andrew!
    I am out of office for about 10 days will reply when I have a chance
    Smbs

Posting Permissions

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