Automated Weekly Output Table to Excel

  • Thread starter Thread starter bymarce
  • Start date Start date
B

bymarce

I have a catalog of samples that is updated frequently. I would like to
write a macro to output the new entrys to excell every week as a backup. I
made a "make table query" that gets all records from the last week. My macro
currently has 4 lines OpenQuery, Close, Output to, and Set Value. I trying
to use setvalue to set to put todays date in a form. Then I want to use that
date to determine when the macro needs to run (if that date is less than or
equal to todays date -7 run the macro). I can't get the setvalue part of the
macro to work. I used the expression builder for the item line of macro but
it says it can't find that form. The expression is [Forms]![MLOBACKUP Date
Table]![Backup Date]. I first tried it as a table but it couldn't find the
table so I made a form from the table and it still doesn't work. I've seen
sugestions in other posts to use the task scheduler for this type of thing
but the database is on a network and needs to backup independantly of my
computer. Thanks for taking the time to read this whole post.
bymarce
 
I figured out most of my problem through reading more posts. I needed to
use an update query rather than setvalue. Now I'm just trying to figure out
how to get the macro only to run if the date in my backup date table was more
than a week ago. My macro currently looks like this.

Condition Action
Comment
"Tables![Tablename]![fieldname]<Date()-7 OpenQuery makes table of last

weeks records
Close

OutputTo
makes excel file of last

weeks records table
OpenQuery
update query to put

current date in back

up date table
Close
I thought with this condition the macro wouldn't run when I click run if
todays date is less than a week after the previous date but it still runs.
Thanks.
 
I have always operated under the assumption that the condition needed
to be stated on each line that it applied to (or you had to jump over
lines some how.

Was the table that was output really created this time or did it
contain the same entries as the last time it was run.?

Ron
 
I needed to put the three dots on each line so the macro would use the first
codition for every line. What I found to fix my problem was using
DLookUp("[Backup Date]","MLOBUD")<Date()-7 as my condition. I found this in
another post for a daily task without the -7.
 
Back
Top