We help IT Professionals succeed at work.

Use VB Script to generate invoices in Excel format automatically

cybersi
cybersi asked
on
1,116 Views
Last Modified: 2014-05-13
Hi All

This post is probably the most testing post I have ever done on this site so I'm keeping my fingers crossed that there is someone with the knowledge that can help. (please)

Right If I start with my requirement:

I have a very simple excel spread sheet that I would like to achieve the following:

1. Create quarterly invoices automatically in a separate excel spread sheet that are saved to the right customer directory.

2. Print the newly created invoices automatically (2 copies of each) quarterly (not sure if this would be done via a scheduled task)

3.  There are 2 different invoices a)management fee b)company secretary fee. Each will be a different invoice however no all companies have the company secretary invoice.

I have attached a copy of the data source template and a copy of the template for the invoice.

I will be completely honest I know absolutely zero where it comes to VB scripts and Zero where it comes to excel macros and how they work.  It may be a case that another technology would be better but hey I guess that's why I'm asking the question.
Merlin-Management-fees.xlsx
Comment
Watch Question

Author

Commented:
Sorry here's the invoice template.

Thanks

Simon
New-merlin-invoice-template-USE-.xls
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Hi, I'd be willing to help, but it may be a slow process while I try to understand what you have.  The first question I have is, how can we create quarterly invoices when there are no dates in the fees sheet?  Also, where is the distinction between management fee and secretary fee?

Regards,

Rob.

Author

Commented:
Hi Rob

So chuffed you have taken this on thanks.  To be honest I was not sure on the right direction.  Looking at the bigger picture and the lack of my knowledge I was not sure if a  script would have that function.  I guess I could insert 8 additional column's within the data source 4 start dates and 4 end dates of all 4 quarters?

I guess the process could be split and have 2 procedures that are in fact the same.  Just split management fee and secretary fee? So I would have to xlsx files or CSV not sure which would be better?  

What do you suggest?

Regards

Simon
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Don't think about this as being impossible, because from the nice simple layout of your data, this is definitely possible.  What I need to be able to understand how to code it, is to understand how you would manually collate the data to give you what you need.

So the spreadsheet you have posted is titled Merlin-management-fees.xlsx.  I assume this means there's also a Merlin-secretary-fees.xlsx?  If so, it would be easy enough to write a script to open both workbooks, and combine the data in them into your invoice template, provided that the customer names were identical in both.

When you would do this manually, what are the dates that define your quarters?  Do you just get all the spreadsheets in a folder, created in a three month period, and collate those (because there are no dates within the files themselves)?

If so, then we can work on those assumptions, and build something to suit.  We just need to define some business rules as to how you would go about doing this, and we can begin to automate it.

Try to take me through your process, and I'll turn that into some code for you.

Regards,

Rob.

Author

Commented:
OK let’s forget secretary fees completely as I have just been told they are yearly and not quarterly and do not apply to all clients.

Each customer has a dedicated share on the network.  Each customer share has the same directory structure as the next.
All invoices are located in the customers invoice directory. Management fees have their own directory in the invoice directory.

Very happy for it to be a manual process we run 4 times a year.  Please excuse my ignorance but the script would it use what I call static data or would the script ask for the date range or the starting invoice number for example?

I have edited the spreadsheet to see if the format is better for what we are trying to achieve.  


Thanks Rob

Simon
Merlin-Management-fees.xlsx
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Ok, so since you've now put the quarter totals in that sheet, it looks pretty straight forward to create invoices for each customer. It's late here so I'll see what I come up with tomorrow.

Rob.

Author

Commented:
:) thanks Rob cant wait. Not just the fact you will find a solution but the how you did it I will find cool.
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Sorry I didn't get time today. I'll work on it tonight.

Rob.
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
OK, spent an hour on this, and came up with some VBS code that should do the job I think.

Save this code into Notepad, then Save As something like C:\Scripts\CreateInvoices.vbs

If LCase(Right(Wscript.FullName, 11)) = "wscript.exe" Then
    strPath = Wscript.ScriptFullName
    strCommand = "%comspec% /k cscript  """ & strPath & """"
    Set objShell = CreateObject("Wscript.Shell")
    objShell.Run(strCommand), 1, True
    Wscript.Quit
End If

strFees = "C:\Temp\Scripts\Merlin-Management-fees.xlsx"
strInvoiceTemplate = "C:\Temp\Scripts\Merlin-invoice-template-USE-.xls"

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objExcel = CreateObject("Excel.Application")
Const xlUp = -4162
Const xlNormal = -4143
Const xlExcel8 = 56
Const xlOpenXMLWorkbook = 51
objExcel.Visible = True
Set objFeesWB = objExcel.Workbooks.Open(strFees, False, False)
Set objFees = objFeesWB.Sheets(1)
strInvoice = objFees.Cells(5, "I").Value
strInvoicePrefix = Left(strInvoice, 2)
intInvoiceNumber = CInt(Mid(strInvoice, 3))
For intRow = 5 To objFees.Cells(65536, "A").End(xlUp).Row
	strCustomer = Trim(objFees.Cells(intRow, "A").Value)
	strInvoiceLocation = Trim(objFees.Cells(intRow, "G").Value)
	If strInvoiceLocation <> "" Then
		If objFSO.FolderExists(strInvoiceLocation) = False Then
			WScript.Echo "Invoice location " & strInvoiceLocation & " does not exist for customer " & _
				strCustomer & " on row " & intRow & vbCrLf & "Please check invoice location."
		Else
			If Right(strInvoiceLocation, 1) = "\" Then strInvoiceLocation = Left(strInvoiceLocation, Len(strInvoiceLocation) - 1)
			For intCol = 3 To 6
				Set objTemplateWB = objExcel.Workbooks.Open(strInvoiceTemplate, False, False)
				strQuarterDesc = objFees.Cells(4, intCol).Value
				strQuarterFees = objFees.Cells(intRow, intCol).Value
				strInvoicePath = strInvoiceLocation & "\" & strCustomer & " " & Replace(strQuarterDesc, "/", "-") & ".xlsx"
				objExcel.DisplayAlerts = False
				objTemplateWB.SaveAs strInvoicePath, xlOpenXMLWorkbook
				objExcel.DisplayAlerts = True
				Set objTemplate = objTemplateWB.Sheets(1)
				objTemplate.Cells(3, "N").Value = strInvoicePrefix & Right(String(Len(strInvoice), "0") & CInt(intInvoiceNumber), Len(strInvoice) - 2)
				intInvoiceNumber = intInvoiceNumber + 1
				objTemplate.Cells(13, "D").Value = strCustomer
				objTemplate.Cells(13, "N").Value = Date
				objTemplate.Cells(21, "L").Value = strQuarterFees
				objTemplate.PrintOut , , 2
				objTemplateWB.Save
				objTemplateWB.Close True
				Set objTemplate = Nothing
				Set objTemplateWB = Nothing
			Next
		End If
	End If		
Next
objFeesWB.Close
objExcel.Quit
WScript.Echo "Done"

Open in new window


Change strFees to the path of your fees spreadsheet, and strInvoiceTemplate to the path of your invoice template.  The template gets saved into each customer folder before it gets modified.  I also made the individual invoices save in XLSX format.  I can add more output logging if you want, and save logging to a file as well if you need.

Regards,

Rob.

Author

Commented:
Wow excellent thanks rob.  I'm just entering all the data to the spread sheet and file unc paths.  Does it print them or ask to printer or does it create them all in one go?

Regards
Simon

Author

Commented:
Also in the spread sheet under location is it ok to use "" when there is a gap in the directory names?

Author

Commented:
Do not wish to sound ungrateful but would it be possible for the script to print 2 copies also or would that be a nightmare?

Author

Commented:
PS sorry just to let you know the data is now in the spread sheet so will test first thing Monday. Have a great weekend rob and thanks for all your help.

Simon
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Hi, I forgot to add printing, but we can definitely add a print for two copies. You can leave the directory blank, and it just won't generate those invoices. In fact, it'd be good for testing probably to only have on or two directories listed in the data. That's what I tested with.  You would save paper too ;-)

I'll add printing tomorrow.

Rob.
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
I have edited my previous comment ID 40053258 to add
                        objTemplate.PrintOut , , 2

above the
                        objTemplateWB.Save

line.  This prints out two copies to the currently selected active printer.

Test that out and let me know if you need anything else.

Regards,

Rob.

Author

Commented:
Sorry Rob is there a new link to the new version script?

Author

Commented:
forget last :)

Author

Commented:
Hi Rob

I added a dummy record to the spread sheet just for testing purposes and ran the script.  May I just start by saying how shocked I was at the speed. Very fast its brilliant.  Only one small change needs to happen which was a over sight on my behalf and thats the description field.  Is it possible to have the date ranges added to the description field so it reads like "Management Agent Fee for first quarter of 2014" or "Management Agent Fee for 01/01/2014 - 01/03/2014" ?

Next year when we run the script do I just update my spread sheet or do I need to do anything to the script?

Thank you so much

Regards

Simon
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
BINGO!!!  Rob cant thank you enough.  Absolutely perfect Sir.  I just wish I could give you more points.  I think i will have a play with the above to see if I can adapt for company secretary fees.  It should be a lot simpler as its only once a year.

Thanks you so much Rob and Rock on Experts Exchange!!!!  Fantastic!!!!!

Author

Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for cybersi's comment #a40058565

for the following reason:

Brilliant!  Simply Brilliant!!!
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
No problem. Glad to help. If you need help understanding how it works, let me know.

One thing though, you seem to have picked one of your comments as the answer. You should be able to accept one or more of my comments as the answer and close the question straight away.

Regards,

Rob.

Author

Commented:
Sorry Rob! Will change for you now.  In one of your earlier post you did mention logging.  Is it possible to update the attached spread sheet with the outputs so we can keep an eye on the invoice numbers?

Is it possible for me to reopen this posting and re-close giving you the points?  Sorry about that I must of clicked the wrong thing in my excitement.
If I cant give you the points I will open another one for you and give you the points?  Or we can add a new question about the logging if you wish?
Mangement-Fee-Invoices-Report.xlsx

Author

Commented:
I think I have reallocated right this time please can you check your end.

Thanks
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Thanks, you closed it correctly.  I will have a quick look at the logging.  Do you want that to remain in the separate workbook?  Should be easy enough...give me a few minutes.....

Rob.
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
OK, this will summarise to the report worksheet now.

Regards,

Rob.

If LCase(Right(Wscript.FullName, 11)) = "wscript.exe" Then
    strPath = Wscript.ScriptFullName
    strCommand = "%comspec% /k cscript  """ & strPath & """"
    Set objShell = CreateObject("Wscript.Shell")
    objShell.Run(strCommand), 1, True
    Wscript.Quit
End If

strFees = "C:\Temp\Scripts\Merlin-Management-fees.xlsx"
strInvoiceTemplate = "C:\Temp\Scripts\Merlin-invoice-template-USE-.xls"
strReport = "C:\Temp\Scripts\Merlin-Mangement-Fee-Invoices-Report.xlsx"
blnPrintInvoices = False

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objExcel = CreateObject("Excel.Application")
Const xlUp = -4162
Const xlNormal = -4143
Const xlExcel8 = 56
Const xlOpenXMLWorkbook = 51
objExcel.Visible = True
Set objFeesWB = objExcel.Workbooks.Open(strFees, False, False)
Set objFees = objFeesWB.Sheets(1)
Set objReportWB = objExcel.Workbooks.Open(strReport, False, False)
Set objReport = objReportWB.Sheets(1)
strInvoice = objFees.Cells(5, "I").Value
strInvoicePrefix = Left(strInvoice, 2)
intInvoiceNumber = CInt(Mid(strInvoice, 3))
For intRow = 5 To objFees.Cells(65536, "A").End(xlUp).Row
	strCustomer = Trim(objFees.Cells(intRow, "A").Value)
	strInvoiceLocation = Trim(objFees.Cells(intRow, "G").Value)
	If strInvoiceLocation <> "" Then
		If objFSO.FolderExists(strInvoiceLocation) = False Then
			WScript.Echo "Invoice location " & strInvoiceLocation & " does not exist for customer " & _
				strCustomer & " on row " & intRow & vbCrLf & "Please check invoice location."
		Else
			If Right(strInvoiceLocation, 1) = "\" Then strInvoiceLocation = Left(strInvoiceLocation, Len(strInvoiceLocation) - 1)
			For intCol = 3 To 6
				Set objTemplateWB = objExcel.Workbooks.Open(strInvoiceTemplate, False, False)
				strQuarterDesc = objFees.Cells(4, intCol).Value
				strQuarterFees = objFees.Cells(intRow, intCol).Value
				strInvoicePath = strInvoiceLocation & "\" & strCustomer & " " & Replace(strQuarterDesc, "/", "-") & ".xlsx"
				objExcel.DisplayAlerts = False
				objTemplateWB.SaveAs strInvoicePath, xlOpenXMLWorkbook
				objExcel.DisplayAlerts = True
				Set objTemplate = objTemplateWB.Sheets(1)
				objTemplate.Cells(3, "N").Value = strInvoicePrefix & Right(String(Len(strInvoice), "0") & CInt(intInvoiceNumber), Len(strInvoice) - 2)
				objTemplate.Cells(13, "D").Value = strCustomer
				objTemplate.Cells(21, "D").Value = "Management Agent Fee for " & strQuarterDesc
				objTemplate.Cells(13, "N").Value = Date
				objTemplate.Cells(21, "L").Value = strQuarterFees
				If blnPrintInvoices = True Then objTemplate.PrintOut , , 2
				objTemplateWB.Save
				objTemplateWB.Close True
				Set objTemplate = Nothing
				Set objTemplateWB = Nothing
				intNextReportRow = objReport.Cells(65536, "A").End(xlUp).Row + 1
				objReport.Cells(intNextReportRow, "A").Value = CInt(intInvoiceNumber)
				objReport.Cells(intNextReportRow, "B").Value = strCustomer
				objReport.Cells(intNextReportRow, "C").Value = strQuarterDesc
				objReport.Cells(intNextReportRow, "D").Value = strQuarterFees
				' Increment the invoice number
				intInvoiceNumber = intInvoiceNumber + 1
			Next
		End If
	End If		
Next
objReportWB.Save
objReportWB.Close
objFeesWB.Close
objExcel.Quit
WScript.Echo "Done"

Open in new window

Author

Commented:
Great sorry about that.  When I run this next year will it add to the report or over write?

Many thanks

Simon
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
It will add. It will always write to the next available row. Did you want me to add a year column to the report, and the invoice names as well?

Author

Commented:
If you don't mind that would be great. Thank you.
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Actually I just noticed that the quarter dates you put in the spreadsheet have the year in them anyway, which means the file names and the report already have the year in them.  Did you want that changed in any way?

Rob.

Author

Commented:
No it's ok Rob starting to feel a little guilty with the amount of work you have put in already.

Regards

Simon
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
LOL! It's ok. You can open a new question if you want something else done.

Rob.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.