printing a form

[Michelle Beard]

Is it possible to take a database of invoice information (i.e. each row
contains company name, invoice date, amount due, etc) and import(?) it into
a sheet that has an invoice form and print separate invoices for each
customer? I'd also like to have the option to print just those that are
current, or 30, 60, 90 or 120 days out. I'm hoping this can be a macro that
can do all that meet a certain criteria so I don't have to hit "print" for
each invoice. Is this possible? And, what would be the best way of doing
it?

Thanks in advance,
Gary

 

[K Dales]

Is the database in Excel already? If not you can import from an external
database; do a search of this site for "External Data" or "MSQuery."

But if the data is already in Excel, or you can import it using MSQuery:

You can set up a worksheet as your invoice form. To fill in the form you
can use various lookup formulas. For example, let's say you want the data
from row 25 (forget for now how to find the data, just assume that is the row
you need). In the cell of the invoice that has the company name the formula
could be:
=OFFSET(Sheet1!A1, 24, 0)
(this assumes your data is on Sheet1 with the company name in column A)
The invoice date (if it is in column B) would be
=OFFSET(Sheet1!A1, 24, 1)
etc.

I like using named ranges to make these easier to use, so call Sheet1!A1
something like HOMEBASE and have another cell (perhaps hidden or perhaps on
another sheet) for ROWNO. We will use ROWNO to store the row you want to
use, so the formulas now look like this:
=OFFSET(HOMEBASE, ROWNO-1, ...)

Finally, there are many ways to code a macro but the easiest one is to
simply increment the number in ROWNO in steps of 1. Read the data from your
list to see if you need to print it; if so do the print, if not step ahead.
Example:

Sub PrintInvoices()
Dim i as Integer
i=1
While Worksheets("Sheet1").Cells(i,1).Value <> ""
' Put an IF statement here to look at the data and decide if it needs to
be printed
' Example - find invoice dates older than 30 days:
If Worksheets("Sheet1").Cells(i,2).Value < Date() - 30 Then
Range("ROWNO").Value = i
' Now your invoice formulas should be pointing to the current row, so:
Worksheets("Invoice").Printout
End If
Wend
End Sub

This is just the outline but hope you can take it from here.