suppose you are away for your vacation. You logged off your desktop. You closed your Microsoft Office software.At 9:00AM when you are away, a program can automatically open Microsoft Office and go to a server to get data and copy and paste it into a spreadsheet.How can we do to make this thing happen even if there is no one sitting in the desk?

Sounds like you want the equivalent of what would be a shell script and crontab on a *nix platform. Maybe a .bat file that scripted out your desired Office-related activities combined with CRON for windows (http://cronw.sourceforge.net/) to execute the script at the appropriate time?

Yes. Would you please let me know how to learn this skill? any good books or references?

start by looking at scheduled tasks and the microsoft knowledge base

You want to look at the "AT" commandBe also aware that Excel VBA has "events".Hint: when doing things with the Windows command line spaces can cause an issue unless you use " to mark the text as literal.You can set a piece of code to be called hours or days hence, or when a sheet is loaded.Or better, make the routine wake up every few minutes to see if there is data there, which will protect you against the data arriving at 9:01, and thus being missed by an over precise reading function.OnTimeis your friend here.Also, you can use the Shellcommand in VBA, which will start a program, and you can comibne this with timer and sheet loading events.A more industrial-strength approach is to remember that MS provides database drivers that let you write code in VBA, C++, C#, etc that will treat an Excel file as a database, so that you can write code that does not have to rely upon Excel being fired up.

QuoteOriginally posted by: DominicConnorYou want to look at the "AT" commandBe also aware that Excel VBA has "events".Hint: when doing things with the Windows command line spaces can cause an issue unless you use " to mark the text as literal.You can set a piece of code to be called hours or days hence, or when a sheet is loaded.Or better, make the routine wake up every few minutes to see if there is data there, which will protect you against the data arriving at 9:01, and thus being missed by an over precise reading function.OnTimeis your friend here.Also, you can use the Shellcommand in VBA, which will start a program, and you can comibne this with timer and sheet loading events.A more industrial-strength approach is to remember that MS provides database drivers that let you write code in VBA, C++, C#, etc that will treat an Excel file as a database, so that you can write code that does not have to rely upon Excel being fired up.Thanks.Are there good books guiding us related to shell commend in VBA? IT is very interesting!

Things to consider:1. Do you need Excel to do any special business logic/formatting of retrieved data? 2. What APIs are available to you to request data from the 'server'3. Mission critical?* If you are working with raw data -- best is to retrieve data to a plain data file (CSV, etc). This will reduce dependency on using Excel as a service (which until Office 2007, was never really supported). Excel can process CSV data files just as easy as XLS files without the overhead.* Consider going straight to the source, and let the 'service' itself generate the required datafiles to a shared location. This way - your desktop does not become an integral part of daily processing and this process can therefore be managable/supported. (Since anybody can reboot/switch off your desktop when you are away) Server Push vs Client Pull model.* Ever since MS got all security conscious - running scheduled tasks will be a background process (unless you are logged on) so diff security context need to be considered. Some components will fail if the security settings are not configured correctly, UI may not necessarily appear (if you are relying on that), and UNC paths (instead of mapped drives) may need to be used.* Your options to kick off an Excel process from your chosen scheduler include 1. Excel command line with a Workbook_Open() event, 2. COM-aware script (*.vbs, *.js etc). 3. You could take it to extreme and develop apps or true Windows Services in C# etc, but overkill for your current requirement.

You can do this using a local service using c#I would create a local service, using the office interrop, and a library called Quartz.NET which is actually a scheduler.Quite easy to do indeed, telle me if you need an example.

Local service will do you the job. Just to make sure it runs under "local system" account so that it does not require any user log-in. (If you set the service to automatic start, it will also work if the machine needs weekend reboot etc).Actually you don't need any external scheduler. Just put a timer inside your Windows service application.In interacting with Excel, you need to be careful about run-time exception as Excel does not like others to forcefully push data to a spreadsheet directly. But it can be handled. If you just need data, create a CSV or Excel-XML file directly in your application. By doing this, you don't need to deal with Excel at all. (BTW, in fact, you can create Excel functions etc in a CSV file which will actually work when you open it in Excel ;->)All the above can be done natively in C# easily, no need for any external library. (We have a similar application running for more than one year already - automatically fetching data from a remote server for a spread-sheet based curve builder.)

The Shell command simply runs a Command line, which is well documented.Be aware that it can be scripted with .CMD or .BAT files which are little most than a list of commands to be executed.