• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Invoice generation

F

ferocious12

Member
Hi All,
I have to create invoices regularly based on information contained in excel spreadsheet and want to want to automate the process.

for each row in "invoice details" I create a separate invoice and convert it to PDF. Invoice is a standard template (worksheet "template") and only three fields are populated from worksheet "invoice details". 3 fields to populate are:

1. date
2. invoice number and
3. total invoice amount

can you please help me to write a code that populate 3 fields to invoice template and create a separate tab for each row and convert each tab to separate PDF file.

Thanks !
 

Attachments

  • Workbook1.xlsx
    14.2 KB · Views: 9
ferocious12
You should fill/edit few 'template's values.
By Button, You will get those PDFs to same folder as Your Excel-file.
 

Attachments

  • Workbook1.xlsb
    21.5 KB · Views: 6
try this:

Code: 
Option Explicit

Sub createInvoice()
    Dim s As Worksheet, t As Worksheet, ws As Worksheet
    Set s = Sheets("invoice details")
    Set t = Sheets("template")
    Dim i As Long, lr As Long
    lr = s.Cells(Rows.Count, "A").End(xlUp).Row
    Dim sName As String
    Application.ScreenUpdating = False
    For i = 7 To lr
        t.Copy After:=Worksheets(Worksheets.Count)
        ActiveSheet.Name = s.Range("C" & i)
        ActiveSheet.Range("F3") = s.Range("A" & i)
        ActiveSheet.Range("F4") = s.Range("C" & i)
        ActiveSheet.Range("F19") = s.Range("B" & i)
    Next i
    Application.ScreenUpdating = True
End Sub
 
Modified to add creation of .pdf which is saved to the original workbook folder
Code: 
Option Explicit

Sub createInvoice()
    Dim s As Worksheet, t As Worksheet, ws As Worksheet
    Set s = Sheets("invoice details")
    Set t = Sheets("template")
    Dim i As Long, lr As Long
    lr = s.Cells(Rows.Count, "A").End(xlUp).Row
    Dim sName As String, sPath As String
    Application.ScreenUpdating = False
    sPath = ThisWorkbook.Path
    For i = 7 To lr
        t.Copy After:=Worksheets(Worksheets.Count)
        ActiveSheet.Name = s.Range("C" & i)
        ActiveSheet.Range("F3") = s.Range("A" & i)
        ActiveSheet.Range("F4") = s.Range("C" & i)
        ActiveSheet.Range("F19") = s.Range("B" & i)
            ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        sPath & "\" & ActiveSheet.Name & ".pdf", Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
        True
    Next i
    Application.ScreenUpdating = True
End Sub
 
Awesome. Can you please make one more change to the code. For all the invoices for which PDF is created "done" is added in the last column and code only generate invoice where "done" is not marked in last column
 
Awesome. Can you please make one more change to the code. For all the invoices for which PDF is created "done" is added in the last column and code only generate invoice where "done" is not marked in last column
 
Thanks guys !

Can you help me to further modify above code to:

1. create invoice only for filtered entries (customer A in this case) and not for all entries

2. mark "done" in the last column if an invoice is created for a row. And only create invoice for entries where last column is blank and doesn't say "done"

Code: 
Option Explicit

Sub createInvoice()
    Dim s As Worksheet, t As Worksheet, ws As Worksheet
    Set s = Sheets("invoice details")
    Set t = Sheets("template")
    Dim i As Long, lr As Long
    lr = s.Cells(Rows.Count, "A").End(xlUp).Row
    Dim sName As String, sPath As String
    Application.ScreenUpdating = False
    sPath = ThisWorkbook.Path
    For i = 7 To lr
        t.Copy After:=Worksheets(Worksheets.Count)
        ActiveSheet.Name = s.Range("C" & i)
        ActiveSheet.Range("F3") = s.Range("A" & i)
        ActiveSheet.Range("F4") = s.Range("C" & i)
        ActiveSheet.Range("F19") = s.Range("B" & i)
            ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        sPath & "\" & ActiveSheet.Name & ".pdf", Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
        True
    Next i
    Application.ScreenUpdating = True
End Sub
 

Attachments

  • Workbook1.xlsx
    14.4 KB · Views: 11
Perhaps this:


Code: 
Option Explicit

