printing in pdf format

luis gaspper
StarLounger
Posts: 68
Joined: 03 Aug 2020, 05:23

printing in pdf format

Post by luis gaspper »

Hi Gents,
I have the following code that works well and I need just one point in it

Code: Select all

   Sub PRINT_ALL()
   Dim Sh As Worksheet, LR As Long, Cel As Range
   Dim Stx1 As String, Stx2 As String, St1 As String, St2 As String, Texte1 As String, Texte2 As String
   
   Set Sh = Sheets("First stage")
   LR = Sh.Cells(Sh.Rows.Count, 1).End(xlUp).Row
   Stx1 = "Dollars ": Stx2 = "Cents ": St1 = "and ": St2 = "No non"
    
   Texte1 = Ar_WriteDownNumber(Int(Cells(LR, "Z")))
   Texte2 = Ar_WriteDownNumber(100 * Cells(LR, "Z") Mod 100)

    With Cells(LR + 1, "A")
   .Value = "Exchange an amount :  "
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlCenter
         .Font.Size = 16
         .Font.Name = "Times New Roman"
         .Font.Bold = True
         .RowHeight = 24
      End With

      With Cells(LR + 1, "B")
       If 100 * Cells(LR, "Z") Mod 100 = 0 Then .Value = "Only " & Texte1 & Stx1 & St2 Else _
        .Value = "Only " & Texte1 & Stx1 & St1 & Texte2 & Stx2 & St2
      
      .Offset(1, 0).Value = "Signature                                                                                Signature                                                               Signature                                                                 Signature                                                                      Signature                                          Signature"
      .Offset(1, 0).HorizontalAlignment = xlLeft
      .Offset(1, 0).VerticalAlignment = xlCenter
      .Offset(1, 0).Font.Size = 16
      .Offset(1, 0).Font.Name = "Times New Roman"
      .Offset(1, 0).Font.Bold = True
      .Offset(1, 0).RowHeight = 24
      
      .Offset(4, 0).Value = "xxxxxxxxxxx                                              data                           /                  /                                         Signature                                                dddddddddddd                                                            date                          /           /                           Signature"
      .Offset(4, 0).HorizontalAlignment = xlLeft
      .Offset(4, 0).VerticalAlignment = xlCenter
      .Offset(4, 0).Font.Size = 16
      .Offset(4, 0).Font.Name = "Times New Roman"
      .Offset(4, 0).Font.Bold = True
      .Offset(4, 0).RowHeight = 24

      End With
     ActiveWindow.SelectedSheets.PrintOut Copies:=1
    Range(Cells(LR + 1, "A"), Cells(LR + 7, "B")) = ""
   End Sub
What I need to add in this code is some lines to print the workbook in pdf format In the same file path
How can modify this line to achieve this?

Code: Select all

    ActiveWindow.SelectedSheets.PrintOut Copies:=1
Thanks in advance for any assistance.
Last edited by luis gaspper on 14 Aug 2020, 02:06, edited 1 time in total.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: printing in pdf format

Post by HansV »

For example:

Code: Select all

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ActiveWorkbook.Path & "\Export.pdf"
You can change the filename of course.
Best wishes,
Hans

luis gaspper
StarLounger
Posts: 68
Joined: 03 Aug 2020, 05:23

Re: printing in pdf format

Post by luis gaspper »

That's great ...Thank you very much Mr.Hans for your great help
Many thanks and kind regards

luis gaspper
StarLounger
Posts: 68
Joined: 03 Aug 2020, 05:23

Re: printing in pdf format

Post by luis gaspper »

I am so sorry for disturbing you again Mr. Hans
The code shown above is for printing the first stage sheet only
What I am wanting to do is Print the two sheet ( first stage & second stage )
How can I modify this line of code to achieve that

Code: Select all

   Set Sh = Sheets("First stage")
This attachment is for illustration only ....Thanks advanced for help
You do not have the required permissions to view the files attached to this post.
Last edited by luis gaspper on 14 Aug 2020, 07:31, edited 1 time in total.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: printing in pdf format

Post by HansV »

There is no code in your workbook...
Best wishes,
Hans

luis gaspper
StarLounger
Posts: 68
Joined: 03 Aug 2020, 05:23

Re: printing in pdf format

Post by luis gaspper »

I am so sorry ... I have updated the file.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: printing in pdf format

Post by HansV »

Insert the following line above the ActiveSheet.ExportAsFixedFormat line:

Code: Select all

    Worksheets(Array("first stage", "second stage")).Select
and below the ActiveSheet.ExportAsFixedFormat line:

Code: Select all

    Sh.Select
Best wishes,
Hans

luis gaspper
StarLounger
Posts: 68
Joined: 03 Aug 2020, 05:23

Re: printing in pdf format

Post by luis gaspper »

Thanks a lot Mr. Hans for the addition ... but For more clarification
This function is used to convert currency numbers into words.

Code: Select all

