 |
SoftTree Technologies
Technical Support Forums
|
|
Author |
Message |
siupang
Joined: 16 Jan 2008 Posts: 9 Country: Hong Kong |
|
No response on excel |
|
Hi,
I have a job which call a application (written in VB6.0) to open a excel and fill in some formula to download data from DDE server. The job is simply call as "App.exe task11" task11 is the paramter pass-in to App.exe.
It works fine in dos command prompt. But if i run it in 24x7 Scheduler, the application still can open a excel worksheet, and after it filled the first formula to a cell, the excel is hang. I have to terminate the application manually.
Please advise. Thanks
|
|
Wed Jan 16, 2008 4:19 am |
|
 |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7951
|
|
|
|
The problem is likely in your Excel application. All this stuff with Excel DDE frequency doesn't work if the client or server application is not having an input focus, which rarely happens in automated jobs. Automated jobs are used to run processes unattended in the background or completely invisible.
Consider rewriting the job to call Excel directly. 24x7 supports full range of DDE functions (an example is available in the on-line help, search for DDE, click the Example button on the help toolbar) as well as it supports Excel COM automation via VBScript jobs (an example provided with the installed sample job database file).
|
|
Wed Jan 16, 2008 9:51 am |
|
 |
siupang
Joined: 16 Jan 2008 Posts: 9 Country: Hong Kong |
|
|
|
Hi,
Thanks for your reply.
But i it works fine for the command "App.exe task11" in dos prompt, and even in my old scheduler program. This program only happens on 24X7 scheduler only.
|
|
Wed Jan 16, 2008 8:58 pm |
|
 |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7951
|
|
|
|
If you run the process in the foreground, from DOS prompt it works, but if you run in the background, it doesn't. The deficiency is not in the scheduler, but in the Excel. If you want, you can log a case with Microsoft, but I doubt they will do anything about it. In the best case they will suggest to use Excel COM automation instead of DDE, and in fact I have already suggested that too, or use direct 24x7 to Excel DDE channel without intermediate processes.
By the way, you can try scheduling "cmd /C App.exe task11" command – this will open DOS prompt and run from where the command processor will start your Application with task11 parameter and check where it gets you.
|
|
Wed Jan 16, 2008 11:03 pm |
|
 |
siupang
Joined: 16 Jan 2008 Posts: 9 Country: Hong Kong |
|
|
|
Hi,
The "cmd /C App.exe task11" command is not work. I have to run it in "Test Mode" in 24X7, so it should be running in foreground, am i right? And it is not possible to re-write all of our jobs (>40 jobs) into the method you suggested due to using 24X7.
But i got some findings, that the excel will show "not responding" when the App.exe fill in the first formula, if i kill the App.exe, the excel resume normal and it can download the data from DDE server. Any idea on this?
|
|
Thu Jan 17, 2008 9:50 pm |
|
 |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7951
|
|
|
|
You may consider using JAL script type jobs in 24x7 that will run your application and explicitly set focus to the application after it started. While this is definitely doable, the method itself is not reliable, just as not reliable the method you are using now. Think for a second what is going to happen if you have 2 concurrently running jobs each requiring running in the foreground. How are they going to fight for the input focus?
For implementation ideas, try the following example
 |
 |
Dim process_id, number
Dim window_handle, number
// Use Excel for demonstration
// Run Excel
Run( "C:\\Program Files\\Microsoft Office\\Office\\excel.exe", "", process_id )
// Wait 5 seconds for the Excel to open
Wait( 5 )
// Get handle of the main Excel window
ProcessGetWindow( process_id, window_handle )
// place it into foreground
WindowActivate( window_handle )
// do something, for example, send keystrokes to make it open first found Excel file
SendKeys( "{ALT}FO*.xls{ENTER}{SHIFT}{TAB}{ENTER}" ) |
|
|
Thu Jan 17, 2008 10:33 pm |
|
 |
siupang
Joined: 16 Jan 2008 Posts: 9 Country: Hong Kong |
|
|
|
I have tried to create a VBscript (list below) to open an excel and fill in the formula, but when it goto the line "objXL.Cells(1,2).Value = "=BLP|H!'AMP AU Equity,[PX_BID],ST=20080117 END=20080117PT=1 QT=P'", the excel is hang up, and 24X7 is utilize more 50% of CPU. When i stop the job in 24X7, the excel resume normal and download the data from DDE server without any problem.
It seems the problem still exist even calling excel directly.
Sub Main( )
' ********************************************************************************
' *
' * Excel Sample
' *
Dim objXL
Dim objXLchart
Dim intRotate
Set objXL = CreateObject("Excel.Application")
objXL.Workbooks.Add
objXL.Visible = True
objXL.Cells(1,1).Value = 5
objXL.Cells(1,2).Value = "=BLP|H!'AMP AU Equity,[PX_BID],ST=20080117 END=20080117PT=1 QT=P'"
objXL.Cells(1,3).Value = "=BLP|H!'ANZ AU Equity,[PX_BID],ST=20080117 END=20080117PT=1 QT=P'"
objXL.application.Quit
End Sub
|
|
Fri Jan 18, 2008 1:45 am |
|
 |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7951
