Page 3 of 4 FirstFirst 1 2 3 4 LastLast
Results 21 to 30 of 37

Thread: Macro to create PDF from Excel 2003 and send as attachment in email

  1. #21

    Use code to make dyn file names from cell contents and save to dyn dir



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hey Ken,


    Quick question for ya (I'm sure not a quick answer tho....)


    I would like to know what I will need to modify in your code to save a pdf with its name derived from a cell in the active sheet.

    For example:

    I have a sheet with a cell that contains a concatentation of things from other cells:

    Sheet1!A5 = JoeSmith_12345_BillingStatement_05-21-2012

    Thus I want the file name to be:
    *CustomerName*_*CustomerAcctNumber*_BillingStatement_MM-DD-YYYY.pdf

    and be saved to a UNC path:
    \\ data \ wireless \ receivable \ *CustomerName* \ *CustomerName_YYYY*

    so when you looke at the full path to the file you see:
    UNCPath\ *CustomerName*_*CustomerAcctNumber*_BillingStatement_MM-DD-YYYY.pdf

    Please note that that the items between the *'s are variable and are derived from other areas of the same sheet.

    One other nice thing would be if the dir does not exist then mkdir on the fly without user intervention being needed.

    I also will need to know what module I will need to palce the code for it to work. Thank a million in advance!!

    - Jerry L Englehaupt
    Wireless World

  2. #22

    Saving Statements

    Jerry,

    See if this is what you might be shooting for.

    1. create this directory or folder structure before you get started

    C:\data\wireless\receivable

    the macro will automaticallly create the rest of the folder structure and save the PDF .

    just fill in a customer name Ex. ( Jon Doe) and a account number ex.( JD0001) and click the Save Statement Button

    when it is done you would have the following folder structure

    C:\data\wireless\receivable\Jon Doe\Jon Doe_2012\ JonDoe_JD0001_BillingStatement_05-21-2012.pdf

    the page is real crude layout just so you can see how it all works.

    Hope this helps .
    Attached Files Attached Files

  3. #23
    tommyt61,

    Thanks for the quick reply. I think I failed to mention I am using office 2003. I went to use your sheet example; it wanted me to download the office compatibility pack for 2007. Not sure what to do at this point. Is there a way for this to be do natively in 2003 (.xls)?

    BTW I am not familiar with all the new office stuff. Is the .xlsm for excel 2010?

    Thanks,


    Jerry L Englehaupt
    Wireless World

  4. #24
    Seeker Kas2401's Avatar
    Join Date
    May 2012
    Location
    Johannesburg SA
    Posts
    9
    Articles
    0
    Hi There

    Having been involved with VB for the past week my skill-set is something to be desired. Lets see what the experts(have been's under pressure - sic) have to say!

  5. #25

    .xls file for 2003

    Here it is in 2003 format. Office 2007 and forward is a different format. i forget that alot of people are still running 2003 and earlier. The office compatability pack can be downloaded from Microsoft. Install it and it will allow 2003 to open files that have been created in 2007 and later.
    Attached Files Attached Files

  6. #26
    Thank you! I will check it out and let you know, but the test file works nice! I was able to get rid of the C:\ and use a UNC instead. Thanks! Thanks! ... and THANKS!!!

    - Jerry Englehaupt
    Wireless World

  7. #27
    Hi Guys,


    Now I am stuck with something like below, When Terms payslip & sheet1 refer to sample data attached.

    Now I have certain requirement to a form which is payslip sheet, the data should be extracted from Sheet1 when any individual check box is created....at last the same data should be exported with name of the employee which is in sheet1 as a mail......to progress this i need your guidance.


    Its kinda of urgent...please help me.


    Thanks ahead,
    Nani
    Attached Files Attached Files

  8. #28
    HI Ken,
    Please help me. I need to create a drop down list or buttons to give an option to convert an Excel 2003 to
    1) Word
    2) PDF
    3) Power Point
    I am totally new to macros. I tried using the above piece of code for PDF but I have CutePDF in my system so maybe that is why it does not work.
    If you have an existing code to do so please paste it here so that I can work else guide me to do so.

  9. #29

    I am not able to solve this datatype issue. please give me the solution.

    I am not able to solve this datatype issue. please give me the solution. what changes i suppose to make to work this code?

    Quote Originally Posted by Paulmer View Post
    Hi Ken,

    Please bear with me. The above code works perfectly in the test workbook however when I transfer the same coding to my real project I'm having this error. Would really appreciate your help. Thanks alot.
    Attachment 371

  10. #30
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,344
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Quote Originally Posted by Avi View Post
    I am not able to solve this datatype issue. please give me the solution. what changes i suppose to make to work this code?
    See here: http://www.excelguru.ca/content.php?...pe-Not-Defined
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Page 3 of 4 FirstFirst 1 2 3 4 LastLast

Posting Permissions

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