Public Function Ar_WriteDownNumber(Number_Value As String, Optional Main_Currency As String, Optional Small_Currency As String, Optional Main_To_Small_Factor As Integer)
and This code is used to insert currency numbers after converting them into words in a specified cell
In addition to inserting a series of signatures as footers below the last table

Code: Select all

 Sub PRINT_ALL()
   Dim Sh As Worksheet, LR As Long, Cel As Range, arrSheets As Variant
   Dim Stx1 As String, Stx2 As String, St1 As String, St2 As String, Texte1 As String, Texte2 As String
   
 Set Sh = Sheets("First stage")
  LR = Sh.Cells(Sh.Rows.Count, 1).End(xlUp).Row
   
 Stx1 = "Dollars ": Stx2 = "Cents ": St1 = "and ": St2 = "No non"
    
   Texte1 = Ar_WriteDownNumber(Int(Cells(LR, "Z")))
   Texte2 = Ar_WriteDownNumber(100 * Cells(LR, "Z") Mod 100)

    With Cells(LR + 1, "A")
   .Value = "Exchange an amount :  "
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlCenter
         .Font.Size = 16
         .Font.Name = "Times New Roman"
         .Font.Bold = True
         .RowHeight = 24
      End With

      With Cells(LR + 1, "B")
       If 100 * Cells(LR, "Z") Mod 100 = 0 Then .Value = "Only " & Texte1 & Stx1 & St2 Else _
        .Value = "Only " & Texte1 & Stx1 & St1 & Texte2 & Stx2 & St2
      
      .Offset(1, 0).Value = "Signature                                                                                Signature                                                               Signature                                                                 Signature                                                                      Signature                                          Signature"
      .Offset(1, 0).HorizontalAlignment = xlLeft
      .Offset(1, 0).VerticalAlignment = xlCenter
      .Offset(1, 0).Font.Size = 16
      .Offset(1, 0).Font.Name = "Times New Roman"
      .Offset(1, 0).Font.Bold = True
      .Offset(1, 0).RowHeight = 24
      
      .Offset(4, 0).Value = "xxxxxxxxxxx                                              data                           /                  /                                         Signature                                                dddddddddddd                                                            date                          /           /                           Signature"
      .Offset(4, 0).HorizontalAlignment = xlLeft
      .Offset(4, 0).VerticalAlignment = xlCenter
      .Offset(4, 0).Font.Size = 16
      .Offset(4, 0).Font.Name = "Times New Roman"
      .Offset(4, 0).Font.Bold = True
      .Offset(4, 0).RowHeight = 24

      End With
  ' ActiveWindow.SelectedSheets.PrintOut Copies:=1
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ActiveWorkbook.Path & "\Export.pdf"
    Range(Cells(LR + 1, "A"), Cells(LR + 7, "B")) = ""
         End With
   End Sub
I tried modify this lines of code from

Code: Select all

  Set Sh = Sheets("First stage")
  LR = Sh.Cells(Sh.Rows.Count, 1).End(xlUp).Row
TO

Code: Select all

   'Set Sh = ThisWorkbook.Worksheets("DATA")
  ' arrSheets = Array("First stage", "Second stage")
  ' LR = Sh.Cells(Sh.Rows.Count, 1).End(xlUp).Row
  ' With Sh
but it didn't work properly with me .... my question is how can print the two sheets ( first stage & second stage ) one by one
There is more in the next post

luis gaspper
StarLounger
Posts: 68
Joined: 03 Aug 2020, 05:23

Re: printing in pdf format

Post by luis gaspper »

Thanks for the follow-up
I have attached 2 picture so you can see what I am trying to achieve.
The first picture is an example of the first stage sheet
and The second photo is an example of the second stage sheet
Again, thank you so much for all your time and help.
You do not have the required permissions to view the files attached to this post.
Last edited by luis gaspper on 14 Aug 2020, 22:07, edited 5 times in total.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: printing in pdf format

Post by HansV »

Most of your macro acts on the active sheet, whichever that is when you run it. Can you explain more clearly what you want?
Best wishes,
Hans

luis gaspper
StarLounger
Posts: 68
Joined: 03 Aug 2020, 05:23

Re: printing in pdf format

Post by luis gaspper »

Thank you very much Mr.Hans
I'll explain more
The first stage sheet contains two pages, ready to be printed and The second stage sheet contains one page ready to be printed.
When you run the macro Will be printed Footer rows and row of converting numbers into words In the last page only.
There might be 50 pages ready to be printed for example Will be printed Footer rows and row of converting numbers into words In the last page.
For testing, Please run the code on the name of the current sheet Then change the sheet name to second stage to see what I mean.
You will notice that the Footer rows and row of converting numbers into words Appear in the last page only
Sorry I didn't know a better way to describe this problem .... Please refer to the pictures above .... Here's an attachment
Again, thank you so much for all your time and help.
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: printing in pdf format

Post by HansV »

I'm afraid I still don't understand. Should PRINT_ALL do something on the first stage sheet, or on the second stage sheet, or on both?
Best wishes,
Hans

luis gaspper
StarLounger
Posts: 68
Joined: 03 Aug 2020, 05:23

Re: printing in pdf format

Post by luis gaspper »

on both

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: printing in pdf format

Post by HansV »

Is this better?

Code: Select all

Sub PRINT_ALL()
    Dim Sh As Worksheet, LR As Long, Cel As Range
    Dim Stx1 As String, Stx2 As String, St1 As String, St2 As String, Texte1 As String, Texte2 As String

    For Each Sh In Worksheets(Array("First stage", "SEcond stage"))
        LR = Sh.Cells(Sh.Rows.Count, 1).End(xlUp).Row
        Stx1 = "Dollars ": Stx2 = "Cents ": St1 = "and ": St2 = "No non"

        Texte1 = Ar_WriteDownNumber(Int(Cells(LR, "Z")))
        Texte2 = Ar_WriteDownNumber(100 * Cells(LR, "Z") Mod 100)

        With Sh.Cells(LR + 1, "A")
            .Value = "Exchange an amount :  "
            .HorizontalAlignment = xlLeft
            .VerticalAlignment = xlCenter
            .Font.Size = 20
            .Font.Name = "Times New Roman"
            .Font.Bold = True
            .RowHeight = 24
        End With

        With Sh.Cells(LR + 1, "B")
           If 100 * Sh.Cells(LR, "Z") Mod 100 = 0 Then
                .Value = "Only " & Texte1 & Stx1 & St2
            Else
                .Value = "Only " & Texte1 & Stx1 & St1 & Texte2 & Stx2 & St2
            End If

            .Offset(1, 0).Value = "Signature                                                                                Signature                                                               Signature                                                                 Signature                                                                      Signature                                          Signature"
            .Offset(1, 0).HorizontalAlignment = xlLeft
            .Offset(1, 0).VerticalAlignment = xlCenter
            .Offset(1, 0).Font.Size = 20
            .Offset(1, 0).Font.Name = "Times New Roman"
            .Offset(1, 0).Font.Bold = True
            .Offset(1, 0).RowHeight = 24

            .Offset(4, 0).Value = "Signature                                              data                           /                  /                                         Signature                                                Signature                                                            date                          /           /                           Signature"
            .Offset(4, 0).HorizontalAlignment = xlLeft
            .Offset(4, 0).VerticalAlignment = xlCenter
            .Offset(4, 0).Font.Size = 20
            .Offset(4, 0).Font.Name = "Times New Roman"
            .Offset(4, 0).Font.Bold = True
            .Offset(4, 0).RowHeight = 24
        End With
        Sh.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "\" & Sh.Name & ".pdf"
        Sh.Range(Sh.Cells(LR + 1, "A"), Sh.Cells(LR + 7, "B")).ClearContents
    Next Sh
End Sub
You don't have to select the sheet - the macro will process both.
Best wishes,
Hans

luis gaspper
StarLounger
Posts: 68
Joined: 03 Aug 2020, 05:23

Re: printing in pdf format

Post by luis gaspper »

Thanks a lot Mr.Hans for helping me in solving this issue
Printing has been tested in pdf format and everything is fine.
Can I ask for another point please
When testing printing by this line ... the first stage sheet has been printed Only.

Code: Select all

ActiveWindow.SelectedSheets.PrintOut Copies:=1
How can modify this line to print the first stage sheet and second stage sheet together.
Thanks again for your support

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: printing in pdf format

Post by HansV »

I already answered that higher up in this thread:

Code: Select all

    Worksheets(Array("first stage", "second stage")).Select
    ActiveWindow.SelectedSheets.PrintOut
    Worksheets("first stage").Select
Best wishes,
Hans

luis gaspper
StarLounger
Posts: 68
Joined: 03 Aug 2020, 05:23

Re: printing in pdf format

Post by luis gaspper »

I will come back to you tomorrow Before the curfew time

luis gaspper
StarLounger
Posts: 68
Joined: 03 Aug 2020, 05:23

Re: printing in pdf format

Post by luis gaspper »

Thanks again Mr.Hans for all the great help you offered
When printing the first and second stage sheets I discovered an error in first stage sheet.
The error is Not converting numbers into words In the cell Z
How can fix that. Thanks for your all the efforts and time.
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: printing in pdf format

Post by HansV »

I get a different result, but I see that I forgot two changes: the lines

Code: Select all

        Texte1 = Ar_WriteDownNumber(Int(Cells(LR, "Z")))
        Texte2 = Ar_WriteDownNumber(100 * Cells(LR, "Z") Mod 100)
should be

Code: Select all

        Texte1 = Ar_WriteDownNumber(Int(Sh.Cells(LR, "Z")))
        Texte2 = Ar_WriteDownNumber(100 * Sh.Cells(LR, "Z") Mod 100)
Best wishes,
Hans

luis gaspper
StarLounger
Posts: 68
Joined: 03 Aug 2020, 05:23

Re: printing in pdf format

Post by luis gaspper »

Thank you so much Mr.Hans .. worked like a charm.
Regards