Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 8 of 8

Thread: Create PDF File From Cell Values & Print To PDF

  1. #1
    Join Date
    22nd January 2008
    Posts
    2

    Create PDF File From Cell Values & Print To PDF

    Hi there,

    We use Excel 2003 to generate invoices. I've created a macro to generate an invoice number, print the invoice and save the values in a table.

    Now I want to enhance the macro to go a step further: I want to save the invoice as a PDF in a certain folder.

    The filename of the pdf should be the invoice number (plus customer name if that is possible). Is it possible to create a VBA script for this?

    (In short: generate PDF and save it in folder c:/invoices/.
    For filename capture invoice number from cell A12 + maybe also custormer name in cell A1)
    PS Im using Adobe Acrobat 7.0 prof.

    Many thanks in advance.

    Jim
    The Netherlands

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    19th January 2007
    Location
    South Carolina, USA
    Posts
    9,206

    Re: PDF generation via macro

    jimfx,

    Welcome to Ozgrid . . . best Excel forum on the web!

    Because printing to PDF involves third party software, be it Adobe or another PDF printer, Excel, as far as I know, cannot send the file name and location directly to the PDF printer.

    You could try the following approach. The code below generates the file name and location and sends it to the clipboard.
    VB:
    Sub PDFsave() 
         
        Application.CutCopyMode = False 'clear clipboard
        Dim cname, invnum 
        Dim fpath As String 
         
        fpath = "C:\Myfoldername\" 
         
        With Worksheets("Sheet1") 
            cname = Range("A1").Value 
            invnum = Range("B1").Value 
            Range("D1").Value = fpath & cname & " " & invnum & ".pdf" 'change D1 to a cell of your choice
            Range("D1").Copy 'send to clipboard
        End With 
         
        Application.ActivePrinter = "doPDF v5 on DOP5:" 'change this line whatever PDF printer you use
         
         'code here to run AutoIt
         
    End Sub 
    
    
    From there, you might use an automated scripting program like AutoIt to paste in the string with the file path & name and execute the "print/save" button. I haven't used AutoIt myself, but am planning to give it a try as I, too, have a need to something similar.
    AutoIt has great reviews and is completely free. AutoIt homepage.

    Edit: added line to include "Application.ActivePrinter" (thanks shg, I overlooked)
    Last edited by AAE; February 10th, 2008 at 03:37.
    AAE
    ----------------------------------------------------

    Forum Rules | Message to Cross Posters | How to use Tags

  3. #3
    Join Date
    12th September 2006
    Location
    The Great State of Texas
    Posts
    4,323

    Re: PDF generation via macro

    Record a macro that selects Adobe PDF (or whatever you use) as the active printer and prints the document.
    Entia non sunt multiplicanda sine necessitate.
    MS MVP - Excel

  4. #4
    Join Date
    8th December 2006
    Location
    New Zealand
    Posts
    3,924

    Re: PDF generation via macro

    Will Riley posted some code on how to print to PDF including name the file, have a search for post by him.

    EDIT: Here it is http://www.ozgrid.com/forum/showthre...ighlight=Print

  5. #5
    Join Date
    22nd January 2008
    Posts
    2

    Re: Generate & Print PDF File From Workbook

    Hello AAE,
    Thanks for your reply. I still have to go through a lot of steps to achieve what I finally want. (as descriped in my previous post)
    To focus on the first small step: copy to clipboard. This is not going right. When I use your code, The string is copied, but I suppose it is not actually in the windows clipboard. Because when I execute your macro, and then manually try to save the excelfile, I try 'paste' via <CTRL> V but it is not working.

    What am I doing wrong?

    thanks in advance,

    Jim

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    19th January 2007
    Location
    South Carolina, USA
    Posts
    9,206

    Re: Generate & Print PDF File From Workbook

    This seems to work for me, although the use of Sendkeys is pretty much eschewed by the forum experts.

    When the PDF printer dialog opens, just highlight the entire line and press Ctrl+v to paste in the path and file name.

    VB:
    Sub PDFsave() 
         
        Application.CutCopyMode = False 'clear clipboard
        Dim cname, invnum 
        Dim fpath As String 
         
        fpath = "C:\Temp\" 'change this path per your requirements
         
        With Worksheets("Sheet1") 
            Dim fname As Variant 
            fname = Range("D1").Text 
            cname = Range("A1").Value 
            invnum = Range("B1").Value 
            Range("D1").Value = fpath & cname & " " & invnum & ".pdf" 'change D1 to a cell of your choice
        End With 
         
        Application.SendKeys ("^c~") 
        InputBox "clipboard", , Sheets("Sheet1").Range("D1").Text 
         
        Application.ActivePrinter = "doPDF v5 on DOP5:" 'change this line and the next to whatever PDF printer you use
        Application.ActiveSheet.PrintOut Copies:=1, ActivePrinter:="doPDF v5 on DOP5:" 
         
    End Sub 
    
    
    AAE
    ----------------------------------------------------

    Forum Rules | Message to Cross Posters | How to use Tags

  7. #7
    Join Date
    21st January 2013
    Posts
    3

    Print & save the invoice with customer name & invoice no

    i am creat a retail invoice & 1 am creat a print & save button & print the invoice in PDF format with invoice no & customer name and save the invoice in d:/invoice folder/Anil kumar 0001.pdf
    I have a single excel sheet (invoice copy) which i want to print copies & Range (A16:E25").ClearContents
    The invoice in the first page to be printed should be
    "original for buyer"
    The invoice in the second page to be printed should be
    "Duplicate for seller"
    The third page should be " Transporter's Copy"

    and so on
    How do i over come this problem
    Can anyone help me
    Thanks a ton in advance
    example: anil kumar is the buyers
    0001 is the invoice no.
    save in pdf format
    please halp us
    I AM ATTACH THE FILES FOR YOUR HALP PLEASE SEE THE FILE AND CREAT A VBA
    1 AM CREAT A VBA =SPELLINDIAN() TO TOAL AMOUNT CONVERT IN TO WORDS VBA OPEN PASSWORD IS 123456
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    15th August 2005
    Location
    Mechelen, Belgium
    Posts
    6,068

    Re: Create PDF File From Cell Values & Print To PDF

    Hello

    This is a very old topic. Please do not post in here.
    Also, if you have a question, start your own topic instead of hijacking someone else's topic.

    Thanks.
    Regards,

    Wigi

    Excel MVP 2011-2014



    For more Excel memes: visit http://www.wimgielis.be ==> English articles ==> Excel memes

    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Create Sub Directories From Cell Values
    By Jeffmcneese in forum EXCEL HELP
    Replies: 8
    Last Post: September 12th, 2012, 05:13
  2. Replies: 5
    Last Post: February 28th, 2007, 12:50
  3. Cell Values As File Paths
    By rm_hodson in forum EXCEL HELP
    Replies: 10
    Last Post: February 22nd, 2007, 05:16
  4. Print PDF File Using Cell Values
    By martusj1 in forum EXCEL HELP
    Replies: 3
    Last Post: December 15th, 2006, 03:24
  5. Print file based on cell criteria
    By cnydame in forum EXCEL HELP
    Replies: 4
    Last Post: June 1st, 2006, 07:32

Bookmarks

Posting Permissions

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