Creating a Database from Template Invoice

[Domenic]

Hi everyone!

I've created a template for invoicing. How can I automatically create a
database for these invoices.

In other words, after creating an invoice, I'd like to have the
pertinent information transfered and collected into a database without
having to enter it myself onto another sheet or workbook.

Thanks in advance for your help!

 

[Bernard Rey]

Domenic wrote :

I've created a template for invoicing. How can I automatically create
a database for these invoices.

In other words, after creating an invoice, I'd like to have the
pertinent information transfered and collected into a database without
having to enter it myself onto another sheet or workbook.

This whould be easy to do with a macro copying the data from the current
invoice to another workbook used as a database. You could then put a button
in a toolbar (or on the sheet) and a click on it would add the data to the
list. Are you somewhat familiar with macros?

Basically, the pattern could be something like:

Sub SaveToDatabase()

InvoiceNumber = Range("A2") ' to be adapted
CustomersName = Range("B2") ' etc...

Workbooks.Open Filename:="HD:Users:mynameesktopatabase.xls"
NewRow = Range("A65536").End(xlUp).Row + 1 ' gets the first free cell
Range("A" & NewRow) = InvoiceNumber ' fills in
Range("B" & NewRow) = CustomersName

ActiveWorkbook.Save
ActiveWindow.Close

End Sub

 

[Domenic]

Hi Bernard,

Are you somewhat familiar with macros?

Unfortunately, that's an aspect of Excel I know nothing about. I tried
the Macro and I get an error message. I'm sure it's something I'm doing
wrong. The message is as follows:

Compile Error:
Only comments may appear after end sub, etc...

Here's what I did:
1) Opened my template invoice
2) Control clicked the sheet tab
3) Selected View Code
4) Pasted the macro code between Private Sub Worksheet... and End Sub
5) Amended the code to specify which cells contains InvoiceNumber and
CustomerNames (only these two fields for now until I can sort this out)
6) Amended the code to specify the correct path for my database file,
which I saved as Database.xls and placed on my desktop

Where did I go wrong? Thank you for your help!

 

[Bernard Rey]

Domenic wrote :

Unfortunately, that's an aspect of Excel I know nothing about. I tried
the Macro and I get an error message. I'm sure it's something I'm doing
wrong. The message is as follows:

Compile Error:
Only comments may appear after end sub, etc...

That comes from the way you did you "insertion" in fact within another macro
(but you couldn't know about it) Let's see it a bit further:

Here's what I did:
1) Opened my template invoice

Good start ;-)

2) Control clicked the sheet tab
3) Selected View Code

There would have been a better way.

4) Pasted the macro code between Private Sub Worksheet... and End Sub

That's where you did it wrong. That way you (I suppose) have two "End sub"
lines, which brings you the error message. Delete everything on that code
sheet if you haven't yet.

5) Amended the code to specify which cells contains InvoiceNumber and
CustomerNames (only these two fields for now until I can sort this out)
6) Amended the code to specify the correct path for my database file,
which I saved as Database.xls and placed on my desktop

That's right, but you won't necessarily save it on your desktop. Of course.

Open your Template Invoice.
With the "Tools menu" > "Macro" > "Visual Basic Editor" option (or the
"Option-F11" keyboard shortcut), open the VBE.
Add a module ("Insertion" > "Module") so you'll have a blank code sheet.
On this sheet, paste the macro from the previous message, and adapt it to
your needs.
"Option-F11" your way back to the usual Excel display. Save your Template.

Now you can run your macro with a "Option-F8" KB shortcut. If you want, you
can link your macro to a toolbar button: View" > "Toolbars" > "Customize",
then from the "Commands" pane, get the "custom button" from the "macro"
line, drag it to a toolbar. Control-click on it and Attach your macro to it.
From now on a click on this button will automatically launch your macro.

You can avoid the "flashing" of the display if you want, by inserting:

Application.ScreenUpdating = False

As a second line to the macro. Now it should read:

Sub SaveToDatabase()

Application.ScreenUpdating = False

InvoiceNumber = Range("A2") ' to be adapted

There are quite a few more possibilities to have it more sophisticated, but
let's already begin with this and let us know if you find your way out of
it...

 

[Domenic]

Bernard, I followed your instructions, including the bit about
"flashing", and it works beautifully. I have to say I'm excited now
that I have that sorted. Thank you very much for your help!

You mentioned that I can also place a button on the worksheet itself.
How is that done?

There are quite a few more possibilities to have it more sophisticated, but
let's already begin with this and let us know if you find your way out of
it...