Sub createInvoice()
    Dim s As Worksheet, t As Worksheet, ws As Worksheet
    Set s = Sheets("invoice details")
    Set t = Sheets("template")
    Dim i As Long, lr As Long
    lr = s.Cells(Rows.Count, "A").End(xlUp).Row
    Dim sName As String, sPath As String
    Application.ScreenUpdating = False
    sPath = ThisWorkbook.Path
    For i = 7 To lr
    If Range("D" & i) <> "Done" Then
        t.Copy After:=Worksheets(Worksheets.Count)
        ActiveSheet.Name = s.Range("C" & i)
        ActiveSheet.Range("F3") = s.Range("A" & i)
        ActiveSheet.Range("F4") = s.Range("C" & i)
        ActiveSheet.Range("F19") = s.Range("B" & i)
            ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        sPath & "\" & ActiveSheet.Name & ".pdf", Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
        True
    s.Range("D" & i) = "Done"
    End If
    Next i
    Application.ScreenUpdating = True
End Sub
 
Thanks !

Two issues with this code:

1. It create invoice for all the rows. I want invoice only for rows filtered for "customer A" and

2. worksheets containing invoices need to be created in a separate workbook
Code: 
Option Explicit

Sub createInvoice()
    Dim s As Worksheet, t As Worksheet, ws As Worksheet
    Set s = Sheets("invoice details")
    Set t = Sheets("template")
    Dim i As Long, lr As Long
    lr = s.Cells(Rows.Count, "A").End(xlUp).Row
    Dim sName As String, sPath As String
    Application.ScreenUpdating = False
    sPath = ThisWorkbook.Path
    For i = 6 To lr
    If Range("E" & i) <> "Done" Then
        t.Copy After:=Worksheets(Worksheets.Count)
        ActiveSheet.Name = s.Range("C" & i)
        ActiveSheet.Range("F3") = s.Range("A" & i)
        ActiveSheet.Range("F4") = s.Range("C" & i)
        ActiveSheet.Range("F19") = s.Range("B" & i)
            ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        sPath & "\" & ActiveSheet.Name & ".pdf", Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
        True
    s.Range("E" & i) = "Done"
    End If
    Next i
    Application.ScreenUpdating = True
End Sub
 
If you already have the invoices in a .PDF then keeping a copy in another file is redundant and only adds to storage clutter. Why do you need this?

Attached is amended code to only create invoice for those not "Done" and Customer is "A"

Code: 
Option Explicit


Sub createInvoice()
    Dim s As Worksheet, t As Worksheet, ws As Worksheet
    Set s = Sheets("invoice details")
    Set t = Sheets("template")
    Dim i As Long, lr As Long
    lr = s.Cells(Rows.Count, "A").End(xlUp).Row
    Dim sName As String, sPath As String
    Application.ScreenUpdating = False
    sPath = ThisWorkbook.Path
    For i = 6 To lr
    If Range("E" & i) <> "Done" Then
    If Range("D" & i) = "A" Then
        t.Copy After:=Worksheets(Worksheets.Count)
        ActiveSheet.Name = s.Range("C" & i)
        ActiveSheet.Range("F3") = s.Range("A" & i)
        ActiveSheet.Range("F4") = s.Range("C" & i)
        ActiveSheet.Range("F19") = s.Range("B" & i)
            ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        sPath & "\" & ActiveSheet.Name & ".pdf", Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
        True
    s.Range("E" & i) = "Done"
    End If
    End If
    Next i
    Application.ScreenUpdating = True
End Sub
 
One iteration to add the excel files to a separate file called Invoices.xlsx. Note this file must be open when you run this macro. Make sure to run the macro from the source file and not the target file as it will not work under those circumstances.
Code: 
Option Explicit

Sub createInvoice()
    Dim s As Worksheet, t As Worksheet, ws As Worksheet
    Dim target As Workbook
    Set target = Workbooks("Invoices.xlsx") 'make sure this file is open
    Set s = Sheets("invoice details")
    Set t = Sheets("template")
    Dim i As Long, lr As Long
    On Error Resume Next
    lr = s.Cells(Rows.Count, "A").End(xlUp).Row
    Dim sName As String, sPath As String
    Application.ScreenUpdating = False
    sPath = ThisWorkbook.Path
    For i = 6 To lr
    If Range("E" & i) <> "Done" And Range("D" & i) = "A" Then
    'If Range("D" & i) = "A" Then
        target.Activate
        t.Copy After:=target.Sheets(target.Worksheets.Count)
        ActiveSheet.Name = s.Range("C" & i)
        ActiveSheet.Range("F3") = s.Range("A" & i)
        ActiveSheet.Range("F4") = s.Range("C" & i)
        ActiveSheet.Range("F19") = s.Range("B" & i)
            ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        sPath & "\" & ActiveSheet.Name & ".pdf", Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
        True
    s.Range("E" & i) = "Done"
    End If
    s.Activate
    Next i
    Application.ScreenUpdating = True
End Sub
 
Thank you so much Sidman, you did an awesome job,
Can you please make one more slight change,
I dont want the converted sheets of pdf to open in the end.

Thanks,
 
You must log in or register to reply here.
Back
Top