Automate Invoice Report Generation Using Excel VBA


A user would like to automatically generate a report of her invoice in another Excel worksheet once she has finished entering all the relevant data in the invoice template. The invoice template performs all the calculations like totals automatically using in-built Excel formulas and functions. We can automate the invoice report generation using Excel VBA. Once the user runs the Excel VBA code a report is generated in sheet2 and the file is saved in two formats PDF and XLSX. Now the user can make new entries in the same invoice template to create a new invoice and run the VBA code. In this manner he not only generates the report in Sheet2 but can also archive the invoices in PDF and XLSX formats. In sheet2 he generates a hyperlink to the PDF file which he can view with a single click.

View the training video below to understand the complete process of automating the invoice report generation with Excel VBA:

View the video on YouTube.


Download a sample file by clicking on the Excel icon:


Here’s the complete VBA code:
Sub InvoiceReport()
Dim myFile As String, lastRow As Long
myFile = “C:\invoices\” & Sheets(“Sheet1”).Range(“B5”) & “_” & Sheets(“Sheet1”).Range(“F1”) & Format(Now(), “yyyy-mm-dd”) & “.pdf”
lastRow = Sheets(“Sheet2”).UsedRange.SpecialCells(xlCellTypeLastCell).Row + 1
‘Transfer data to sheet2
Sheets(“Sheet2”).Cells(lastRow, 1) = Sheets(“Sheet1”).Range(“B5”)
Sheets(“Sheet2”).Cells(lastRow, 2) = Sheets(“Sheet1”).Range(“F1”)
Sheets(“Sheet2”).Cells(lastRow, 3) = Sheets(“sheet1”).Range(“I36”)
Sheets(“Sheet2”).Cells(lastRow, 4) = Now
Sheets(“Sheet2”).Hyperlinks.Add Anchor:=Sheets(“Sheet2”).Cells(lastRow, 5), Address:=myFile, TextToDisplay:=myFile
‘Create invoice in PDF format
Sheets(“sheet1”).ExportAsFixedFormat Type:=xlTypePDF, Filename:=myFile
Application.DisplayAlerts = False
‘create invoice in XLSX format
ActiveWorkbook.SaveAs “C:\invoices\” & Sheets(“Sheet1”).Range(“B5”) & “_” & Sheets(“Sheet1”).Range(“F1”) & “_” & Format(Now(), “yyyy-mm-dd”) & “.xlsx”, FileFormat:=51
‘ActiveWorkbook.Close
Application.DisplayAlerts = True

End Sub

Further reading:
Use VBA SaveAs in Excel 2007-2013

8 thoughts on “Automate Invoice Report Generation Using Excel VBA

  1. Don Burhenne

    hello sir, i try in my best englisch to ask my question
    when i make een xlsx file then excel write a new file en override my original i want one sheet of my workbook in a pdf and the same also in xlsx is that posable.

    I will thank you for the great lessons this is the best way to learn VBA thank again

    Reply
  2. Dung Nguyen

    Dear Sir, thank you so much for the tutorial. It helps me a lot with my current project. I just have a little issue I hope you can help me solve it. After I was done, saved and closed the macro, all the information recorded was deleted from the original application. It was saved in the last file I worked on but there was no macro in that file. I had to go back to the original macro if I wanted to create another invoice, but all the previous information and the links were lost. Can you please advise. Thanks

    Reply
  3. Hiren

    The tutorial is extremely helpful. I am trying to replicate the same functionality on Google Spreadsheet. Is it possible? Will you be able to post what script changes will be necessary to have the similar report created on Google (with a PDF potentially created on Google drive itself)?

    Reply
  4. Anne Bateman

    I have watched several of your videos and each one completes a specific task I am trying to replicate. However, I am in need of combining multiple videos/tasks/code to complete my desired project. Are you able to assist me in combining multiple Modules?

    I have several invoices in one folder (each invoice is a separate file). I would like to create a master file that contains specific information from each saved invoice (name, service date, materials used, etc.). I believe this will require a module to loop through each file saved in the directory and then select certain cells within the invoice and paste those in the master file.

    Thanks in advance!
    Anne

    Reply
  5. sandeep jha

    Sub InvoiceReport()
    Dim myFile As String, lastRow As Long
    myFile = “C:\invoices\” & Sheets(“Sheet1”).Range(“B5”) & “_” & Sheets(“Sheet1”).Range(“F1”) & Format(Now(), “yyyy-mm-dd”) & “.pdf”
    lastRow = Sheets(“Sheet2”).UsedRange.SpecialCells(xlCellTypeLastCell).Row + 1
    ‘Transfer data to sheet2
    Sheets(“Sheet2”).Cells(lastRow, 1) = Sheets(“Sheet1”).Range(“B5”)
    Sheets(“Sheet2”).Cells(lastRow, 2) = Sheets(“Sheet1”).Range(“F1”)
    Sheets(“Sheet2”).Cells(lastRow, 3) = Sheets(“sheet1”).Range(“I36”)
    Sheets(“Sheet2”).Cells(lastRow, 4) = Now
    Sheets(“Sheet2”).Hyperlinks.Add Anchor:=Sheets(“Sheet2”).Cells(lastRow, 5), Address:=myFile, TextToDisplay:=myFile
    ‘Create invoice in PDF format
    Array([val])Sheets(“sheet1”).ExportAsFixedFormat Type:=xlTypePDF, Filename:=myFile
    Application.DisplayAlerts = False
    ‘create invoice in XLSX format
    ActiveWorkbook.SaveAs “C:\invoices\” & Sheets(“Sheet1”).Range(“B5”) & “_” & Sheets(“Sheet1”).Range(“F1”) & “_” & Format(Now(), “yyyy-mm-dd”) & “.xlsx”, FileFormat:=51
    ‘ActiveWorkbook.Close
    Application.DisplayAlerts = True

    End Sub
    ***************************************
    File Run karne per Error aa raha hai aapki hi excel and Script me how to Solved problem
    Note: Check Error Msg
    Run-time Error ‘5’:
    Invalid procedure call or argument
    ****************************************
    was problem this file not save PDF plz solved this problem today. thanks.

    Reply
  6. CHOKSI

    myFile = “C:\invoices\” & Sheets(“Sheet1”).Range(“B5”) & “_” & Sheets(“Sheet1”).Range(“F1”) & Format(Now(), “yyyy-mm-dd”) & “.pdf”

    PLEASE SOLE THIS ERROR

    Reply
  7. Naeem

    Dear sir
    The same problem as sandeep jha i have faced when i run it and the file showing in sheet 2 but could not open in pdf format
    Every thing going well such as total amount, transfer data in sheet 2 but the error 5 is coming when i did run

    Would you please guide how i can solve

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *