Using Invoice Template to Automate Invoice Creation and Printing with VBA


Somebody rightly said: All cash flow starts with an invoice. We can use an invoice template to automate the invoice creation and printing with VBA.

  • First we download an invoice of our choice right from within MS Excel when we start the program or software
  • Next we save it as a normal file ‘.xlsx’ file
  • Now we study the various elements of the invoice which we can fill out automatically using VBA
  • We copy the elements headings and paste them in our Excel worksheet
  • Next we enter the data as we make the sales
  • We place a command button on our worksheet by clicking on the developer tab, selecting the insert option and from the ActiveX controls we click on the ‘command button’ icon. We can now click and drag to draw a command button at a place of our choice on the Excel worksheet. We right-click on the command button, select properties and from the new window that pops up we select the text box next to the item caption and insert our text, for example, print invoices
  • We double click on the command button in the deign-mode and between the two lines of code that are shown in the Visual Basic for Applications Editor (VBE) we start writing our own code
  • First we define the different variables that we will need
  • Now we get the data from the worksheet cells into our variables
  • We now assign the variables to the relevant worksheet cells in the invoice file
  • Since the variables already have values from the worksheet stored in them, these values are assigned to the invoice worksheet cells.
  • We have mapped a few interesting items. The others you can do as an exercise to firm up your understanding of VBA
  • Once the data is entered into the invoice worksheet cells automatically we save the file in a specific manner using the path to the directory, the invoice number, customername, etc
  • Then we make the file read only
  • Also we place automatically a text value called ‘done’ under the header in our Excel data worksheet
  • This ensures that these files will not be saved and printed again by our program or macro

The complete VBA code is given below:

Private Sub CommandButton1_Click()

‘First we define a few variables to capture data from our Excel worksheet
Dim customername As String
Dim customeraddress As String
Dim invoicenumber As Long
Dim r As Long
Dim mydate As String
Dim path As String
Dim myfilename As String

‘our last row of data in the worksheet is defined
lastrow = Sheets(“CustomerDetails”).Range(“A” & Rows.Count).End(xlUp).row

‘ we start at row 2 since the first row contains headers
r = 2

‘Looping process starts
For r = 2 To lastrow

‘If the value under the header ‘Note’ is done then the data in the row is not processed and jump to the label nextrow

If Cells(r, 17).Value = “done” Then GoTo nextrow

‘we map excel worksheet data to the variables

customername = Sheets(“CustomerDetails”).Cells(r, 1).Value
customeraddress = Sheets(“CustomerDetails”).Cells(r, 2).Value
invoicenumber = Sheets(“CustomerDetails”).Cells(r, 6).Value
quantity = Sheets(“CustomerDetails”).Cells(r, 18).Value
Description = Sheets(“CustomerDetails”).Cells(r, 19).Value
UnitPrice = Sheets(“CustomerDetails”).Cells(r, 20).Value
SalesTaxRate = Sheets(“CustomerDetails”).Cells(r, 16).Value

Cells(r, 17).Value = “done”
Application.DisplayAlerts = False
Workbooks.Open (“C:\invoices\BasicInvoice.xlsx”)
ActiveWorkbook.Sheets(“BasicInvoice”).Activate

‘ now we map the variables to the invoice worksheet data
ActiveWorkbook.Sheets(“BasicInvoice”).Range(“I8”).Value = invoicenumber
ActiveWorkbook.Sheets(“BasicInvoice”).Range(“C8”).Value = customername
ActiveWorkbook.Sheets(“BasicInvoice”).Range(“C9”).Value = customeraddress
ActiveWorkbook.Sheets(“BasicInvoice”).Range(“B21”).Value = quantity
ActiveWorkbook.Sheets(“BasicInvoice”).Range(“C21”).Value = Description
ActiveWorkbook.Sheets(“BasicInvoice”).Range(“H21”).Value = UnitPrice
ActiveWorkbook.Sheets(“BasicInvoice”).Range(“D18”).Value = SalesTaxRate

path = “C:\invoices\”
mydate = Date
mydate = Format(mydate, “mm_dd_yyyy”)

‘File is saved as a read-only file
ActiveWorkbook.SaveAs Filename:=path & invoicenumber & “-” & customername & “-” & mydate & “.xlsx”
myfilename = ActiveWorkbook.FullName
SetAttr myfilename, vbReadOnly
Application.DisplayAlerts = True
ActiveWorkbook.PrintOut copies:=1

‘We close the active workbook. We don’t need to save it because we just saved it!
ActiveWorkbook.Close SaveChanges:=False

‘our label next row. Labels have a colon after their name

nextrow:

Next r

End Sub

View the training video. If you are not able to view the data conveniently then use the full-screen mode.

 


Watch the video on YouTube.

