Excel Scheduled Imports 1

[baycolor]

Hi,

I have Excel 2003 with SP2 running on XP.

I have a workbook that imports data from an external database. I refresh the data in the workbook by opening the workbook and running an import query. I'm wondering if there is an automated way to do this.

Is there a way to write a macro or something that could be kicked off via a Windows Scheduled task that would open the workbook and refresh/import the latest data from the database. After that I would want some macros to run and manipulate the data around and finally close the workbook.

Please note my Excel skill level is not that high I basically go in and create some formulas now and then. Although I am a programmer and if I know its possible to create some macros that would do something like this I could probably work it out if pointed in the correct direction.

Thanks in advance for your help.

 

[Griffyn]

get started with an Autpen macro. This will be automatically launched every time the workbook is opened. Pop some code to refresh your queries and do whatever else in it.

 

[baycolor]

Got it thanks. I've heard of auto open macros but is there a way to have one of these work with a scheduled windows task - so I don't have to open the workbook, wait for the macro to do its thing and then close the workbook?

Thanks
Mike

 

[Griffyn]

sure, if you're using Windows NT/2000/XP, click start, Run and type 'cmd' (no quotes) and press Enter

then at the command-line type:

AT 14:00 /interactive /every:M,T,W,Th,F "c:\program files\microsoft office\office\excel.exe c:\mydocs\mybook.xls"

you must include the double talking marks around the entire command-line to run. change 14:00 to whatever 24-hour time you want. Add or delete from the days (you can include S or Su for Saturday or Sunday. Change file paths and document names as necessary.

This will only work for once a day. To have more than once a day, simply type as many AT commands as you require with different times.

use <tt>AT /?</tt> to show help on using AT, and use <tt>AT</tt> on it's own to display the currently set tasks.

The only other issue you may have is with the task scheduler not having enough rights - for example accessing data on any computer except your own in this way will probably fail unless you change the login for the Task Scheduler. To do this, go to Start, Control Panel, Administrative Tools, Services.

Scroll down to Task Scheduler and double-click. Click the Log On tab at the top, and change the login user and password to be your own for your computer. Stop and Restart the service to make sure it can start ok with these new credentials.

 

[Griffyn]

bah, wrong bbcodes.

use <tt>AT /?</tt> to show help on using AT, and use <tt>AT</tt> on it's own to display the currently set tasks.

should be:
use [tt]AT /?[/tt] to show help on using AT, and use [tt]AT[/tt] on it's own to display the currently set tasks.

 

[baycolor]

That's great. Thank you very much!!!