Automatic Periodic Query Export

[Jasper]

I tried to make a macro which exports the query
"APE-Uren-Activiteiten-Project" from my Acces D-base to an Excel file every
day. Preferably without even opening the Acces file. The big problem is the
periodic factor. Can anybody help me getting started whit such a code? Thanks
in Advance!

 

[tina]

Hi Jasper,

This is Tina. First I want to thank you for reading my repsonse. I read
your posting and I am having trouble generating a report to excel
automatically everyday without manually opening the report and exporting it.
I was wondering do you happen to know if there is a way to do that? My main
concern is to have the report generate into excel without me opening it and
exporting it to a certain drive on my computer. I want the access to help me
do all that with me activating it everyday. Please help!

Thanks,
Tina Li

 

[Ken Snell [MVP]]

You can "automate" such activities via macros or VBA code in an ACCESS
database. You'll need to tell us more information about
when/how/who/what/etc.

 

[tina]

Hello,
Thanks for your reply. Here is my situation:
I am thinking if I can set up a default in Access to automatically generate
a report into excel each day. For example my reprot (my report is name
"todays ecn entry") 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 without a person manually activating the file.
When: Each day (monday to friday)
How: Export "todays ecn entry" REPORT INTO EXCEL FILE AUTOMATICALLY
WHO: NO ONE. I WANT IT TO BE DONE AUTOMATICALLY
WHAT: OTHER USERS DO NOT HAVE ACCESS PROGRAM, SO NEED TO VIEW THE REPORT IN
EXCEL FORMAT.

PLEASE HELP ME. I AM STUCK! I DO NOT KNOW ANYTHING ABOUT VBA CODE IN ACCESS
DATABASE. THANKS FOR YOUR HELP!

 

[tina]

hi! sorry i did not mention eariler. Can you please tell me step by step the
procedure? I am not advance in this! thanks you!
tina.

 

[Ken Snell [MVP]]

I will need some time to type a response...

 

[tina]

ok,, thanks for your effort and time. Thanks again!

 

[Ken Snell [MVP]]

You shouldn't export the report itself into EXCEL, but rather export the
report's Recordsource query into EXCEL.

This can be done via a macro or VBA code that runs the TransferSpreadsheet
action (see Help for information about it).

You can automate the running of such code by creating a macro (name it
AutoExec) that runs the TransferSpreadsheet action. An AutoExec macro runs
when the database is first opened. Thus, opening the database will cause the
code/macro to run. If you want to have the database "open" itself, you can
set up a scheduled task in Windows to open the database file at a particular
time/date. If you do this, be sure to put a Quit action as the macro's last
step so that the database then will close itself after the EXCEL file has
been generated.


--

Ken Snell
<MS ACCESS MVP>

 

[tina]

Hi Ken,

Thanks, I think this will work too, by creating an AutoExec to open when i
open the database. But i seem to be getting an error massage. It says,
The Microsoft Jet databse engine cannot open the file 'C:\Documents and
Settings\tli6\Desktop\today ecn report'. It is already open exclusively by
another user, or you need permission to view its data.

The folder is not open at that time. I'm thinking I must have entered
something wrong in the macro. Here is what i Have:

Action: TransferSpreadsheet

Action Arguments:
Transfer Type: Import (i'm not sure i should choice import or export. I am
transfering the query to an excel sheet)
Spreadsheet type: Microsoft Excel 8-10
Table Name: todays ecn entry
File Name: C:\Documents and Settings\tli6\Desktop\today ecn report
Has Field Name: Yes
Range: (Leave empty)

What am i doing wrong? I've changing the settings but all does not work.
what am i going wrong? Please help! thanks again!

have a nice day!
tina.

 

[Ken Snell [MVP]]

You need the full path to the file, not just to the folder. Add the file
name to the "File Name" string:

C:\Documents and Settings\tli6\Desktop\today ecn report\ActualFileName.xxx

--

Ken Snell
<MS ACCESS MVP>

 

[tina]

Hi ken,
Thanks again for your reply. I;ve tried it and it does not work. it
seems to be asking for a table name instead of a query's name under the
string "table name". I typed in the query name "todays ecn entry" and it
creates a new table with the name"error 1" under TABLES. Is there a way to
put in a query instead on a table?

I've tried using the OutputTo action in Macro and save it as AutoExec, But
the problem with that is, it does not save over the existing file
automatically, instead I have to click YES everytime I open the Access
Database. Is there a way around that? Sorry, for any inconvience I've
caused. Your time and effort is greatly appreciated. Thanks, Have a nice day!

Thanks,
Tina.

 

[Ken Snell [MVP]]

Let's take each issue one at a time...

(1) You put Import as the TransferType. You want to use Export.

(2) Export type will allow you to use a table or a query.

(3) TransferSpreadsheet will overwrite an existing file (without asking you)
if the file contains a spreadsheet page with the same name as the query. If
not, it will add a new sheet to that file.

--

Ken Snell
<MS ACCESS MVP>

 

[tina]

Hi Ken,
Once again Thanks for your patience replies. I tried it and it is not
repsonding. It does not do anything when i open the database or by double
clikcing it. I've tried this is what i have save as

AutoExec: Macro
Action: TransferSpreadsheet

Action Arguments:
Transfer Type: Export
Spreadsheet Type: Microsoft Excel 8-10
Table Name: todays ecn entry (this is the query's name)
File Name: C:\Documents and Settings\tli6\My Documents\document type
Has Field Name: Yes
Range: Left Empty

But when i try it with a TABLE, It has not problems at all. Am I not seeing
something here, that I am doing wrong? Thanks for your time and effort!

Thanks,
Tina.

 

[tina]

HI KEN,

Thanks for your advising and information. I've Got it to work!!! I don't
know what I did differently from the previous times, I just started over
again. Your infomation allowed me to complete this task! Thanks alot!!! Have
a great day!!!! !
Tina Li.

 

[Ken Snell [MVP]]

You're welcome!

--

Ken Snell
<MS ACCESS MVP>

HI KEN,

Thanks for your advising and information. I've Got it to work!!! I don't
know what I did differently from the previous times, I just started over
again. Your infomation allowed me to complete this task! Thanks alot!!!
Have
a great day!!!! !
Tina Li.