B
Beginner
Steve
It was rather easy to create a macro as you had suggested
below. The hitch is creating different excel files. Would
it be possible to pass the filename as a variable to the
Output file argument of the output to action of the macro
so that the query is output to different files during each
loop ?
Thanks in advance for any help
Beginner
I have attached the previous thread below...
Subject: Re: Query/Report
From: "Steve Schapel" <[email protected]> Sent:
9/11/2003 10:36:39 PM
Beginner,
Thanks for the further explanation. You are correct, that
the idea of
using a customer selected in a combobox as a criteria
would involve
manually selecting each customer one by one.
If you want each customer's data presented as a separate
report, you
can base a Report on a query which includes the data for
all
customers, and then use the Report's 'Sorting and
Grouping' facility
to show the data for each customer on a separate page.
But this is
still all data in the one file if you export it.
It is possible to cycle through the customers one by one
using a
macro, and exporting a file with the data for each.
Assuming you have
a Customers table, make a temporary copy of it, and then
this would
involve exporting, using your macro, from a query whose
criteria is
DMax("[Customer]","CustomersCopy") followed by an
OpenQuery action in
your macro to run a Delete Query to delete from the
CustomersCopy
table using the same criteria. Make another macro using
the RunMacro
action to run the macro that contains these two existing
actions, and
in its Repeat Count argument put =DCount("*","Customers")
Obviously this is not a trivial procedure, but with a bit
of work you
should be able to get it going properly. Otherwise, a
somewhat less
awkward (though still non-trivial) approach is to use a
VBA procedure
rather than a macro, using a For Each ... Next construct
to loop
through the Customers and output the report for each.
I realise that this is not a complete answer. A complete
answer would
be a long answer. But hopefully it might point you in the
right
direction.
- Steve Schapel, Microsoft Access MVP
It was rather easy to create a macro as you had suggested
below. The hitch is creating different excel files. Would
it be possible to pass the filename as a variable to the
Output file argument of the output to action of the macro
so that the query is output to different files during each
loop ?
Thanks in advance for any help
Beginner
I have attached the previous thread below...
Subject: Re: Query/Report
From: "Steve Schapel" <[email protected]> Sent:
9/11/2003 10:36:39 PM
Beginner,
Thanks for the further explanation. You are correct, that
the idea of
using a customer selected in a combobox as a criteria
would involve
manually selecting each customer one by one.
If you want each customer's data presented as a separate
report, you
can base a Report on a query which includes the data for
all
customers, and then use the Report's 'Sorting and
Grouping' facility
to show the data for each customer on a separate page.
But this is
still all data in the one file if you export it.
It is possible to cycle through the customers one by one
using a
macro, and exporting a file with the data for each.
Assuming you have
a Customers table, make a temporary copy of it, and then
this would
involve exporting, using your macro, from a query whose
criteria is
DMax("[Customer]","CustomersCopy") followed by an
OpenQuery action in
your macro to run a Delete Query to delete from the
CustomersCopy
table using the same criteria. Make another macro using
the RunMacro
action to run the macro that contains these two existing
actions, and
in its Repeat Count argument put =DCount("*","Customers")
Obviously this is not a trivial procedure, but with a bit
of work you
should be able to get it going properly. Otherwise, a
somewhat less
awkward (though still non-trivial) approach is to use a
VBA procedure
rather than a macro, using a For Each ... Next construct
to loop
through the Customers and output the report for each.
I realise that this is not a complete answer. A complete
answer would
be a long answer. But hopefully it might point you in the
right
direction.
- Steve Schapel, Microsoft Access MVP