Automate File/SaveAs/Export in macro

[Ken]

I will describe the manual flow of events that I would
like to automate in a macro. I run a macro to open a
report and select data pertenant to a specific month.
When I run the macro, I am asked to Enter Parameter Value
(enter the month). I enter the month and the report opens
with multiple pages of data in Print Preview. I can then
print the report with File/Print and I save the new report
with File/SaveAs/Export. I give the new file a name and
it is saved as an Access report.

I'm sure there must be a way to save the newly created
report- populated with data- automatically (without having
to go through Print Preview and doing it manually), but I
can't figure out how to do it. I tried the Macro Output
To command, but that didn't seem to work either.
Suggestions would be appreciated. Thanks.

 

[Steve Schapel]

Ken,

I do not understand your statement "I give the new file a name and
it is saved as an Access report". There is no such thing. Are you
saving it as a Snapshot file, or a text file, or something else?
Access report does not exist as a separate file type.

What happened when you try OutputTo macro? If I correctly understand
your requirements, this is the way to go. This macro action mimics
the functionality of the File|Export menu. What are the arguments of
the macro that you tried? Did you get an error message, or just
nothing happened?

- Steve Schapel, Microsoft Access MVP

 

[Ken]

Steve,
Thanks for the reply. To answer your 2 questions:
1. I had the report in Print Preview. When I select
File/ SaveAs/Export/ "Within Current Database as - New
Name:" I enter a new file name when asked for it. The
report is saved under the new file name listed with all
the Access Reports for that database.

2. I did some more playing around with Output To
command. When I saved it as a Snapshot -- I could not
open it. The rich text format looks good in Word (except
the lines do not carry over to the rich text format. My
objective is to print a report based on data for one
month. If my first statement in the Macro is print data
for January and then Output To a richtext format, all the
data in the data base is outputed -- not just January. I
found if I use PrintPreview, it must hold the new report
open and the Output To outputs only January -- which is
what I wanted. So I guess it works OK. (I am missing the
lines, and I have to use Print Preview and not just
automatically print, but I am getting close.)

 

[Steve Schapel]

Hi Ken,

1. Ah, ok, thanks for the explanation. I should have twigged earlier
that you are using an early version of Access. Now the question is...
Why? Aren't you just creating a duplicate copy of your already
existing report? I can now see what you are doing, but I still can't
see your purpose.

2. Microsoft Snapshot Viewer is automatically installed with more
recent versions of Office. If you haven't got it, it is a free
download from
http://www.microsoft.com/downloads/...3f-6d74-423d-8274-8b7e6313edfb&DisplayLang=en
(watch for word-wrap of this URL in your newsreader!)
This allows the viewing and printing of a snapshot file.
The other point, I think, is how are you selecting "January". If the
criteria is in the report's underlying query, or if it is referenced
from a criteria entered in a form, or if it is entered at a prompt
from a Parameter Query, the selection should carry through to the file
produced by the Output To macro. If it doesn't, I can't quite imagine
right now why not.

- Steve Schapel, Microsoft Access MVP

 

[Ken]

Steve,
Hi Steve,
I should have mentioned I am using 97 on my lap top and
will eventually upload it to OfficeXP on my desk top where
it will be used. I'm sure there will be fine tuning when
that happens. I am actually creating billing invoices for
reocurring revenue that is paid once per year.

You asked why am I saving it -- I just wanted to have a
copy of the exact invoice that was generated and sent to
the customer.

You raise a good point about generating the criteria in
the query. I am generating it in the Open Report Macro
statement.

The more I am getting into this, I am beginning to think I
should try to us query's to automatically create new
records in a billing database and then print the
invoices.

Are you aware of any template invoicing databases written
that you can modify to meet your particular needs?

Thanks your your assistance.

Ken

 

[Steve Schapel]

Ken,

The idea of "saving" a copy of a printed report as you described would
generally be an extremely unusual, and probably invalid, idea in a
database. And I am sorry to say that I don't think it is doing what
you think it is doing anyway.

Regarding accounting databases, there is a fair bit of related bedtime
reading here...
http://www.granite.ab.ca/accsacct.htm

- Steve Schapel, Microsoft Access MVP