14 thoughts on “Using Invoice Template to Automate Invoice Creation and Printing with VBA

  1. Ori

    Hello- I found your youtube posts and found them very helpful. I have a quick question and was wondering if you could email me (I can’t find your contact information here).

    Thanks!

    Reply
  2. Srenivasank

    Hi sir,

    First, I would like to say thank you so much ..! for your innovative ideas for teaching this..

    Can we do the below scenario through VBA? is it possible ?

    I need to create a macro for employee working hours calculation report.

    step 1: When i start the system the macro will popup up commend button automatically & ask please enter Employee name & Employee ID ? once done this it will calculate working hours..

    step 2: In between time if we lock the system it will consider as a break time( 10 min & 20 min=30 min

    step 4: when we are going to shut-down the system. it will consider as end time. message box will popup up your time is end.

    step 5; Finally we need to get total working hours.

    Ex:
    Employee name / Employee ID / Start time & date / End time & Date / break hours / total working hours

    xxxx /546 / 5/20/14 12:00 AM / 5/20/14 4:00 AM / 10 min+ 5 min+15 min = 30min/ 3:30 hrs

    Note: No one can not edit the system updated timings by manually.
    if there are 10 employees, all the 10 employees working hours will be appear in the single excel sheet.

    finally the file will save the data in particular location automatically.
    Can you help me this ?

    Regards,
    Srenivas

    Reply
  3. reuben

    Hi Dinesh,

    thank you for a wonderful video. I have tweaked the code and used it in my work. But then I am facing issues which I would like your help with. Could you please let me know if I can contact you in anyway.

    Thank you.

    Reply
  4. Hema

    Dear Sir,

    This is awesome, is there a way where we can transfer the excel content in Word and save the invoices individually?

    It is the same way just in place of transferring data from excel to excel we have to transfer the data to word document.

    Please suggest and Thank you so much for all the knowledge.

    Regards,
    Hema

    Reply
  5. Iain

    Hi Dinesh

    Is there a way with your code above to reverse the process?

    Once I have the created the invoice and have saved the data into a second spreadsheet for record keeping, how can I reverse it all and create the invoice again (say if the customer requests another copy in a months time)

    I’d like the user to press a button, this opens up a input box where they can insert the invoice number or their name and the user is presented with the results, they then choose which invoice they wish and then it repopulates the invoice with all the original data and prints again.

    Thoughts?

    Reply
  6. Deep Khatri

    Hi Dines
    am new to VBA. I have been watching your video and learn a lot stuff. I have created useform and database using your codes. they work to the prefection. My next time is how can i automatically consolidate the data. For example My vbais used for a farmer, He has his produce that he distributes to different seller, He want to be able to consolidate the data and print invoice and shipping label. I am having difficulty creating the codes for this. Is there anyway you can help or point me in the right direction. I can also send you the the file that i have so far.
    Thank you
    Deep

    Reply
  7. Michelle

    Hi Sir,
    Thanks for the lesson, very helpful.
    I have some trouble of using looping in my exercise. It looks like there is bug in my spreadsheet. Would you mind helping! thanks!!!

    ‘our last row of data in the worksheet is defined
    lastrow = Sheets(“CustomerDetails”).Range(“A” & Rows.Count).End(xlUp).row

    ‘ we start at row 2 since the first row contains headers
    r = 2

    ‘Looping process starts
    For r = 2 To lastrow

    ‘If the value under the header ‘Note’ is done then the data in the row is not processed and jump to the label nextrow

    If Cells(r, 17).Value = “done” Then GoTo nextrow

    Reply
    1. sujit chaudhary

      i have some tourble
      plaese solve this problem with example

      our last row of data in the worksheet is defined
      lastrow = Sheets(“CustomerDetails”).Range(“A” & Rows.Count).End(xlUp).row

      ‘ we start at row 2 since the first row contains headers
      r = 2

      ‘Looping process starts
      For r = 2 To lastrow

      ‘If the value under the header ‘Note’ is done then the data in the row is not processed and jump to the label nextrow

      If Cells(r, 17).Value = “done” Then GoTo nextrow

      Reply
  8. Ankur

    Can you please help us, we are getting error while performing the same steps.

    can you please me the sample sheet?

    Reply
  9. Jorge Meneses

    Hi sir, I think your information is pretty much to the point. I used this program and everything works fine, except that the numbers in the report are coming out as $5,891.00 instead of $5,890.65. I have tried changing the format with different ways, but to no avail. Do you have any ideas? Thank you.

    Reply
  10. Jorge Meneses

    Thanks a lot again (I should had said this before) for taking the time to explain these concepts to us would-be-programmers. I really enjoyed doing these things and love when they work properly and make the job easier. Thank you.

    Reply
  11. Vikram Mundra

    Hi ! I have a worksheet named invoice which is linked to another worksheet in the same workbook. I want to add a print button to the invoice sheet so that on clicking the button, a new workbook is created with just the invoice sheet containing only data and formatting ( no links ). The new workbook should be saved at the same location as the original file. The new workbook should be named from a cell reference in the original invoice worksheet. Please help me out as I have zero knowledge of macros, etc.,
    Thanks. Vikram

    Reply

Leave a Reply

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