Excel print to PDF Macro

[mazher]

Hi VBA Gurus,,

I am using the following code to export the selected area to the PDF files

Sub Print PDF()
Dim i As Integer
Dim minx As Integer
Dim maxy As Integer
Dim InvNo As String

minx = Sheets("InvBCC").Range("k4").Value
maxy = Sheets("InvBCC").Range("l4").Value

InvNo = Sheets("InvBCC").Range("h14").Value

For i = minx To maxy

    Range("k14").Value = i
    
    ThisFile = Sheets("InvBCC").Range("h14").Value
    ThisPath = Sheets("InvBCC").Range("n4").Value

    Sheets("InvBCC").Range("C4:H35").ExportAsFixedFormat _
           Type:=xlTypePDF, _
           Filename:=ThisPath & ThisFile & ".pdf", _
           Quality:=xlQualityStandard, IncludeDocProperties:=True, _
           IgnorePrintAreas:=False, OpenAfterPublish:=False

Next i

End Sub

I have recorded the macro and want to add the following code and tidy up.

With ActiveSheet.PageSetup

        .RightFooter = "Printed on &D at  &T"

        .PrintQuality = 600

        .CenterHorizontally = True

        .Orientation = xlPortrait

        .Draft = False

        .PaperSize = xlPaperA4

    End With

Please can someone club both of these codes and make them working.

Thanks in advance.

 

[Domenic]

Try the following code...

Option Explicit

Sub Print_PDF()

    Dim i As Integer
    Dim minx As Integer
    Dim maxy As Integer
    Dim InvNo As String
    Dim ThisFile As String
    Dim ThisPath As String
   
    With Worksheets("InvBCC")
   
        With .PageSetup
            .RightFooter = "Printed on &D at  &T"
            .PrintQuality = 600
            .CenterHorizontally = True
            .Orientation = xlPortrait
            .Draft = False
            .PaperSize = xlPaperA4
        End With
       
        minx = .Range("k4").Value
        maxy = .Range("l4").Value
       
        InvNo = .Range("h14").Value
       
        For i = minx To maxy
       
            .Range("k14").Value = i
           
            ThisFile = .Range("h14").Value
            ThisPath = .Range("n4").Value
       
            .Range("C4:H35").ExportAsFixedFormat _
                   Type:=xlTypePDF, _
                   Filename:=ThisPath & ThisFile & ".pdf", _
                   Quality:=xlQualityStandard, _
                   IncludeDocProperties:=True, _
                   IgnorePrintAreas:=False, _
                   OpenAfterPublish:=False
       
        Next i
       
    End With
   
   
End Sub

Note that the Option Explicit statement has been added at the beginning of the code (actually, this statement should be at the top of the module before any code), which forces the explicit declaration of variables and will help catch an spelling errors in the code.

Also, note that the code has not been tested, so if there are any problems please let me know.

Hope this helps!

 

[mazher]

Extremely thankful Domenic, its working fine.

Please if possible I need a Message box displaying message " Publishing 1 of 10" and at the end it changes to "10 Invoices Published Successfully"

Thanks in advance

 

[Domenic]

You're very welcome, glad I could help.

How do we determine how many pages will be printed?

 

[mazher]

Thanks Domenic again.

Value in K14 will be the first page.
Value in L14 will be the last page.

Hope this helps.

 

[Domenic]

Using a message box would mean that for each invoice that is published a message box would be displayed and the user would have to click OK to continue. This would be very inconvenient and likely very annoying to the user.

Maybe you actually want a progress bar using a userform. If so, you would first need to create a userform, and then include the appropriate code. You can search Google for some examples.

An alternative would be to simply use Excel's status bar to indicate the progress, and then use a message box at the end to convey the final result. Here's an example..

Option Explicit

Sub Print_PDF()

    Dim i As Integer
    Dim minx As Integer
    Dim maxy As Integer
    Dim InvNo As String
    Dim ThisFile As String
    Dim ThisPath As String
    
    With Worksheets("InvBCC")
    
        With .PageSetup
            .RightFooter = "Printed on &D at  &T"
            .PrintQuality = 600
            .CenterHorizontally = True
            .Orientation = xlPortrait
            .Draft = False
            .PaperSize = xlPaperA4
        End With
        
        minx = .Range("k4").Value
        maxy = .Range("l4").Value
        
        InvNo = .Range("h14").Value
        
        For i = minx To maxy
        
            Application.StatusBar = "Publishing " & i & " of " & maxy
        
            .Range("k14").Value = i
            
            ThisFile = .Range("h14").Value
            ThisPath = .Range("n4").Value
        
            .Range("C4:H35").ExportAsFixedFormat _
                   Type:=xlTypePDF, _
                   Filename:=ThisPath & ThisFile & ".pdf", _
                   Quality:=xlQualityStandard, _
                   IncludeDocProperties:=True, _
                   IgnorePrintAreas:=False, _
                   OpenAfterPublish:=False
        
        Next i
        
        Application.StatusBar = False
        
    End With
    
    MsgBox maxy & " Invoices Published Successfully", vbOKOnly, "Finished"
    
End Sub

Hope this helps!

 

[mazher]

Dear Domenic,

Extremely thankful for all your help as this is exactly what I was looking for.

I need to learn VBA , please can you or someone guide me from where to start as this automation saves a lot of time.

I know that It will me a long process and even if I start today after a year I will be able to make small codes required for my work.

Thanks once again.

 

[Domenic]

I would recommend a book by John Walkenbach called "Microsoft Office Excel 20xx Power Programming with VBA". In addition, this is a great place to learn. You can learn by asking questions like you've already done, learn from code offered by others, and by trying to answer questions yourself.

Cheers!