|
|
|
|
You have an error in the value. Any value beginning with = sign means to Excel that you are entering a formula not a value. In your case you are trying to enter a formula containing a reference to non-existing worksheet BLP. That's why Excel hangs - the situation is so awkward that Excel just fails to abort with an error.
Change your code as below
 |
 |
Sub Main( )
' ********************************************************************************
' *
' * Excel Sample
' *
Dim objXL
Dim objXLchart
Dim intRotate
Set objXL = CreateObject("Excel.Application")
objXL.Workbooks.Add
objXL.Visible = True
objXL.Cells(1,1).Value = 5
objXL.Cells(1,2).Value = "=Sheet2!A1+1"
objXL.Cells(1,3).Value = "'=test value"
objXL.application.Quit
End Sub
|
|
|
Fri Jan 18, 2008 2:08 am |
|
 |
siupang
Joined: 16 Jan 2008 Posts: 9 Country: Hong Kong |
|
|
|
"BLP" is an excel add-in function.
When i stop the job in 24X7, the excel which is created by the job resume normal, and the data can be download by the "BLP" formula.
And i also try to open a new excel directly and input the same formula, the formula performs normal also.
|
|
Fri Jan 18, 2008 3:03 am |
|
 |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7951
|
|
|
|
I think you are looking in a wrong place. You are having problems in Excel, not in 24x7. Try saving your VBScript as a .VBS file and running it as an external script file. If the result is the same, that would be a hard proof of Excel problem. To run an external .VBS file just schedule it as a command line job. 24x7 will automatically launch cscript.exe (or whatever is registered in the system as a default application for files with .VBS file extensions) and make it run the file specified on the command line.
By the way, if a job like that get hung, don't kill 24x7, kill Excel and the job will resume or terminate with an error. When a job hangs, it is 24x7 that is waiting for a job/script to complete, not the other way around.
|
|
Fri Jan 18, 2008 9:56 am |
|
 |
siupang
Joined: 16 Jan 2008 Posts: 9 Country: Hong Kong |
|
|
|
I have tried to implement the script in a excel button (the script run after the button is clicked), it works perfectly, so i don't think that is a excel problem.
And the excel script is hung only if it is run under 24X7, and the excel script work fine in Dos command prompt, Windows scheduler, other third party scheduler or even directly in the excel.
|
|
Tue Jan 22, 2008 2:45 am |
|
 |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7951
|
|
|
|
Have you tried running it as an external VBS file?
By the way, it is Windows, not 24x7, that runs your script. 24x7 simply invokes VBS scripting engine interface and passes the script to execute.
Next time when the job hangs, kill Excel, not 24x7 and you will see that the job will attempt to continue running and fail because it cannot continue. This will prove to you that it is not 24x7 that hangs. 24x7 simply waits for your job script to finish and it is Excel that is holding that.
|
|
Tue Jan 22, 2008 3:26 am |
|
 |
siupang
Joined: 16 Jan 2008 Posts: 9 Country: Hong Kong |
|
|
|
I have tried running it as an external VBS (main.vbs) file in 24X7, the excel is still hang.
But if i run "cscript.exe main.vbs", the script completed successfully. If it is a excel problem, how come the scripts cannot be run in 24X7 but it can run with scsript.exe directly?
|
|
Tue Jan 22, 2008 4:55 am |
|
 |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7951
|
|
|
|
Your main.vbs is run using the same cscript. So you got now a hard proof that your application/code only works when Excel has an input focus and this issue has nothing to do with 24x7.
Excel gets focus - it works;, Excel doesn't get focus - it doesn't work.
My suggestion for the next step is to add the necessary code to the job code to make Excel popup in the foreground with an input focus before changing cell values.
objXL.Application.Activate
and if this is not enough, add some "wait" to wait until Excel fully loads and activates all add-ons.
|
|
Tue Jan 22, 2008 12:36 pm |
|
 |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7951
|
|
|
|
While the suggestion in the previous response may help you to get the process going, you will still have a business problem with the process automation. You can only run it when somebody is logged in to the system. I am also not sure about effects of screensavers and server/workstation screen locks, you should check these effects too.
|
|
Tue Jan 22, 2008 12:39 pm |
|
 |
|
|
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
|
|
|