Automatically create PDF-invoices with Excel

[bjornbjorn]

Hi,

I am looking for a way to automatically create PDF invoices using Excel. I have a standardized excel-sheet with customer information and I'd like to be able to push a button and the customer information to be gathered into an invoice template that I've created. I.e. for each line of customer information a new PDF-invoice should be created. Does anyone have any ideas how to create this? I assume I need to create a macro to be able to create one invoice per customer.

Any help is appreciated.

Thanks,
Björn

 

[sureshxcell]

Hi Frank,

Invoice number is in Column"B". I don't want to skip if Column"B" is blank.

 

[frank_AL]

I'm sorry you said you wanted to skip if the Invoice amount is blank. What column is the Invoice amount in?

 

[sureshxcell]

Invoice amount is in Column"H"

Also I want to export only new records

 

[frank_AL]

suresh,

code has been updated to not include a row if either Column F or H are blank.
For your last request to only export new records you will need to let me know how to determine if a record is new. Is there a date field or something like that I could key on to make that determination?

Option Explicit


Sub InvoiceGeneration()


Dim cfws, ctws As Worksheet
Dim lastrow, i As Long
Dim fileloc, filename, Fname As String


Set cfws = Worksheets("Raw-Data")
lastrow = cfws.Cells(cfws.Rows.Count, "B").End(xlUp).Row + 1
fileloc = "C:\Test\"


For i = 1 To lastrow
    If cfws.Range("L" & i).Value = vbNullString Then
        Set ctws = Worksheets("Tax Invoice-2")
    Else
        Set ctws = Worksheets("Tax Invoice-1")
    End If
    If cfws.Range("F" & i).Value <> vbNullString And cfws.Range("H" & i).Value <> vbNullString Then
        filename = "Invoice -" & cfws.Range("B" & i).Value
        ctws.Range("A9").Value = "Invoice No : " & cfws.Range("B" & i).Value
        ctws.Range("B23").Value = cfws.Range("H" & i).Value
        Fname = fileloc & filename & ".pdf"
        cfws.Hyperlinks.Add Anchor:=cfws.Range("M" & i), Address:=Fname, TextToDisplay:=filename
            
        With ctws
            .ExportAsFixedFormat Type:=xlTypePDF, filename:=Fname
        End With
    End If
    
Next i
        
End Sub

 

[sureshxcell]

frank_AL,

The below line creates hyperlinks in raw-data sheet. so, is it possible to run the macro for the cells in raw-data sheet which don't have hyperlinks( i.e new records)?

cfws.Hyperlinks.Add Anchor:=cfws.Range("M" & i), Address:=Fname, TextToDisplay:=filename

Thanks

 

[frank_AL]

suresh,

The code now works on when Column F and H are not blank and M is blank.

Option Explicit


Sub InvoiceGeneration()


Dim cfws, ctws As Worksheet
Dim lastrow, i As Long
Dim fileloc, filename, Fname As String


Set cfws = Worksheets("Raw-Data")
lastrow = cfws.Cells(cfws.Rows.Count, "B").End(xlUp).Row + 1
fileloc = "C:\Test\"


For i = 1 To lastrow
    If cfws.Range("L" & i).Value = vbNullString Then
        Set ctws = Worksheets("Tax Invoice-2")
    Else
        Set ctws = Worksheets("Tax Invoice-1")
    End If
    If cfws.Range("F" & i).Value <> vbNullString And cfws.Range("H" & i).Value <> vbNullString And cfws.Range("M" & i) = vbNullString Then
        filename = "Invoice -" & cfws.Range("B" & i).Value
        ctws.Range("A9").Value = "Invoice No : " & cfws.Range("B" & i).Value
        ctws.Range("B23").Value = cfws.Range("H" & i).Value
        Fname = fileloc & filename & ".pdf"
        cfws.Hyperlinks.Add Anchor:=cfws.Range("M" & i), Address:=Fname, TextToDisplay:=filename
            
        With ctws
            .ExportAsFixedFormat Type:=xlTypePDF, filename:=Fname
        End With
    End If
    
Next i
        
End Sub

 

[sureshxcell]

Thank you for the kind assistance.

 

[Charlie228]

I've been using something similar to this to generate PDFs of invoices for my customers but I'd like to add a step after the pdf is created where the pdf is sent as an email attachment to an address also on the data worksheet. Can anyone help with this please? Thanks

 

[mza3025]

hell im new here, if i implement this into my raw data + invoice, i have 200+ rows of enteries that i need invoices for, will this create one for each one? or would i need to do this manually?