Okay. This is my first posting here, so I hope you guys can help me out.

I'm working on an Access database. The boss-lady wants this database to have an output so that certain data is transferred to a formatted excel spreadsheet for mailing to our client, who is apparently too dimwitted to know how to cut-and-paste the data himself.

No big deal, really, except that she wants this all to be AUTOMATIC. One button form-to-excel.

No problem. I know how to use macros.

The difficulty is in getting the macro to wait long enough for Excel to open before it tries to send commands (using Sendkeys). Here's a brief outline to make the problem clear:

1) Access macro (AM) uses "transfer spreadsheet (export)" to send the query in question to a spreadsheet.

2) RunApp (Excel)

3) SendKeys("^t")

I set up a macro in Excel that does the actual work of the data transfer (using the table I created in step 1 and inserting its relevant data into a template -- simple looping there).

The Access macro works, mostly, and the excel macro works when I trigger it manually (with ctrl-t), but the problem is that step 3 of AM doesn't seem to have any effect. My guess is that, because it takes several seconds for Excel to finish opening, AM is sending the ctrl-t command before Excel is ready, and it just ignores the instruction, so the second macro won't start.

So -- is there a way to make my macro wait a few seconds, or wait until the RunApp is done before continuing?

I COULD just have it open Excel and make the USER hit Ctrl-T to start the transfer, but Boss-Lady won't like that. She really really wants it to be entirely automatic.

Any suggestions?

For the second argument for the Macro, which is "Wait", put Yes. The default right now is set to No.

Jack

Okay, that doesn't work, because the Wait argument determines if the macro waits AFTER sending the command, not BEFORE.

You should be using Excel automation via access vba. Macros just won't cut it here at all. And sendkeys should never be used, way too many bad things can happen when using them. The excel forum here is a great place for help with this kind of task.

HTH
Ken