StartExcel utility - Automatic (periodical) report export
Automatic export allows to periodically (or when an event occurs) activate a workbook (report) saved on hard disk as an .xls file, to evaluate it and then to save active sheet as a new .xls file in defined directory.
The automatic export can be executed by means of the utility StartExcel (the file startexcel.exe placed in the subdirectory Bin of the installation directory ) run with the following parameters:
specifically for automatic export:
The utility StartExcel runs the MS Excel. If the parameter /I is defined, the MS Excel
will be opened as visible. If the parameter /Q is entered, any errors will be written into
the file startexcel.log and the utility StartExcel will be terminated.
Then a workbook specified by the parameter fullXlsPath will be opened and it will call the macro StartParams with the parameter EXP. The macro must be implemented in the workbook (if the workbook has been created from the template D2000_WorkBook.xlt, the macro is already included in it).
The workbook is automatically logged on to the D2000 system according to logon parameters (USER_NAME, PASSWORD, SERVER), it evaluates itself and a saves a copy of the active sheet into a new file defined, whose name is defined in the cell EXPORT_FILE_NAME. By default, the cell is filled with an expression that adds the date and time to the workbook name. In the application, it is necessary to change the directory destined for exported reports. By default, it is set to C:\Reports.
Report evaluation is executed by setting the times of the cells D2000_BT a D2000_ET to the current time.
Periodic (or using any start trigger) export can be performed using ESL script as follows:
- create an object of Event type, that is to be started when there is needed to create an export (e.g. periodically)
- source code of the event:
TEXT _report = "c:\Reports\test1.xls"
; report export with 50 seconds timeout
_ret := RUN "startexcel.exe" SYNC " " + _report + " StartParams EXP" TIMEOUT 50
IF _ret\VLD THEN
IF _ret = _ERR_TIME_OUT THEN
_txtMsg := "TIMEOUT for report export"
_txtMsg := "Export ExitCode = " + %IToStr(_ret)
IF _ret = 0 THEN
_txtMsg := _txtMsg + " OK"
IF _ret = 1 THEN
_txtMsg := _txtMsg + " Init Ole Error"
IF _ret = 2 THEN
_txtMsg := _txtMsg + " Start Excel Error"
IF _ret = 3 THEN
_txtMsg := _txtMsg + " Open Report Error"
IF _ret = 4 THEN
_txtMsg := _txtMsg + " Run Macro Error"
_txtMsg := _txtMsg + " Unknown Error"
_txtMsg := "Attempt to run startexcel.exe has failed"
LOG _txtMsg, SysTime
Automatic export run can be set by using the system service Scheduled Tasks (Windows NT or 2000). The service allows to perform (run) periodically given program.
Required setting of the parameters for automatic export:
Important cells are the cells B1, B2 and B3. The value of the cell B5 must be FALSE, the cell B11 represents a filename, which the export will be saved into. If the files already exists, they will be overwritten.