Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (2)

Resolved Orphan excel.exe process after creating it via oleobject.ConnectToNewObject("Excel.Application")

  1. Issue
0
Votes
Undo
  1. Jim Reese
    Jim Reese
  2. PowerBuilder
  3. Thursday, 25 July 2024 20:12 PM UTC

We have existing processes to export something to excel using OLE and Excel VBA commands, which do their job, and then exit. We have a call at the end to an external function EndTask to kill the excel.exe process for these.

We're developing a new process that is more interactive, exporting data from a data-entry datawindow with multiple detail rows, allowing the user to manipulate the data in the excel spreadsheet that was created during the download, and then re-importing the data and simulating manual entry using setrow/setcolumn/settext/accepttext in order to use pre-existing validation code. Development is actually proceeding nicely, with good performance.

One issue we're facing is we want the user to be able to open other workbooks from our newly created workbook/worksheet, in order to copy/paste data if they desire. as well as allow them to keep these open if desired after the upload process has completed.

If we omit the existing external function to kill the excel.exe process like the earlier processes do, then even after the user has closed all existing workbooks, and we have disconnected in PB, there is an orphan excel.exe process in the task manager. We've tested the following simple process, and the orphan still remains:

oleobject	lole_excel
lole_excel = create oleobject
lole_excel.ConnectToNewObject("Excel.Application")
lole_excel.visible = TRUE
lole_excel.disconnectobject( )
destroy lole_excel
return

With the .visible set to true, an empty Excel frame with no sheet is displayed. Closing it does not terminate the excel.exe process.

Without the .visible set to true, the user sees nothing, but the orphan excel.exe process remains.  Executing multiple times creates a new excel.exe process each time.

We were hoping that once all references to the process went away, then the process would close down, similar to how Excel does when initiated manually, but it seems since it is started via OLE, the only way to terminate is with the external EndTask function:

Function boolean EndTask(ulong hWnd, boolean fShutDown, boolean fForce) Library "user32.dll"

Calling EndTask will close all open Excel windows opened from the OLE instance when it executes, which is what we are trying to avoid.

Any suggestions?

  1. Excel
  2. OLE
  3. PowerBuilder
DLLS needed to export as XLSX?
Powerbuilder 2017 R3 1880 installation files
Responses (2)
Jim Reese
Jim Reese Accepted Answer Pending Moderation
  1. Tuesday, 6 August 2024 20:30 PM UTC
  2. PowerBuilder
  3. # 1

We haven't upgraded or applied the hotfix yet, but we have found what we think is a better approach than what we've been doing.

Rather than using ConnectToNewObject("Excel.Application"), we've tested the following:

result = myoleobject.ConnectToObject("","excel.application")

If result is 0, then an existing Excel.exe is already running, and we can use that. If it fails, returning something else, then we can startup Excel with a run() statement rather than by using ConnectToNewObject():

result = registryget("HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\excel.exe", "Path", ls_excel_path)
result = run(ls_excel_path + 'EXCEL.EXE' )

If the run statement succeeds, we can retry the ConnectToObject() after Excel has been started.

This prevents the multiple orphan EXCEL.EXE processes we were seeing with the /automation -Embedding on the Command line when using myoleobject.ConnectToNewObject("Excel.Application")

Comment
Load more comments
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Wednesday, 7 August 2024 02:47 AM UTC
which version of office are you using?
  1. Helpful
Jim Reese
  1. Jim Reese
  2. Wednesday, 7 August 2024 12:44 PM UTC
