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?