template creates invoice - how to create summary worksheet?

[Tony Barratt]

Hi,
I have been asked to help a friend.
It seems that she creates an invoice from a template downloaded from
MS Office site.
So a workbook with a file name with the invoice number in it is
magically created.
Then the customer address, number of item and item cost is added
manually, Date and sales tax and total amount is calculated, Then the
invoice is printed and sent in the post.
So far so so good.

Additionally for each invoice generated, some of the fields are copied
and pasted into a row in an Invoice Summary worksheet.
As the number of invoices per week is rising, automating the
generation of the Invoice Summary sheet would be helpful.

Could someone please help me out, and let me know what approaches
might work?


TIA

Tony

 

[Gord Dibben]

Easy enough to do utilizing the functions of the Template Wizard
Add-in that used to be bundled with Excel.

I can probably assist with this because I have a copy of the
add-in..........works with versions up to and including 2007.

Before we get too enthusiastic.........what version of Excel is your
friend running?

Do you know which Template she is using for her invoices?


Gord

 

[GS]

Gord Dibben formulated on Monday :

Easy enough to do utilizing the functions of the Template Wizard
Add-in that used to be bundled with Excel.

Gord,
May I ask which version this wizard was bundled in?

 

[GS]

I have an addin ("Invoicing: Simple Bookkeeping") that I've developed
for use by self-employed people or small home-based business ventures.
It doesn't use any MS invoice templates but it does provide 4 templates
by default, customizeable to the user's specs. These are generated to a
fiscal workbook and currently need to be entered manually in the
associated "bookkeeping" workbook for the fiscal period. (Fiscal period
is Jan 1 to Dec 31; normal individual tax year) Since the bookkeeping
file is separate (invoicing or beeokkeeping work stand-alone) it makes
sense to offer an option to programmatically add invoices to the
"Income" sheet of the bookkeeping file as they're created. Though users
can change the invoice template on the fly, sales transaction numbering
(format:="yyyy-####") is sequential regardless of which template is
used, so inserting the details into the Income sheet is trivial
(date,customer,invoice#,currency,amount,tax1,tax2).

I'd be interested in studying the wizard addin if it writes to a
summary sheet. Otherwise, I intend to use ADODB if the bookkeeping file
is not open; write the sheet directly if the file is open.

 

[Gord Dibben]

Latest version I had with the Template and Tracking Wizard was Excel
97.

I think xl2000 also supplied it but I never ran that version.

I have tested the 97 version of the Wizard quite a bit with
xl2002(XP), 2003 and 2007.

Have sent it out numerous times to folks who have emailed me for a
copy.


Gord

Gord Dibben formulated on Monday :

Easy enough to do utilizing the functions of the Template Wizard
Add-in that used to be bundled with Excel.

Gord,
May I ask which version this wizard was bundled in?

 

[Gord Dibben]

Garry

The Template Wizard writes user-defined details from each transaction
out to a database in a summary workbook.

Not just for Invoices.........could be work orders or expense accounts
etc.


Gord

 

[GS]

After serious thinking Gord Dibben wrote :

Latest version I had with the Template and Tracking Wizard was Excel
97.

I think xl2000 also supplied it but I never ran that version.

I have tested the 97 version of the Wizard quite a bit with
xl2002(XP), 2003 and 2007.

Have sent it out numerous times to folks who have emailed me for a
copy.


Gord

Gord Dibben formulated on Monday :

Easy enough to do utilizing the functions of the Template Wizard
Add-in that used to be bundled with Excel.

Gord,
May I ask which version this wizard was bundled in?

Thanks, Gord! I have XL8/9 installed in another machine I use for
testing. My XL9 is MSO 2000 Developer Edition; XL8 is Office 97
Professional. I'll see if it exists on the hard disc and report back.

Thanks again...

 

[GS]

Garry

The Template Wizard writes user-defined details from each transaction
out to a database in a summary workbook.

Not just for Invoices.........could be work orders or expense accounts
etc.


Gord

Gord,
Thanks! I already know pretty much the what/how of what I want to do.
My bookkeeping part of the app is a multi-sheet project that handle
income, expenses, customers, vendors, P&L, chart of accounts, taxes,
personal tax forms, and any number of bank ledger sheets depending on
how many bank accounts you want to keep transactions for. Thus, I doubt
the wizard will work directly with my app but I still want to see its
what/how. Fact is, anyone generating enough invoices that mandates
automatic entry into some tracking component should, IMO, be using
commercial accounting software. As you probably know, these are
database driven. My app is entirely XL, each year being a new fiscal
period that new working files are created the first time the app opens
in a new calendar year. This precludes the bookkeeping file be
perpetual so that the customer/vendor data and COA remains current.
Clearing the income/expense sheet input areas is all that's required to
start a new period. Bank ledger sheets are replaced with new ones that
have the closing balances carried forward.

This is probably too complex to bother trying to make one's own
accounting app, but it's certainly much easier to use and is tailored
to input directly into our tax return e-forms (I'm in Canada). I
originally started with invoicing only, using one of the templates that
ships with XL. It just evolved into a bookkeeping project due to user
demand, which also led to redesigning the invoice template to be with
or without product/service pricing features for automatic filling in
fields. Very flexible, but as I said, it's probably overly complex for
someone to make since reasonably robust accounting software can be had
fairly cheap nowadays!<g>

 

[Tony Barratt]

Gord,
Thanks! I already know pretty much the what/how of what I want to do.
My bookkeeping part of the app is a multi-sheet project that handle
income, expenses, customers, vendors, P&L, chart of accounts, taxes,
personal tax forms, and any number of bank ledger sheets depending on
how many bank accounts you want to keep transactions for. Thus, I doubt
the wizard will work directly with my app but I still want to see its
what/how. Fact is, anyone generating enough invoices that mandates
automatic entry into some tracking component should, IMO, be using
commercial accounting software. As you probably know, these are
database driven. My app is entirely XL, each year being a new fiscal
period that new working files are created the first time the app opens
in a new calendar year. This precludes the bookkeeping file be
perpetual so that the customer/vendor data and COA remains current.
Clearing the income/expense sheet input areas is all that's required to
start a new period. Bank ledger sheets are replaced with new ones that
have the closing balances carried forward.

This is probably too complex to bother trying to make one's own
accounting app, but it's certainly much easier to use and is tailored
to input directly into our tax return e-forms (I'm in Canada). I
originally started with invoicing only, using one of the templates that
ships with XL. It just evolved into a bookkeeping project due to user
demand, which also led to redesigning the invoice template to be with
or without product/service pricing features for automatic filling in
fields. Very flexible, but as I said, it's probably overly complex for
someone to make since reasonably robust accounting software can be had
fairly cheap nowadays!<g>

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

 

[Tony Barratt]

Gord,
Thanks! I already know pretty much the what/how of what I want to do.
My bookkeeping part of the app is a multi-sheet project that handle
income, expenses, customers, vendors, P&L, chart of accounts, taxes,
personal tax forms, and any number of bank ledger sheets depending on
how many bank accounts you want to keep transactions for. Thus, I doubt
the wizard will work directly with my app but I still want to see its
what/how. Fact is, anyone generating enough invoices that mandates
automatic entry into some tracking component should, IMO, be using
commercial accounting software. As you probably know, these are
database driven. My app is entirely XL, each year being a new fiscal
period that new working files are created the first time the app opens
in a new calendar year. This precludes the bookkeeping file be
perpetual so that the customer/vendor data and COA remains current.
Clearing the income/expense sheet input areas is all that's required to
start a new period. Bank ledger sheets are replaced with new ones that
have the closing balances carried forward.

This is probably too complex to bother trying to make one's own
accounting app, but it's certainly much easier to use and is tailored
to input directly into our tax return e-forms (I'm in Canada). I
originally started with invoicing only, using one of the templates that
ships with XL. It just evolved into a bookkeeping project due to user
demand, which also led to redesigning the invoice template to be with
or without product/service pricing features for automatic filling in
fields. Very flexible, but as I said, it's probably overly complex for
someone to make since reasonably robust accounting software can be had
fairly cheap nowadays!<g>

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Thanx for your reply Gord!

->Before we get too enthusiastic.........what version of Excel is your
friend running?

2007

-> Do you know which Template she is using for her invoices?

No. The actual template used is not known.

 

[Gord Dibben]

Wizard OK with 2007 except it is limited to 256 columns and will save
templates only as *.xlt.


Gord

 

[Gord Dibben]

It may be just as easy to see what Garry has or go to Ron de Bruin's
site and have a look at his "creating a summary sheet from many
workbooks"

http://www.rondebruin.nl/summary2.htm


Gord

 

[Tony Barratt]

It may be just as easy to see what Garry has or go to Ron de Bruin's
site and have a look at his "creating a summary sheet from many
workbooks"

http://www.rondebruin.nl/summary2.htm

Gord

I have learnt that an upgrade to excel 2010 is going to happen
sometime soon.
and that she might start from the beginning again.
Any suggestions as to the right way to start inplementing invoices and
maybe a list of products (dresses in this case)?

 

[GS]

I have learnt that an upgrade to excel 2010 is going to happen
sometime soon.
and that she might start from the beginning again.
Any suggestions as to the right way to start inplementing invoices and
maybe a list of products (dresses in this case)?

Not sure what you mean by "the right way" but I suggest an addin that
uses pre-designed invoice templates for specific products. The user
should be able to select which template to use 'on-the-fly'.

A products list could be maintained in a delimited text file so it's
easy to maintain, OR a separate Excel file that your addin opens hidden
so it can present product info 'under the hood'.