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

 

[frank_AL]

Bjorn,

This can be handled with a macro.
Answer the questions below and I will build one for you.

1. Name of worksheet with list of customer information (raw data).
2. Name of worksheet that is the template.

For each field on the template that gets populated with data from the raw data worksheet identify the column that data point comes from on the raw data worksheet.

For example:

Template Raw Data
B2 B
D4 E

 

[sureshxcell]

frank_AL,

I am looking to learn the same

Sheet1 = Rawdata
Sheet2 = Template

Linked cells from Rawdata to Template
RawData Template
Column"A" Range"B5"
Column"B" Rabge"F1"

Thanks

 

[frank_AL]

One other question. Do you want to create/send an email out to these customers with the PDF file that is created? If so, is there a column on the raw data worksheet that has their email address?

Also, let me know the folder where you want to save these PDF files.

 

[sureshxcell]

No, I don't like to send email. I would like to save the pdf files C:\Test folder.

Could you please add additional column in "raw data" which shows the status " Whether the invoice is saved as pdf or not?

Thanks

 

[frank_AL]

I can do that. Just tell what column you want to put it in.

 

[sureshxcell]

Column"F"

Thank you

 

[frank_AL]

sureshxcel,

I was answering questions over the weekend using my phone and did not pay attention that you were not the person that started this forum post. However, since you provided answers to my questions this code module should work for you.

Bjorn,

Just provide answers to the questions and I can easily modify what I have developed to support your needs as well.

Frank_al

Option Explicit


Sub CopyToTemplate()


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


Set cfws = Worksheets("Raw Data")
Set ctws = Worksheets("Template")


lastrow = cfws.Cells(cfws.Rows.Count, "B").End(xlUp).Row
fileloc = "C:\Test\"


For i = 2 To lastrow
    filename = "File " & i
    ctws.Range("B5").Value = cfws.Range("A" & i).Value
    ctws.Range("F1").Value = cfws.Range("B" & i).Value
    Fname = fileloc & filename & ".pdf"
    With ctws
        .ExportAsFixedFormat Type:=xlTypePDF, filename:=Fname
    End With
    cfws.Range("F" & i).Value = Date
Next i
        
End Sub

 

[sureshxcell]

Thanks for the code. I made few changes and I want to modify the loop structure in the following situation

1. How to run the macro only for the cells in the "Raw-Data" for which "PDF Invoice is not exported .

2. Sometimes Invoice number exists but the Invoice amount will be "". I want to skip such cells for which the Invoice amount is ""

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 = "" Then
Set ctws = Worksheets("Tax Invoice-2")
Else
Set ctws = Worksheets("Tax Invoice-1")
End If
    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
    
Next i
        
End Sub

 

[frank_AL]

suresh,

I modified your code to skip any row where Column F = Blank (vbNullString)

Let me know what column the Invoice number is in and I can add that to the code to skip if that column is blank.