Automate an Excel spreadsheet during an installation

[ewallig]

Hi,

Developer of a spreadsheet-based application now needs to upgrade his "database" structure. As part of the upgrade, he needs to capture all of the currently stored data in the previous version of his app. To do this, he's built another spreadsheet with a bunch of macros in it. This spreadsheet needs to be launched and then closed once it has completed its task (get the data and then save the data as a text file).

The installer that I built for him now needs to do the following:

- Launch the macro-loaded spreadsheet
- Close the macro-loaded spreadsheet once it has created its text file.
- Proceed with the installation of the rest of the upgraded files.

To tackle this, I wrote a small script that checks for the presence of the text file and loops until it exists. In the installer, I run this script (actually a script inside of an exe wrapper) after launching the macro-loaded spreadsheet. Once it detects the text file, it sends various keystrokes to click a dialog box that pops up and then close the spreadsheet. Once the macro-loaded spreadsheet closes, the installation can proceed.

The helper script works fine if I manually run the macro-loaded spreadsheet and then run the script; the text file is created and the macro-loaded spreadsheet closes w/o issue. However, I've run into some difficulties when trying to do this via the NSIS installer. If I use an Exec command to try to open the macro-loaded spreadsheet, there is no interaction w/ the desktop which the script needs to do the keystrokes. If I use an ExecShell open, then the installer stalls once the macro-loaded spreadsheet opens up - no further installer-related activity occurs until I manually click the button on the dialog box.

Anyone have any suggestions on how to deal with this - the developer really wants it to be automatic and is giving me a bit of grief over it.


Thanks

[kichik]

You can try using Excel's automation interface to invisibly load the spreadsheet. It'd also allow you greater control with direct API instead of keystrokes. You can use the System plug-in to do that or create a simple plug-in for the task.

[ewallig]

Thanks for the info - can you point me at some documentation or examples?

[kichik]

MSDN should contain all the information you need. Sites like code project might contain more examples. A simple search for "Excel" comes up with many results.