Page 1 of 8 123 ... LastLast
Results 1 to 10 of 75

ANSWER! How to create an excel to PDF macro with auto filename

This is a discussion on ANSWER! How to create an excel to PDF macro with auto filename within the Excel Questions forums, part of the Question Forums category; After much searching and many times of trial and error, I have finally found a macro that will quickly and ...

  1. #1
    New Member
    Join Date
    Jun 2008
    Location
    California
    Posts
    6

    Red face ANSWER! How to create an excel to PDF macro with auto filename

    After much searching and many times of trial and error, I have finally found a macro that will quickly and easily convert an excel worksheet to PDF and automatically do a save as with a filename based on content in a specified cell. Hope it works for you as well as it works for me!

    The following code has been tested on Excel 2003 and Acrobat 8.0 Pro:

    ' This line of code specifies your directory as well as the cell or range which you want the filename to come from. As you can see, I have a specific cell with the range name "InvNbr" so that the macro knows to pull the filename from there. If you don't want to use a range name, just replace InvNbr with your cell reference, such as C4.

    Filename = "C:\Folder1\SubFolder1\" & ActiveSheet.Range("InvNbr").Value & ".pdf"

    ' This line of code sends the filename characters and the ENTER key to the active application. The "False" statement allows the macro to continue running without waiting for the keys to be processed.

    SendKeys Filename & "{ENTER}", False

    ' This line of code calls the Adobe PDF printer and runs the conversion. To ensure that you replace this code correctly with your own PDF printer, simply record a macro to print to Adobe PDF and then copy and paste it here.

    ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
    "Adobe PDF on Ne02:", Collate:=True



    If you don't have Acrobat Pro and are using a free version of PDF conversion software, try the following (it has been tested on Excel 2003 and CutePDF):

    ' This line of code calls your PDF printer and runs the conversion. Record your own macro to call your PDF printer and copy and paste it here.

    ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
    "CutePDF Writer on CPW2:", Collate:=True

    ' This set of code tells the macro to pause for 2 seconds. This will allow for the PDF printer to run through its process and prompt you for a filename.

    newHour = Hour(Now())
    newMinute = Minute(Now())
    newSecond = Second(Now()) + 2
    waitTime = TimeSerial(newHour, newMinute, newSecond)
    Application.Wait waitTime

    ' This line of code specifies your directory as well as the cell or range which you want the filename to come from.

    Filename = "C:\Folder1\SubFolder1\" & ActiveSheet.Range("InvNbr").Value & ".pdf"

    ' This line of code sends the filename characters and the ENTER key to the active application (i.e. the prompt window). The "False" statement allows the macro to continue running without waiting for the keys to be processed.

    SendKeys Filename & "{ENTER}", False


    The beautiful thing about these macros is that you can specify your directory, so you can customize them to send your worksheet to whatever directory you want and save it under whatever filename you want. Awesome!

    I hope someone else finds this useful!!!

  2. #2
    Board Regular
    Join Date
    Jun 2008
    Posts
    57

    Default Re: ANSWER! How to create an excel to PDF macro with auto filename

    That is helpful, thanks for the tip!

  3. #3
    Board Regular
    Join Date
    Aug 2004
    Location
    Hamilton, ON
    Posts
    64

    Default Re: ANSWER! How to create an excel to PDF macro with auto filename

    Lifesaver.

    I'm going to implement it into one our forms for one of our more, um, let's say picky clients.

    CP
    n/a

  4. #4
    Board Regular
    Join Date
    Aug 2004
    Location
    Hamilton, ON
    Posts
    64

    Default Re: ANSWER! How to create an excel to PDF macro with auto filename

    One more thing. Is there a way to modify the folder this is being saved to?
    n/a

  5. #5
    Board Regular Domski's Avatar
    Join Date
    Jan 2005
    Location
    Leeds, UK
    Posts
    7,178

    Default Re: ANSWER! How to create an excel to PDF macro with auto filename

    I would just point out that you don't need to use Sendkeys, which I would always avoid unless really necessary.

    If you add the Adobe Distiller library in the VBA editor under Tools...References you can then control Adbobe directly. I've posted some examples before of how to do this although I have to admit others have struggled to replicate it the way they want.

    I know it works however as I use it to create hundreds of documents each month.

    Dom
    "May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."

    - Guidelines For Posting

    - Mr Excel Articles and PodCasts

    - Display sheet using HTML Maker or Excel Jeanie

    - Something that makes me laugh

    - Please try to remember to use code tags when posting your VBA code: [code] Your code here [/code]

  6. #6
    New Member
    Join Date
    Jun 2008
    Location
    California
    Posts
    6

    Default Re: ANSWER! How to create an excel to PDF macro with auto filename

    To modify the directory where the PDF file is saved to, simply replace the orange section in the following line of code:

    Filename = "C:\Folder1\SubFolder1\" & ActiveSheet.Range("InvNbr").Value & ".pdf"


    For example, if you wanted to save your PDF file to a folder called "PDF Files" that is located in a folder called "Business Files" that is located on your D drive, then the code would read as follows:

    Filename="D:\Business Files\PDF Files\" & ActiveSheet.Range("InvNbr").Value & ".pdf"

    You always have to reference the directory back to the drive. So, if your directory is on your F Drive, then you need to start with "F:\" and then list each subsequent folder until arriving at your destination folder.

    Does this make sense?

  7. #7
    New Member
    Join Date
    Jun 2008
    Location
    California
    Posts
    6

    Default Re: ANSWER! How to create an excel to PDF macro with auto filename

    Domski,

    Let me just say that you were a big part of the research I did when trying to figure out how to get acrobat to take a filename automatically. Thank you for your contributions in these message boards. I actually tried your macro using Distiller and got it to work!

    But my #1 priority in writing this macro was quickness and your macro took a little bit too long for what I was wanting to accomplish. In the end, I decided to go with using Send Keys.


    So to all those reading this,

    If you don't mind a macro that takes a little bit longer, please see Domski's recommendation here (Save Excel Sheet As PDF File (SOLVED)). It is probably a little more reliable than mine. But if quick and easy is what your looking for, than I believe that using SendKeys is a good alternative.

    Best of luck to all!

  8. #8
    Board Regular
    Join Date
    May 2008
    Posts
    128

    Default Re: ANSWER! How to create an excel to PDF macro with auto filename

    Sub MakePDFNS()

    ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
    "Adobe PDF:", Collate:=True
    newHour = Hour(Now())
    newMinute = Minute(Now())
    newSecond = Second(Now()) + 2
    waitTime = TimeSerial(newHour, newMinute, newSecond)
    Application.Wait waitTime
    Filename = "G:\Group\Gfin\Rob_Young\" & ActiveSheet.Range("NS!C3:C4").Value & "NS"
    SendKeys Filename & "{ENTER}", False

    End Sub


    Does not work. The line "Filename = "G:\Group...." is giving me a Type Mismatch Error. Any ideas why this might be?

    Please help?

    Rob

  9. #9
    Board Regular random_noise's Avatar
    Join Date
    Dec 2007
    Location
    Scotland
    Posts
    367

    Default Re: ANSWER! How to create an excel to PDF macro with auto filename

    I think that may be bacuse you are trying to use the value of more that 1 cell in that line.

    try
    Code:
    Filename = "G:\Group\Gfin\Rob_Young\" & ActiveSheet.Range("NS!C3").Value & ActiveSheet.Range("NS!C4").Value & "NS"
    intstead

    Also dont think it matters but you're not saying its a .pdf either
    Dave

    --
    "Chew before you swallow."
    George W. Bush, On TV, about his passing out eating a pretzel
    --

  10. #10
    Board Regular
    Join Date
    May 2008
    Posts
    128

    Default Re: ANSWER! How to create an excel to PDF macro with auto filename

    Thanks, I have solved the cell reference error... and no, it doesn't matter about putting .pdf at the end because when you print to your Adobe PDF it auto saves it as a pdf - no need, but i do need the file to be saved as (todays date)XX (xx = province short form)

    Still getting a type mismatch error however.

Page 1 of 8 123 ... LastLast

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com