PB application closes when we close OLE Object (Excel)

View Replies (4)

Resolved PB application closes when we close OLE Object (Excel)

Issue

Votes

Undo

Sivaprakash BKR
PowerBuilder
Monday, 5 April 2021 07:07 AM UTC

Hello,

PB 2017R3

I use the following code to import data from Excel to Datawindow.

*********************************************

OLEObject xlapp , xlsub

dw_barcode.Reset()
dw_barcode.SetTransObject(Sqlca)

ll_sheet = 1
ls_filename = Trim(sle_filename.Text)
ls_ext = Mid(ls_filename, LastPos(ls_filename, '.'))

TRY

xlApp = Create OLEObject

ll_ret = xlApp.ConnectToNewObject( "Excel.Sheet" )
If ll_ret < 0 then
MessageBox("Connect to Excel Failed !", String(ll_ret))
lb_proceed = False
Return
End If

xlApp.Application.Workbooks.Open(ls_filename)
xlsub = xlapp.Application.ActiveWorkbook.Worksheets[ll_sheet]

ll_col_count = 14 // Fixed extra columns is there for our purpose

ll_max_rows = Xlsub.UsedRange.Rows.Count
ll_max_columns = Xlsub.UsedRange.Columns.Count

ll_cnt = 2
SetPointer(HourGlass!)
If lb_proceed = True Then
htb_1.Visible = True
dw_barcode.SetReDraw(False)
For ll_cnt = 2 to ll_max_rows
htb_1.Position = ll_cnt
If IsNull(Mid(Trim(String(xlsub.cells[ll_cnt, 1].value)),1,20)) = False and Trim(Mid(Trim(String(xlsub.cells[ll_cnt, 1].value)),1,20)) <> '' Then
ll_row = dw_barcode.InsertRow(0)
dw_barcode.SetItem(ll_row, 'usercode', Mid(Trim(String(xlsub.cells[ll_cnt, 1].value)),1,20))
dw_barcode.SetItem(ll_row, 'itemname', Mid(Trim(String(xlsub.cells[ll_cnt, 2].value)),1,80))
dw_barcode.SetItem(ll_row, 'oldpartno', Mid(Trim(String(xlsub.cells[ll_cnt, 3].value)),1,20))
dw_barcode.SetItem(ll_row, 'largecategory', Mid(Trim(String(xlsub.cells[ll_cnt, 4].value)),1,10))
dw_barcode.SetItem(ll_row, 'largecategoryname', Mid(Trim(String(xlsub.cells[ll_cnt, 5].value)),1,50))
dw_barcode.SetItem(ll_row, 'colour', Mid(Trim(String(xlsub.cells[ll_cnt, 6].value)),1,20))
dw_barcode.SetItem(ll_row, 'moq', Mid(Trim(String(xlsub.cells[ll_cnt, 7].value)),1,10))

dw_barcode.SetItem(ll_row, 'mrp', Dec(Trim(String(xlsub.cells[ll_cnt, 8].value))))
dw_barcode.SetItem(ll_row, 'ndp', Dec(Trim(String(xlsub.cells[ll_cnt, 9].value))))
dw_barcode.SetItem(ll_row, 'hsn', Mid(Trim(String(xlsub.cells[ll_cnt, 10].value)),1,20))
dw_barcode.SetItem(ll_row, 'sgstrate', Dec(Trim(String(xlsub.cells[ll_cnt, 11].value))))
dw_barcode.SetItem(ll_row, 'cgstrate', Dec(Trim(String(xlsub.cells[ll_cnt, 12].value))))
dw_barcode.SetItem(ll_row, 'igstrate', Dec(Trim(String(xlsub.cells[ll_cnt, 13].value))))
dw_barcode.SetItem(ll_row, 'cessrate', Dec(Trim(String(xlsub.cells[ll_cnt, 14].value))))
dw_barcode.SetItem(ll_row, 'selection', '')
End If
Next
dw_barcode.SetReDraw(True)
Else
MessageBox('Elifa', 'Cannot Proceed')
Return
End If

SetPointer(Arrow!)
XlApp.Application.Workbooks.close()
CATCH ( runtimeerror lo_rte)
MessageBox('Elifa', "MS Excel api runtime error")
FINALLY
// Quit
IF (IsValid(xlapp)) THEN
xlapp.application.quit() // This is the line which closed the PB Application also
xlapp.DisconnectObject()
END IF
dw_barcode.SetReDraw(True)
Destroy xlsub
Destroy xlapp

END TRY
********************************************

The same code works find in PB 11.5. Only IN PB 2017R3 I get this issue.

Any workaround or solution to this issue ?

Happiness Always
BKR Sivaprakash

Accepted Answer

Miguel Leeuwe Accepted Answer Pending Moderation

Monday, 5 April 2021 15:10 PM UTC
PowerBuilder
# Permalink

Hi Sivaprakash,

Mark and John are absolutely right. I hadn't seen you connect to Excel by not using Excel.Application.

This little bit of sample code works on my PC (including the Quit() ). Also (I think it was Mark who mentioned it?), it might be possible that some kind of "Alert" is trying to pop up when closing. To avoid this use displayAlerts = false, or save before closing if anything needs to be saved.

oleobject excel
string ls_addr
int li_rtn 

ls_addr = "C:\Users\miguel.RSL\Desktop\my.xlsx"
excel = create OLEObject 
li_rtn = excel.ConnectToNewObject("Excel.Application") 
IF li_rtn <> 0 THEN 
	 MessageBox('Excel error','can not run Excel Program')
END IF 

try
	excel.WorkBooks.Open(ls_addr) 
catch (oleruntimeerror oleErr)
	Messagebox("Error opening Excel", oleErr.GetMessage())
end try

