[RESOLVED] Help please with automating Excel & Access jobs

[doasidont]

We are trying to automate, via Scheduled Tasks, the starting of Excel and Access. The Excel workbook contains a macro that runs when the workbook is opened. It downloads data, formats it and saves it in a folder as text. When finished, Excel shuts down. We have this much working.

We now want to start Access and open a particular database. When Access starts, a macro should start automatically that imports the text data created by the previously run Excel workbook. Access should then shut down.

What is the best way to do this via scheduled tasks so that Access does not start before the Excel program finishes?

Thanks.

[westconn1]

you could automate access from excel?
either keep excel open until the access tasks finish then close excel or pass control to some access procedure and close excel, close access from its own code when finished
alternatively you could use ADO to manipulate the database tables from excel, without opening access

[doasidont]

Thanks for quick reply. Because we want the option to run each process manually whenever wanted, I'd rather not mess with the VBA code in either the Excel download routine or the Access import routine. I need a way to delay starting Access until after the Excel routine finishes, and then launch the import routine independently afterwards. If I rename the import routine 'AutoExec', then every time someone opens the database, the import runs unless they depress 'shift' key, which I know will be forgotten sometimes. If a .vbs file would allow me to start import separately from opening the db, can you offer an example or point me to where I can get one?

Thanks.

[Siddharth Rout]

Here is another logic

Create a very small program in vb6 which

1) Opens Excel. When Excel Finishes, It will set off a Boolean variable to "True"
2) The moment the value of the variable is true the small program will prompt if you want to launch Access. It will wait for your prompt for say 10 mins after which it will automatically launch Access.

Sid

[doasidont]

Getting a little wrapped around axle. I know this is easy but can't get it right. How do I start a .bat file (that contains everything needed including switches and other) from Excel using VBA?

Thanks.

[Siddharth Rout]

What do you exactly want to do?

If what I think you want to do then Bat file is a bad option

Sid

[doasidont]

I want to start a .bat file via a Windows Scheduled Task. This .bat file contains DOS commands that start other .bat files; e.g. start Excel /x "C:\Reports\Sales Data\SFTP.xlsm". There are multiple SFTP workbooks, one for each department. The /x switch allows me to examine the command line to see if SFTP.xlsm was started via .bat file or simply opened by a user. I need to know this because I don't want any Excel messages displayed that require a user response if the workbook was opened by Windows as a scheduled task.

The SFTP.xlsm workbook, when opened, automatically runs a macro that downloads data and places that data in a file. Before exiting, an 'Application.Quit' is performed and another subroutine starts that opens the Access database and runs a macro; e.g. start "C:\Program Files\access97\Microsoft Office\Office\MSACCESS.EXE" "C:\Reports\Sales Data\Database.accdb" /Excl /nostartup /x AutoTask where 'AutoTask' is the macro that imports the downloaded data.

In a nut shell, I want to schedule a job stream that runs at a certain time each day. This job stream starts multiple Excel instances, each downloading some data, exiting, and opening a separtate Access database. When opened, Access imports the data, exits, and then sends an email advising of the status. I want to keep the VBA code in the Excel and Access modules separate.

I would sure appreciate some suggestion on a clean way to do this.