Exporting individual reports for 30 clients' list of invoices

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to export a listing of approx. 1500 invoices to individual excel
reports for clients. Can this be done via a macro, or do I have to cut and
paste this?

Thanks,
Nathalie
 
We give our clients an option to receive an invoice list in excel format for
them to manipulate to be able to do their own expenditure analysis. I know
that I could write a macro and create one individual file for each occurring
client, but I was just wondering if there was something that I could write
which would state for every change in customer number, create a new file.
Kind of like creating a page break in the reporting function. I was looking
for a short-cut but there might not be one.
 
You will not be able to do this with a macro. It will take some VBA code.
Basically, you will need to cycle through your clients, determine whether
they get a printed invoice or an Excel Invoice, and take the appropriate
action. Here is an outline of basically what you need to do.

Dim dbf As Database
Dim rst As Recordset

Set dbf = CurrentDb
Set rst = dbf.OpenRecordset("MyClientTable")
If rst.RecordCount = 0 Then
MsgBox "No Records Found"
Else
rst.MoveLast
rst.MoveFirst
Do While Not rst.EOF
If rst![InvoiceType] = "Excel" Then
DoCmd.TransferSpreadsheet......
Else
OpenReport ......
End If
rst.MoveNext
Loop
End If
 
It can be done, but we need more info to help with this:
What format are the invoices in prior to exporting to Excel?
How do you know which invoice goes to which client?
Why not use an Access report to produce invoices rather than export them to
Excel?
 
Thank you very much!

Klatuu said:
You will not be able to do this with a macro. It will take some VBA code.
Basically, you will need to cycle through your clients, determine whether
they get a printed invoice or an Excel Invoice, and take the appropriate
action. Here is an outline of basically what you need to do.

Dim dbf As Database
Dim rst As Recordset

Set dbf = CurrentDb
Set rst = dbf.OpenRecordset("MyClientTable")
If rst.RecordCount = 0 Then
MsgBox "No Records Found"
Else
rst.MoveLast
rst.MoveFirst
Do While Not rst.EOF
If rst![InvoiceType] = "Excel" Then
DoCmd.TransferSpreadsheet......
Else
OpenReport ......
End If
rst.MoveNext
Loop
End If

moom_carr said:
We give our clients an option to receive an invoice list in excel format for
them to manipulate to be able to do their own expenditure analysis. I know
that I could write a macro and create one individual file for each occurring
client, but I was just wondering if there was something that I could write
which would state for every change in customer number, create a new file.
Kind of like creating a page break in the reporting function. I was looking
for a short-cut but there might not be one.
Click to expand...
Click to expand...
 
Back
Top