Exporting individual reports for 30 clients' list of invoices

[moom_carr]

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

 

[moom_carr]

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.

 

[Klatuu]

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

 

[Klatuu]

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?

 

[moom_carr]

Thank you very much!

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

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.