I would be very interested in finding out about them!

 

[Bernard Rey]

Domenic wrote :

Bernard, I followed your instructions, including the bit about
"flashing", and it works beautifully. I have to say I'm excited now
that I have that sorted. Thank you very much for your help!

Glad that it worked fine this time.

You mentioned that I can also place a button on the worksheet itself.
How is that done?

On the "Forms" toolbar, click on the "Button" tool (2nd row). Your cursor
changes to a cross, you can diagonally draw a button on your sheet. When
finished, you should see a dialog box asking you which macro you qould like
to assign to this button. That's it

Should that dialog box not come up automatically, or if you simply want to
change something (font color, etc) on the button, control-click on it.

I would be very interested in finding out about them!

Well, there would be a lot to tell about it. Maybe you could start with a
book. There are quite a lot of them. Most do deal with the Windows versions
of Excel, but most of it can be used on the Mac versions. You just have to
know that Mac uses Visual Basic for Applications version 5, which was the
case with windows up to Excel 97 (the recent versions use VBA 6). So you may
find older books, cheaper than the recent ones and just as accurate as far
as Mac is concerned. There are some more limitations, but most instructions
will word as described.

 

[Domenic]

On the "Forms" toolbar, click on the "Button" tool (2nd row). Your cursor
changes to a cross, you can diagonally draw a button on your sheet. When
finished, you should see a dialog box asking you which macro you qould like
to assign to this button. That's it

Works great! I think I prefer this as opposed to placing a button on
the toolbar. Mainly because I like the way the button looks.

Well, there would be a lot to tell about it. Maybe you could start with a
book. There are quite a lot of them. Most do deal with the Windows versions
of Excel, but most of it can be used on the Mac versions.

Yes, I'll definitely have to do some reading. I can see the great value
in using macros with Excel. Makes work a lot easier.

You just have to
know that Mac uses Visual Basic for Applications version 5, which was the
case with windows up to Excel 97 (the recent versions use VBA 6).

Initially, I was very disappointed to learn about this. Now, I
understand, Microsoft Office 2004 is still using version 5. I don't
understand why.

So you may
find older books, cheaper than the recent ones and just as accurate as far
as Mac is concerned. There are some more limitations, but most instructions
will word as described.

Thanks! I appreciate the advice!

 

[JE McGimpsey]

Initially, I was very disappointed to learn about this. Now, I
understand, Microsoft Office 2004 is still using version 5. I don't
understand why.

I have no real inside info, but I suspect the reason is dollars. Too
many dollars to update and too few dollars demanding it. For 2004, I
suspect the automation budget was almost entirely spent on Applescript -
with excellent results.

VBA is a dead language walking. Barring something tremendously
surprising, I can't imagine there being any development on the WinOffice
side, much less the Mac side. The .Net solution will probably be
available in WinOffice 12 or 13. No idea what MacBU will decide to do,
but they'll have to choose shortly after the Win side does.

In any case, WRT XL, VBA6 isn't that different from the user's POV.
There are a few new functions and a couple of options on some others.
I'm working on a page for my site that explains how I use an add-in to
implement VBA6 functions in MacXL and WinXL97, or, alternatively, to use
conditional compilation. If you have any specific questions, feel free
to post them.

 

[Domenic]

I have no real inside info, but I suspect the reason is dollars. Too
many dollars to update and too few dollars demanding it.

I suspected that as well. Nevertheless, whatever the reason, it's still
very disappointing.

VBA is a dead language walking.

I like your expression.

The .Net solution will probably be
available in WinOffice 12 or 13. No idea what MacBU will decide to do,
but they'll have to choose shortly after the Win side does.

I understand from a previous post of yours that the Mac side is pushing
for RealBasic. If RealBasic is derived from the Basic programming
language, then I don't think I would mind, since I found some of basics
fairly easy to learn. It's been a while, so I would have to refresh my
memory.

Of course, I know nothing about .Net. But, whatever happens, I'd like
to see both sides adopt the same language. I think it's important for
the two to be compatible.

In any case, WRT XL, VBA6 isn't that different from the user's POV.
There are a few new functions and a couple of options on some others.
I'm working on a page for my site that explains how I use an add-in to
implement VBA6 functions in MacXL and WinXL97, or, alternatively, to use
conditional compilation.

Interesting! I've come across your website before and found some useful
information. Now that I've started looking into macros, I'll have to
re-visit.

If you have any specific questions, feel free
to post them.

Thanks! I appreciate it!