Conditions with Macros

[Guest]

Can someone help me with this question. I have a table in Access that I am
exporting out to excel on a daily basis. I've set up a Macro to perform the
export function which works thus far. The table in access gets new info
downloaded to it on a daily basis and I was wondering if there was a way that
I can set up the Macro to only export the records based on a timestamp field?
If so where would I set that condition?

 

[Ken Snell [MVP]]

You would set this "condition" as a criterion in a query, and then export
that query.

 

[Steve Schapel]

Nmarano,

Presumably you would need a Date/Time field in your table that you could
use to determine which records are included in the export.

You wouldn't use a macro Condition for this. You would smake a query,
based on your table, and with the criteria entered as applicable in your
date field to select the required records. Then, export to Excel from
the query rather than from the table.

 

[Guest]

Thank you both for the help. Would I be correct in saying the following:
Create a select query, selecting the fields that I want to export with the
where condition of now(), then in the same query write my
DoCmd.TransferSpreadsheet? Thanks again for the help

Nick

 

[Steve Schapel]

Nick

When you say "where condition", if you mean a query criteria then the
answer is No, you wouldn't use Now(). You might use Date() for the
criteria, if you mean you want to only export those records with today's
date entered in the date field. Then if you're using
DoCmd.TransferSpreadsheet, you are not using a macro, this is presumably
part of a VBA procedure. There is, however, a TransferSpreadsheet macro
action as well. So, whatever you had before, where you said you were
exporting the table, just replace the table with the query.

 

[Guest]

Steve,

Thanks for the help. Can you answer one last question? It appears that my
Excel file gets rewritten every time the macro runs. Is there a way to add
to the Excel spreadsheet rather than rewriting it?

Thanks again
Nick

 

[Steve Schapel]

Nick,

TransferSpreadsheet has a Range argument, where you can specify the
location for the exported data, either by a named range or cell/sheet
references. Maybe that will serve your purpose? If not, I think the
next step would be to use Office Automation procedures in VBA.