Porovnávané verzie

Kľúč

  • Tento riadok sa pridal
  • Riadok je odstránený.
  • Formátovanie sa zmenilo.

...


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:

Declarationstartexcel [/Q] [/I] [/D] fullXlsPath [StartMakro [ param1 .. paramN ]]
specifically for automatic export:
startexcel [/Q] [/I] [/D] fullXlsPath StartParams EXP
Parameters


/QEnables writing of possible errors into the log file (startexcel.log) and terminating the utility StartExcel if any errors occur.
/IRuns the MS Excel as visible. Optional parameter.
This parameter is not recommended to use on the operating systems MS Server 2008, MS Vista and Windows 7 if the parameter is started as service (e.g. SELF.EVH started as service).
/DEnables writing the debug information including the list of parameters, which were entered when its starting, into log file.
fullXlsPathFull path to report (workbook).
StartMacroOptional name of the macro in the document, that is to be executed.
param1 .. paramNOptional parameters of the macro StartMacro.
StartParamsName of macro in the report, that will execute its data export.
EXPParameter of the macro StartParamsspecifying that export of data is to be performed.


DescriptionThe 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.

Note:
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:

INT _ret
TEXT _txtMsg
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"
ELSE
_txtMsg := "Export ExitCode = " + %IToStr(_ret)
IF _ret = 0 THEN
  _txtMsg := _txtMsg + " OK"
ELSE
IF _ret = 1 THEN
  _txtMsg := _txtMsg + " Init Ole Error"
ELSE
IF _ret = 2 THEN
  _txtMsg := _txtMsg + " Start Excel Error"
ELSE
IF _ret = 3 THEN
  _txtMsg := _txtMsg + " Open Report Error"
ELSE
IF _ret = 4 THEN
  _txtMsg := _txtMsg + " Run Macro Error"
ELSE
  _txtMsg := _txtMsg + " Unknown Error"
ENDIF
ENDIF
ENDIF
ENDIF
ENDIF
ENDIF
ELSE
  _txtMsg := "Attempt to run startexcel.exe has failed"
ENDIF

LOG _txtMsg, SysTime
END

When attempting to export any data, the return value of the action RUN is handled, along with writing the export status to the log database.

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.


Starting Excel from the D2000 Event process running as a service

For correct functioning, it is necessary to create the following directories in Windows:

  • 32-bit Excel on 32-bit Windows or 64-bit Excel on 64-bit Windows:
    mkdir C:\Windows\System32\config\systemprofile\Desktop
    mkdir C:\Windows\System32\config\systemprofile\Documents
  • 64-bit Excel on 64-bit Windows:
    mkdir C:\Windows\SysWOW64\config\systemprofile\Desktop
    mkdir C:\Windows\SysWOW64\config\systemprofile\Documents

If these directories do not exist, StartExcel returns error 3 (ERR_OPEN_DOC) and reports a document opening error in the StartExcel.log log, e.g.:

[07-12-2023 08:14:47][INF][13428]   Start excel starting...
[07-12-2023 08:14:47][INF][13428]   Document name is 'd:\D2000\D2000_APP\Demo\Temp\Template.xls'
[07-12-2023 08:14:47][INF][13428]   CLSIDFromProgID SUCCESS
[07-12-2023 08:14:47][INF][13428]   CoCreateInstance SUCCESS
[07-12-2023 08:14:47][INF][13428]   StartExcel DONE
[07-12-2023 08:14:47][INF][13428]   Excel is running as hidden...
[07-12-2023 08:14:47][ERR][13428]   Error opening document d:\D2000\D2000_APP\Demo\Temp\Template.xls
[07-12-2023 08:14:47][INF][13428]   Uninit OLE...


In addition, it is recommended to run the D2000 Event process under a dedicated Windows user (e.g. report). In order for macros to work, it is necessary to log in as this user (report), start Excel and in Options go to the Trust Center item, click on Trust Center Settings and in the Macro Settings tab select Enable all macros.

Without macros enabled, StartExcel will return error 4 (ERR_RUN_MACRO) and in the StartExcel.log report a macro run error:

[07-12-2023 08:39:04][INF][8604]   Start excel starting...
[07-12-2023 08:39:04][INF][8604]   Document name is 'd:\D2000\D2000_APP\Demo\Temp\Template.xls'
[07-12-2023 08:39:04][INF][8604]   CLSIDFromProgID SUCCESS
[07-12-2023 08:39:05][INF][8604]   CoCreateInstance SUCCESS
[07-12-2023 08:39:05][INF][8604]   StartExcel DONE
[07-12-2023 08:39:05][INF][8604]   Excel is running as hidden...
[07-12-2023 08:39:05][INF][8604]   d:\D2000\D2000_APP\Demo\Temp\Template.xls is opening...!
[07-12-2023 08:39:05][INF][8604]   Starting macro...
[07-12-2023 08:39:05][ERR][8604]   Error run macro!
[07-12-2023 08:39:05][INF][8604]   Closing document...
[07-12-2023 08:39:10][INF][8604]   Uninit OLE... 
    


Required setting of the parameters for automatic export:

...

Required parametersImage Modified

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.