Announcement

Collapse
No announcement yet.

Create PDF File From Cell Values & Print To PDF

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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 Iím using Adobe Acrobat 7.0 prof.

    Many thanks in advance.

    Jim
    The Netherlands

  • #2
    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.
    Code:
    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, 03:37.
    AAE
    ----------------------------------------------------

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

    Comment


    • #3
      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.

      Comment


      • #4
        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
        Reafidy

        Forum Rules | Anatomy Of A Good Thread Title | What Have You Tried So Far? | Are You A Cross Poster?

        Comment


        • #5
          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

          Comment


          • #6
            Re: Generate &amp; 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.

            Code:
            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

            Comment


            • #7
              Print &amp; save the invoice with customer name &amp; 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

              Comment


              • #8


                Re: Create PDF File From Cell Values &amp; 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.com ==> English articles ==> Excel memes

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

                Comment

                Working...
                X