That is a good question, if I execute a command at the application level, then yes, such as changing the visibility (I believe you can set visibility of a sheet or the application, but not a workbook, and a sheet only if there are more than 1 in the workbook, someone can correct me if I'm wrong). But in our case, we create an oleobject pointing to the workbook we create, and then work from there, so any other workbooks the user has opened aren't affected unless we let the user, via a PB window dialog, switch to a different workbook/worksheet, and we then point to that one with our oleobject. A lot of our existing code was referencing Activeworkbook or ActiveSheet, or was written to assume the active object, we just needed to adjust that to use our instance oleobjects that represent the workbook and worksheet we intend to operate against.



Using Office 365 MSO version 2405 Build 16.0.17628.20006 64 bit
  1. Helpful
Jim Reese
  1. Jim Reese
  2. Wednesday, 7 August 2024 16:03 PM UTC
Additional note, as this is a work in progress, It seems I need to pause the PB app in between the run() command and the ConnectToObject. I've tried looping on yield() and adding sleep(1) just to give Excel time to complete opening to no avail, the only thing that is working so far is to pop up a messagebox informing the user that we are attempting to start Excel since it isn't currently running. I didn't see this at first as I was running in the debugger.
  1. Helpful
There are no comments made yet.
Ken Guo @Appeon
Ken Guo @Appeon Accepted Answer Pending Moderation
  1. Friday, 26 July 2024 03:08 AM UTC
  2. PowerBuilder
  3. # 2

Hi Jim,

When I execute the following code locally, the Excel process does not remain, it disappears automatically.

oleobject lole_excel
lole_excel = create oleobject
lole_excel.ConnectToNewObject("Excel.Application")
lole_excel.visible = TRUE
lole_excel.disconnectobject( )
destroy lole_excel
return

If you open an Excel file, execute Close() and then execute DisconnectObject(), then you can quit the Excel process. For example:

oleobject lole_excel,lo_ExcelSheet1,lo_ExcelSheet2
lole_excel = create oleobject
lole_excel.ConnectToNewObject("Excel.Application")
lo_ExcelSheet1 = lole_excel.WorkBooks.open('c:\1.xlsx')
lo_ExcelSheet2 = lole_excel.WorkBooks.open('c:\2.xlsx')
lole_excel.visible = TRUE
lo_ExcelSheet1.Close()
lo_ExcelSheet2.Close()
lole_excel.disconnectobject( )
destroy lole_excel
Return

Of course, you can also use Quit() directly to quit the Excel process. For example:

oleobject lole_excel,lo_ExcelSheet1,lo_ExcelSheet2
lole_excel = create oleobject
lole_excel.ConnectToNewObject("Excel.Application")
lo_ExcelSheet1 = lole_excel.WorkBooks.open('c:\1.xlsx')
lo_ExcelSheet2 = lole_excel.WorkBooks.open('c:\2.xlsx')
lole_excel.visible = TRUE
lole_excel.Application.Quit()
lole_excel.disconnectobject( )
destroy lole_excel
Return

For code rigor, I recommend that you use both Close() and Quit() before DisconnectObject


Regards,
Ken

Comment
Jim Reese
  1. Jim Reese
  2. Friday, 26 July 2024 13:25 PM UTC
Ken, thanks for replying. Unfortunately, my experience is not the same as yours. When I run your first example verbatim, the empty Excel frame displays. When I close it and check the task manager, in the Details tab there is still an EXCEL.EXE process running. The Command line for that process shows the path to the executable, followed by /automation -Embedding.

I've tried the Quit command, with no difference, other than it closes the visible Excel frame, but the EXCEL.EXE process remains.



BTW, we are running PB 2022 R2 build 2828, Runtime 22.1.0.2819



Regards,

Jim
  1. Helpful
Ken Guo @Appeon
  1. Ken Guo @Appeon
  2. Tuesday, 30 July 2024 00:24 AM UTC
Hi Jim,



Thank you for letting me know that you are using the 2022 R2 Build 2819. This version does have an OLE bug, and you can download the following hotfix to resolve it:

https://file.appeon.com/download/2022/R2/HotFix/build_2819/PowerBuilder_Hotfix.zip



Alternatively, you can upgrade directly to PB 2022 R3, which also fixes this issue.



Regards,

Ken
  1. Helpful 1
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.
We use cookies which are necessary for the proper functioning of our websites. We also use cookies to analyze our traffic, improve your experience and provide social media features. If you continue to use this site, you consent to our use of cookies.