Friday, November 5, 2021

Home Free Excel Templates [FREE Invoice Generator Template] Save Excel Invoice as PDF

[FREE Invoice Generator Template] Save Excel Invoice as PDF

November 05, 2021 Free Excel Templates,

[FREE Invoice Generator Template] Save Excel Invoice as PDF

I recently had to get myself registered under Goods and Services Tax (GST) so that I can file for GST on my earnings.

Once you have a GST number, you need to file your tax return every month (in India).

So every month, my Charted Accountant would reach out to me asking for sales invoices so that he can file for the GST.

In my case, there only a handful of invoices to be created, as I have only a few sources of income.

However, since this is additional work, I wanted to get this done as quickly as possible.

So to minimize my effort, I created an Invoice Generator template in Excel that allows me to have all the data in one place, and then it automatically creates PDF invoices for all the data points.

As you can see below, all I need to do is double-click on the client’s name (in column B), and it would instantly create and save the invoice in the PDF format in the specified folder.

You can also modify the invoice template to suit your company’s format (which would require you to change the VBA code a little – explained below).

It creates an invoice as shown below:

This Tutorial Covers:

How does this Invoice Generator Template Works?

In this invoice template, there are two worksheets:

Details – This is where you need to specify the details of the sale/transaction. To keep everything together, I have created one row for each record. All the details of a transaction are recorded in the row.
Invoice Template – This is a placeholder template of the invoice where some fields are left empty. I need to generate a separate invoice for all the sale records and the details for each invoice is picked up from the Details worksheet.

I also have a folder on my desktop with the name ‘Invoice PDFs’. This is the folder where the newly created PDF invoices are saved.

Now let’s see how this works:

You need to double-click on the client’s name (highlighted in orange in the Details sheet).

That’s it!

When you double-click on the client name, it kickstarts the VBA magic in the back end and the following things happen:

Details for the client and the sale transaction are picked up and the invoice template sheet is populated with these details.
A new workbook is created that has the details of the selected client (on which you double-clicked).
This workbook is saved as a PDF in the Invoice PDF folder.
The new workbook is closed without saving.

In case there are any changes in the invoice details, you can double-click on the client name again, and a new invoice will be created (and this will overwrite the old one).

Note that the names of the invoices are based on the month and the invoice number.

For example, an invoice with the date 15-04-2019 and the invoice number as 1 would be saved with the name April 2019_1.pdf. This helps in keeping track of the invoices in case you have too many.

You can download the Invoice Generator Template by clicking on the button below:

Modifying the Invoice Generator Template

I created this invoice template with a format that I needed for my GST filings.

If you need a different format, you’ll have to edit the template and then adjust the backend VBA code.

Let me first show you the code and explain how it works:

Sub CreateInvoice(RowNum As Integer)
Application.ScreenUpdating = False
Dim wb As Workbook
Dim sh As Worksheet
With shInvoiceTemplate
.Range("D10") = shDetails.Range("A" & RowNum)
.Range("D11") = shDetails.Range("B" & RowNum)
.Range("D12") = shDetails.Range("C" & RowNum)
.Range("B15") = shDetails.Range("D" & RowNum)
.Range("D15") = shDetails.Range("F" & RowNum)
.Range("D16") = shDetails.Range("G" & RowNum)
.Range("D18") = shDetails.Range("E" & RowNum)
End With
FPath = "C:\Users\sumit\Desktop\Invoice PDFs"
Fname = Format(shInvoiceTemplate.Range("D10"), "mmmm yyyy") _
& "_" & shInvoiceTemplate.Range("D12")
shInvoiceTemplate.Copy
ActiveSheet.Name = "InvTemp"
Set wb = ActiveWorkbook
Set sh = ActiveSheet
sh.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
FPath & "\" & Fname, Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False
wb.Close SaveChanges:=False
ThisWorkbook.Activate
Application.ScreenUpdating = True
End Sub

