Announcement

Collapse
No announcement yet.

Print to pdf using Macro

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Print to pdf using Macro



    Dear Expert,

    I would like to have a macro button on Sheet1 and the function of this button is to print Sheet2 to pdf. Can anyone please help to configure the macro?

    Please help!

    Regards,
    Serenapoh

  • #2
    Re: Print to pdf using Macro

    serenapoh,


    I'm assuming that you're asking because you all ready HAVE that ability on your PC.

    If so why not record the actions that you need to take and then use/modify that macro to fit your specific needs. I don't have that ability on my pc so it must be something that is specific to your setup.
    Regards,
    Barry

    My Favorite New Thing:
    Dynamic Named Ranges



    The alternative for
    "Press Any Key To Continue."

    and we all have one we'd like to use it on

    1. Cross Posting Etiquette
    2. Are You Here To Learn: What Have You Tried?
    3. Your Best Resource to begin learning VBA: RECORD A MACRO . . . Then Study It!

    Comment


    • #3
      Re: Print to pdf using Macro

      I think the user might be using Acrobat distiller.... Is this the case serenapoh ?

      If so, I posted some code up a while back that I had adapted from the web

      http://www.ozgrid.com/forum/showthread.php?t=25773
      Kind Regards, Will Riley

      LinkedIn: Will Riley

      Comment


      • #4
        Re: Print to pdf using Macro

        Thank you for the advice.
        I do not have Acrobat distiller. May I know how should I download it?

        I have downloaded and installed PrimoPDF (freeware) which enable me to print Excel worksheet to PDF. What I have to do is just click on File-Print-Select Printer as PrimoPDF.

        The freeware can be downloaded from
        http://www.snapfiles.com/get/primopdf.html


        My problem is to configure a button in Sheet1 to print Sheet2 to PDF by just clicking on the button.

        Please help.

        Comment


        • #5
          Re: Print to pdf using Macro

          Acrobat Distiller is part of Adobe Acrobat (full version) - it costs $$ so you can't just download it

          As for your freeware, without having a play around with it i'm not sure. If I get time later, I will take a look at it from home.
          Kind Regards, Will Riley

          LinkedIn: Will Riley

          Comment


          • #6
            Re: Print to pdf using Macro

            The simplest way to achieve this is to use the print dialog
            Code:
            Application.Dialogs(xlDialogPrint).Show
            The pdf writer will appear in the Printer dropdown and you can then create your pdf
            Hope that Helps

            Roy

            New users should read the Forum Rules before posting

            For free Excel tools & articles visit my web site

            If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

            RoyUK's Web Site

            royUK's Database Form

            Where to paste code from the Forum

            About me.

            Comment


            • #7
              Re: Print to pdf using Macro

              Many thanks for the advice. It works!!!

              Is it possible to print Sheet 2 when the print button is on Sheet 1.
              Can it be coded to set the printer to PrimoPDF and click OK button automatically (which means that the print window does not appear on the screen)?

              Please help!!!

              Comment


              • #8
                Re: Print to pdf using Macro

                serenopah,

                Like I stated in the first reply in this thread. Try recording a macro while you do the steps required to print using the pdf in your print menu.

                Excel should record all the steps. If we don't have the software, and are not willing to install it, then you will need to record it and either post it asking how to alter it or use it as is.
                Regards,
                Barry

                My Favorite New Thing:
                Dynamic Named Ranges



                The alternative for
                "Press Any Key To Continue."

                and we all have one we'd like to use it on

                1. Cross Posting Etiquette
                2. Are You Here To Learn: What Have You Tried?
                3. Your Best Resource to begin learning VBA: RECORD A MACRO . . . Then Study It!

                Comment


                • #9
                  Re: Print to pdf using Macro

                  Try this, change the sheet that i have specified to suit your needs
                  Code:
                  '---------------------------------------------------------------------------------------
                  ' Module    : Module2
                  ' DateTime  : 31/10/2005 19:40
                  ' Author    : Roy  Cox
                  ' Website   : www.excel-it.com
                  ' Purpose   : automate pdf with PrimoPDF
                  '---------------------------------------------------------------------------------------
                  Option Explicit
                  
                  Sub PrintToPrimoPDF()
                  Dim strCurrentPrinter As String
                      strCurrentPrinter = Application.ActivePrinter ' save the currently active printer
                      On Error Resume Next ' ignore  errors
                      Application.ActivePrinter = "PrimoPDF on Ne04:" ' change to PrimoPdf
                      Sheet1.PrintOut ' print the sheet1
                      Application.ActivePrinter = strCurrentPrinter ' change back to the original printer
                      On Error GoTo 0 ' resume normal error handling
                  End Sub
                  Hope that Helps

                  Roy

                  New users should read the Forum Rules before posting

                  For free Excel tools & articles visit my web site

                  If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

                  RoyUK's Web Site

                  royUK's Database Form

                  Where to paste code from the Forum

                  About me.

                  Comment


                  • #10
                    Re: Print to pdf using Macro

                    Thanks a lot!!!

                    However, I would like to put the button on Sheet1 to configure printing job for Sheet2. It doesn't work even when I change this:

                    Code:
                     Sheet2.PrintOut   ' print the sheet2

                    Please help!

                    Comment


                    • #11
                      Re: Print to pdf using Macro

                      serenapoh,

                      I'm not sure but!!!

                      Try

                      Code:
                      Worksheets("Sheet2").PrintOut
                      Regards,
                      Barry

                      My Favorite New Thing:
                      Dynamic Named Ranges



                      The alternative for
                      "Press Any Key To Continue."

                      and we all have one we'd like to use it on

                      1. Cross Posting Etiquette
                      2. Are You Here To Learn: What Have You Tried?
                      3. Your Best Resource to begin learning VBA: RECORD A MACRO . . . Then Study It!

                      Comment


                      • #12


                        Re: Print to pdf using Macro

                        I think that starting another thread would have been justified after just about 6 years.

                        However, you must make sure that all sheets have the same print quality before printing to PDF.

                        If you want, you can use snippets from this.
                        Code:
                        Sub TryThisPrintMultiples()
                          Dim Ws As Worksheet, bladen() As Variant, i As Integer
                          DefaultPrinter = Application.ActivePrinter
                          Application.ActivePrinter = "CutePDF Writer on CPW2:"
                          
                          Application.ScreenUpdating = False
                          
                          For Each Sht In Application.Worksheets
                        Sht.Activate
                        With ActiveSheet.PageSetup
                            .PrintQuality = 600
                        End With
                        Next Sht
                          
                          ReDim bladen(1 To Sheets.Count)
                          For Each Ws In Worksheets
                            If Ws.Range("A1").Value <> "" Then
                              i = i + 1
                              bladen(i) = Ws.Name
                            End If
                          Next Ws
                          
                          If i > 0 Then
                            ReDim Preserve bladen(1 To i)
                            Sheets(bladen).PrintOut
                          End If
                          
                          Application.ActivePrinter = DefaultPrinter
                          Sheets(1).Select
                          Application.ScreenUpdating = True
                        End Sub
                        HTH
                        John

                        Comment

                        Working...
                        X