automatically exports queries into excel files

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

Guest

hello,
I am thinking if I can set up a default in Access to automatically generate
a query into excel each day. For example my query is set up to be group by
todays date: date()

But at the end of the day I want it to automatically export into excel files
automatically. The reason I am trying to do this is because some users do
not have Access program and the only way for them to view a query is in excal
format. I want to set up something where it will automatically generate into
excel.
If there any way to so this? Please HELP!!! Thanks first!!!! :-)
 
You could very easily create a macro to create an excel file, but it would
need to be run by someone everyday, rather than happening automatically.
Access itself doesn't really have that kind of functionality. You'd have to
use a secondary software program for automation/task scheduling to do that,
such as AutoMate. Here is a list I pulled off google of the type of
shareware apps I'm speaking of:
http://www.pcsoftland.com/utilities/automation-tools/indexrating.htm

To create the export macro, create a new macro. The action will be
OutputTo. Then, you'll want the Object Type below to be Query, the Object
Name to be the name of your query, the Output format to be Excel, and the
Output File to be the name of the Excel file. Be aware that with this
method, the Excel file would be overwritten every time you run the macro.

Then, to automate the function, you would use the automation software of
your choice to open the database, run the macro, and close the database at a
certain time every day. This could be overnight, before you get in to work,
or perhaps at the close of your business day.

Hope this helps,
Jane
 
The way I would approach this is to create a separate Access database which
is linked to the tables/queries you want to use. This Access application
would have a macro (or vba code if you prefer) which would run when the
application is opened.

You can set up a scheduled windows procedure to open the Access application
at a specific time every day.

Linda
 
I am not an Excel expert but I am sure that you can create an Excel file
that link to Table(s) in Access. This way, when the user opens the Excel
file, he/she will see the current live data from Access rather than the
canned data which is out of date.

See Excel Help topic "About exchanging data between Excel and Access".
 
Hello Jane,
Thanks for your detailed instructions. I really needed that. I am
trying it out, is there a software you recommend from the list you provided
me?

thanks for your time,
Tina LI
 
Back
Top