Making an export to Excel macro work with no further input

[Chris Green]

I've set up a macro to export two of my queries to Excel for easy e-mailing to non-Access-using recipients. They work just fine when run from the Macros list but I'm trying to automate the process from a control button on the main screen of the whole database.

Unfortunately this masks two instances of dialog boxes prompting me with "File exists. Overwrite? Yes or No" (the default is "No") so unbeknown to the operator, nothing has happened behind the scenes. Is there another line I can add to the macro (maybe a Sendkeys string?) to make it press on without these dialog boxes causing it to stop indefinitely?

 

[ajetrumpet]

are you using the transfer spreadsheet method? Using this method overwrites any data that is already stored in the target table (in your case, the Excel sheet).

what action in the macro are you using?

 

[Chris Green]

are you using the transfer spreadsheet method? Using this method overwrites any data that is already stored in the target table (in your case, the Excel sheet).

what action in the macro are you using?

I'm currently using OutputTo. I've looked at your suggestion but it only seems to work for sending the contents of a table, not a query. Would it work with a query? I suppose if I turned my two queries into Maketable queries, that would give me table names to play with, although this process would also throw up new dialog boxes.

 

[ajetrumpet]

yes, they work with queries. queries are tables too.

are they action queries?? if they are, it won't work.

 

[Rabbie]

yes, they work with queries. queries are tables too.

are they action queries?? if they are, it won't work.

Oh no they are not. Tables and select queries are both Datasets

 

[Chris Green]

Thanks guys - I see now that TransferSpreadsheet works just as well with queries and my problem seems to be "solv-ed" as Clouseau would say. This is an object lesson in making sure that everything has a unique name, otherwise a query with the same name as a table would muck things up here. It's reminded me to get back into my old habit of starting each object title with tbl: or qry: or frm: or mcr: or rpt:

Apparently, old habits DON'T die hard.

When I look back at what I designed for a previous employer several years ago, my first reaction was "My God - did I really KNOW all this stuff?"

 

[ajetrumpet]

Oh no they are not. Tables and select queries are both Datasets

So, then to put it in "young person's terms" -

Table = Dataset

Forget about the definitions. just do the work...