The above code is what copies the details of a transaction, fills the invoice placeholder template with those details, creates a new workbook, and save the new workbook as a PDF in the specified folder.

If you have a different template or a different folder location, you need to modify the below-highlighted parts of the code:

The first highlighted section is what takes the details from the Details sheet and populates the invoice template. If you decide to modify the invoice template, you need to make sure you are picking the right details by modifying this part of the code.
This line specifies the folder location. In my case, it was a folder on my Desktop. You can specify the address of the folder where you want the invoices to be saved.

Note that I have renamed the worksheet code name to ‘shDetails’. I have done this so that I can use the name – shDetails – in my code and it would continue to work even if you change the name of the sheets in the worksheet.

If you want to learn more about the sheet name and the code name, have a look at this (check out the section on using the Worksheet code name).

Where is the code in the workbook?The code is placed in the back-end of the Excel workbook in a module.

To access the code, follow the below steps:

Click the Developer tab.
Click the Visual Basic option. This will open the VB Editor window.
In the Visual Basic editor, double-click on the Module to open its code window. You’ll find the code mentioned above.

In case you’re creating a template yourself, you may not find the Module in a new workbook. You need to right-click on any of the workbook objects, go to Insert, and then click on Module. This will insert a new module.

Making the Double-click functionality to Work

The above code does all the heavy lifting, but you need to connect it to the double-click event.

This means that the above VBA macro code needs to run whenever someone double-clicks on the filled cells in the client name column.

This can be done by inserting the following code in the worksheet code window:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Cells <> "" And Target.Column = 2 Then
Cancel = True
Call CreateInvoice(Target.Row)
End If
End Sub

Here are the steps to insert this code in the worksheet backend:

Right-click on the ‘Details’ worksheet tab
Click on the ‘View Code’ option.
Copy and paste the above code into the code window that appears.

The above code does the following things:

Checks if the cell that has been double-clicked has the client details or not. It uses the IF statement to check and run the code only if the cell is not empty and in Column B.
If both the specified criteria are met, it disables double-click functionality (which is to get into the edit mode) and calls the ‘CreateInvoice’ subroutine, which is stored in the Module. It also passes the row number value to the subroutine. For example, if I double-click on the client name in the third row, it will pass 3 as the value to the CreateInvoice subroutine.
Once the ‘CreateInvoice’ subroutine is executed – which creates the PDF of the invoice – it ends.

Click here to download the Invoice Generator Template file.

Saving the Invoice Template as Excel (instead of PDF)

If you want to save the invoice templates as Excel files and not as PDFs, you can use the below code instead:

Sub CreateInvoice(RowNum As Integer)
Application.ScreenUpdating = False
Dim wb As Workbook
Dim sh As Worksheet
With shInvoiceTemplate
.Range("D10") = shDetails.Range("A" & RowNum)
.Range("D11") = shDetails.Range("B" & RowNum)
.Range("D12") = shDetails.Range("C" & RowNum)
.Range("B15") = shDetails.Range("D" & RowNum)
.Range("D15") = shDetails.Range("F" & RowNum)
.Range("D16") = shDetails.Range("G" & RowNum)
.Range("D18") = shDetails.Range("E" & RowNum)
End With
FPath = "C:\Users\sumit\Desktop\Invoice PDFs"
Fname = Format(shInvoiceTemplate.Range("D10"), "mmmm yyyy") _
& "_" & shInvoiceTemplate.Range("D12")
shInvoiceTemplate.Copy
ActiveSheet.Name = "InvTemp"
Set wb = ActiveWorkbook
Set sh = ActiveSheet
sh.Name = Fname
wb.SaveAs Filename:=FPath & "\" & Fname
wb.Close SaveChanges:=False
ThisWorkbook.Activate
Application.ScreenUpdating = True
End Sub

The above code saves the invoice as an Excel workbook with the same naming convention. The worksheet in the workbook that contains the filled invoice in each saved workbook is also named the same.

You May Also Like the Following Excel Tutorials Useful: