Continuing the information from podcasts 742 and 743, I record a simple macro today to open the Invoice.xlt file and then add a couple lines of code to update the invoice number and the date. Episode 744 shows you how.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey! Welcome back to the MrExcel netcast.
This is our third part and how to create an invoice.
That's going to auto increment when we open it.
In the last two netcast, we learn how to get an invoice from office online and then how to save it as a template.
Now, I'm going to record a very simple macro here.
That's going to go through the process of opening that template.
So, tools, macro record, new macro, notice that I'm in a brand new workbook.
I'm going to store the macro in that workbook.
I'll call it new invoice, click [ ok ] and the macro is going to be very simple.
We're just going to go through and do file, new, templates on my computer.
Actually, there's a recently used template from yesterday.
So, I'll choose invoice.xlt and click [ stop recording ], very simple, tiny little macro.
If we go [ alt F11 ] and take a look at the code.
It ends up being, basically just a one-line, a bit of code.
Now, let's see what we have open.
We have two things up when we have the invoice file and we also have our original workbook and right here on our original workbook in cell A1.
I'm going to put the next invoice number.
So, 1207 let's say just as a guess and we will come back here to a recorded code and we'll say a couple of things first of all, this invoice equals this workbook dot worksheets 1 dot cells 1 comma 1.
I'm also going to add one to it.
I'll just copy this reference equals this invoice plus one and then I want to save this workbook.
Now, this workbook is a special name that refers to the workbook in which the code is running.
So, this will make sure that we increment the invoice numbers every time After we've opened the template, let's take a look back at the template and we'll see that the invoice number is actually in cell B9.
So, we can say cells 9 comma 2, B9 is the 9th row, second column dot value equals this invoice.
The other thing that I noticed about this invoice that was a little strange is up there in cell B8.
They have a today function which is great for putting in today's date.
I'm recording this on April 14th.
But what would be really nice as if I could hard code that date in that cell.
So, run out another line of code, cells eight comma two value equals date, special keyword reserved word and basically now, what I want to do, is anytime I want to create a new invoice.
I want to run this macro.
So, we're done recording will close the invoice and now, it's just a simple matter of running new invoice, click [ Run ] and you see that, the next invoice number 1207 is added.
We would fill in this data, save it and close it, file close and then we want to run the macro, again.
I'll [ F8 ], choose new invoice, click [ Run ] and we get invoice 1208.
So, every time we open this, we're going to get a brand new invoice with a brand new number.
Okay! Well, now we're almost there.
Now, tomorrow we'll take a look at how to take that code, that we recorded it and save it as an ad-in, assign to a shortcut keys, that we've been very easily and silently generate these new invoices.
Thanks for stopping by, we'll see you next time for another netcast from MrExcel.
This is our third part and how to create an invoice.
That's going to auto increment when we open it.
In the last two netcast, we learn how to get an invoice from office online and then how to save it as a template.
Now, I'm going to record a very simple macro here.
That's going to go through the process of opening that template.
So, tools, macro record, new macro, notice that I'm in a brand new workbook.
I'm going to store the macro in that workbook.
I'll call it new invoice, click [ ok ] and the macro is going to be very simple.
We're just going to go through and do file, new, templates on my computer.
Actually, there's a recently used template from yesterday.
So, I'll choose invoice.xlt and click [ stop recording ], very simple, tiny little macro.
If we go [ alt F11 ] and take a look at the code.
It ends up being, basically just a one-line, a bit of code.
Now, let's see what we have open.
We have two things up when we have the invoice file and we also have our original workbook and right here on our original workbook in cell A1.
I'm going to put the next invoice number.
So, 1207 let's say just as a guess and we will come back here to a recorded code and we'll say a couple of things first of all, this invoice equals this workbook dot worksheets 1 dot cells 1 comma 1.
I'm also going to add one to it.
I'll just copy this reference equals this invoice plus one and then I want to save this workbook.
Now, this workbook is a special name that refers to the workbook in which the code is running.
So, this will make sure that we increment the invoice numbers every time After we've opened the template, let's take a look back at the template and we'll see that the invoice number is actually in cell B9.
So, we can say cells 9 comma 2, B9 is the 9th row, second column dot value equals this invoice.
The other thing that I noticed about this invoice that was a little strange is up there in cell B8.
They have a today function which is great for putting in today's date.
I'm recording this on April 14th.
But what would be really nice as if I could hard code that date in that cell.
So, run out another line of code, cells eight comma two value equals date, special keyword reserved word and basically now, what I want to do, is anytime I want to create a new invoice.
I want to run this macro.
So, we're done recording will close the invoice and now, it's just a simple matter of running new invoice, click [ Run ] and you see that, the next invoice number 1207 is added.
We would fill in this data, save it and close it, file close and then we want to run the macro, again.
I'll [ F8 ], choose new invoice, click [ Run ] and we get invoice 1208.
So, every time we open this, we're going to get a brand new invoice with a brand new number.
Okay! Well, now we're almost there.
Now, tomorrow we'll take a look at how to take that code, that we recorded it and save it as an ad-in, assign to a shortcut keys, that we've been very easily and silently generate these new invoices.
Thanks for stopping by, we'll see you next time for another netcast from MrExcel.