// avoid being asked for anything:
excel.Displayalerts = FALSE

excel.workbooks.close()
excel.quit()
excel.disconnectobject()
DESTROY excel
GarbageCollect()

Comment

Load more comments

John Fauss
Tuesday, 6 April 2021 13:30 PM UTC

You're welcome, Miguel. I always appreciate your input and assistance. Mark, your responses are always insightful and helpful. Nice team effort!

Glad to know your issue is answered, Sivaprakash! Please mark this issue as resolved? Thanks!

Helpful 0

Miguel Leeuwe
Tuesday, 6 April 2021 13:35 PM UTC

Ha, same feeling I got on this one (about team effort).

Helpful 0

Mark Goldsmith
Tuesday, 6 April 2021 14:10 PM UTC

Thanks very much for the kind words guys...I feel the same way!!

Helpful 0

There are no comments made yet.

Responses (4)

Miguel Leeuwe Accepted Answer Pending Moderation

Monday, 5 April 2021 09:52 AM UTC
PowerBuilder
# 1

Hi,

First of all: Try using the "quit" command without the word "application".
Then, at my company:
When using Word, the "quit" command seems to work fine. For excel sheets we don't use it, since it also seems to blow up.

You say it works fine for pb 11.5. Are you using the same version of Office when running on pb 2017R3?

What we did, is simply get rid of the calls to "quit", we just do the following:

loo_excel.workbooks.close()
loo_excel.disconnectobject()
DESTROY loo_excel

Maybe it's a good idea to do also a "GarbageCollect()" followed by a 'Yield()' and (not sure which order is the best one).

regards

Comment

Sivaprakash BKR
Monday, 5 April 2021 12:52 PM UTC

Thanks Miguel,

1. Using xlapp.quit instead of xlapp.application.quit produced the following error

Name not found calling external object function quit at line 152 in ue_read_file event of object w_xls_import_spare_parts.

2. Commenting out that line xlapp.application.quit is working fine. Any side effects?

3. The code was working with With PB 11.5 and Excel 97-2003 format. It was developed few years back. So far we haven't tried with Windows 10 and later office (Excel) file.

4. Current development is done in Windows 10 and excel 2019 with PB 2017 R3. Here we face this issue.

So continuing the development with that line commented out.

Helpful 0

Miguel Leeuwe
Monday, 5 April 2021 13:27 PM UTC

Hi,

Yw, great! Yes, I think there's no side effects (at least I haven't encountered any), as the object is destroyed eventually. Also, I think the problem might be more related to the newer Office version and not so much the newer powerbuilder version. In the newer office versions, a significant change was that multiple sheets are now opened on different windows in not on the "same mdi frame window" used in previous versions of Office.

Maybe an interesting test would be to put the open en close of the sheets in a loop with a 1000 iterations and see how memory behaves.

regards

Helpful 0

There are no comments made yet.

John Fauss Accepted Answer Pending Moderation

Monday, 5 April 2021 13:33 PM UTC
PowerBuilder
# 2

Hi, Sivaprakash -

Although it may be acceptable, I've not previously seen ConnectToNewObject used with "Excel.Sheet" - I've always used "Excel.Application", much in the same way as connecting to Word ("Word.Application") or Outlook ("Outlook.Application"), since you are requesting a connection to an OLE server.

https://docs.appeon.com/pb2019r2/application_techniques/ch22s05.html

You might consider giving that a try?

Best regards, John

Comment

Sivaprakash BKR
Tuesday, 6 April 2021 07:06 AM UTC

Thanks John Fauss,

The code is there and working fine so far in PB 11.5, since 2007. Don't remember from where I got the code [ConnectToNewObject('excel.sheet')]. Changed it to 'excel.application' and xlapp.quit worked without any issue.

Helpful 0

There are no comments made yet.

Mark Goldsmith Accepted Answer Pending Moderation

Monday, 5 April 2021 14:19 PM UTC
PowerBuilder
# 3

Hi,

I think the issue may be that Excel is asking for user input and so it is pausing, causing your PB app to wait until the response is given leaving the impression that the PB app has hung/ crashed.

So what input might Excel be looking for? If it has detected that any changes have been made, in this workbook or any other workbook, then it will prompt you with the Save, Don't save or Cancel dialogue. However, since Excel has not been made visible (or maybe it was visible but the dialogue was behind other windows) then you won't see it.

Looking at the code you provided it doesn't appear you are changing anything in the Excel workbook you opened so, maybe it's another workbook that is open at the same time?? And since you're closing all workbooks with XlApp.Application.Workbooks.close() that could cause this issue. If that is the case you could close the specific workbook you opened with XlApp.Application.Workbooks("the filename").close(False) where False prevents the dialogue from being presented, it will close the one workbook without saving any changes. FYI...do not include the pathname in the filename above.

You might be able to test whether this is happening by putting the statement XlApp.Application.Visible = True just before the line SetPointer(Arrow!) to see whether or not this dialogue is displaying once your code executes the statement xlapp.application.quit(). Click on the Excel icon on the task bar and it will either just show you the Excel workbook or it will have the dialogue in front of it waiting for a response.

As for your 2nd question regarding side effects if you leave out the line xlapp.application.quit , the only side effect may be that changes you want saved (in one or more workbooks) will not have the opportunity to be saved.

As John mentioned I too tend to use xlApp.ConnectToNewObject("Excel.Application") but if the issue is that changes are being detected then it won't make a difference.

Just my thoughts...HTH.

Regards,

Mark

Comment

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.

Please login to post a reply

You will need to be logged in to be able to post a reply. Login using the form on the right or register an account if you are new here. Register Here »

Forgot Password?

Resolved PB application closes when we close OLE Object (Excel)

Find Questions by Tag