Error on Excel importing

I am trying to import around 1500 Excel files to my system. The code is working in a loop and I am able open and import around 600 Excel files. After that I am getting an error message like: Error calling external object function open at line 55.....

I really stuck with this issue, if anyone can help that will be grateful.

---

Code posted in reply comments:

For ll_LoopCnt = 1 To Dw_1.rowcount( )
    Ls_File_Name = Dw_1.getitemstring( ll_LoopCnt, "file_name")
    Ls_Path =Dw_1.getitemstring( ll_LoopCnt, "file_path")
    ll_Sr_No= Dw_1.getitemNumber( ll_LoopCnt, "sr_no")
    ldt_File_Date= Dw_1.getitemDateTime( ll_LoopCnt, "file_date")
    Excel.Application.DisplayAlerts = "False"
    Excel.WorkBooks.Open( Ls_Path )
    Excel.Application.Visible = False
    Excel.windowstate = 2 // 1 : Normal, 2 : Minimize, 3 : Maximize
    Excel.Application.CutCopyMode = False
    Lb_sheet_rtn = excel.worksheets(7).Activate
    Ls_ClipBoard = c开发者_开发技巧lipboard()
    Excel.Application.ActiveWorkbook.Save()
    Excel.Worksheets(7).UsedRange.Copy
    ll_cnt = ds_1.importclipboard()
    IF ll_cnt <= 1 THEN
        Messagebox("Error", "Could not find.")
    Else
        Dw_1.Scrolltorow( ll_LoopCnt )
        Dw_1.SetItem( ll_LoopCnt, "status", 'Success')
        For ll_Inner_LoopCnt = 1 To Ds_1.RowCount( )
            Ds_1.Object.file_path[ll_Inner_LoopCnt] = Ls_Path
            Ds_1.Object.file_name[ll_Inner_LoopCnt] = Ls_File_Name
            Ds_1.Object.file_sr_no[ll_Inner_LoopCnt] = ll_Sr_No
            Ds_1.Object.file_date[ll_Inner_LoopCnt] = ldt_File_Date
        Next
    END IF
    Clipboard(ls_ClipBoard)
    Ds_1.Reset( ) //Reset the data store
    Excel.Application.ActiveWorkbook.Save()
    Excel.Application.ActiveWorkbook.Close(False);
    Excel.Application.Quit
    Excel.Application.CutCopyMode = False
    IF ll_LoopCnt = ll_Excel_Cnt Then //--->> After 100 files reset the memmory
        ll_Excel_Cnt = ll_LoopCnt + 100
        Excel.DisConnectObject()
        DESTROY excel
        DESTROY TEst_Excel
        GarbageCollect ( )
        Excel = Create OLEObject
        Test_Excel = Create OLEObject
        Li_rtn = excel.ConnectToNewObject("excel.application")
        IF li_rtn <> 0 THEN
            MessageBox('Excel error','can not run Excel Program')
            DESTROY Excel
            RETURN 0
        END IF
    End IF
Next
Excel.displayalerts = False
Excel.Application.Quit
Excel.displayalerts = True
Excel.DisConnectObject()
DESTROY Excel
DESTROY Test_Excel /* This is the code i written i dont think the OLE is crashing i think the connnectto the OLE is getting lost after some time, but stile its going fine for almost 600 records.. */

---

Seeing the line of code would help, but this error message typically (in the context of OLE, which I'm guessing is the case here) comes from PowerBuilder making an OLE call which the OLE host rejects. From the information you've supplied, it's impossible to tell if the OLE host has crashed and isn't responding anymore, or if you've got the OLE host into a state where these functions are no longer applicable, or if the OLE object has become invalid, or what.

If it were me, and it was happening consistently, I'd run the app in the debugger to get to the state where the error is about to happen (you can set advanced attributes in breakpoints to not have a breakpoint activate every time it is passed) and try interrogating the OLE objects. I'd expect you'd also have to throw in some test code, since I'm not confident everything you'd want to test would be available to the debugger.

---

New Feb 21

I'd also change the set of files being processed, so that I could tell if the key to the crash is a specific file, or the quantity of files processed. For example, if you get rid of the first 100 files, does it still crash on the 600th file (same quantity) or the 500th file (same file)?

One possibility is that you're running out of memory. Each "dot" in an OLE reference (attribute access, method call) creates an object in memory that isn't destroyed until the garbage collect. The code clip you've posted will never enter the block where the GarbageCollect() is called (ll_Excel_Cnt is never initialized), so you might want to make sure that part is working. I'd also get rid of unnecessary calls. For example, you've got several calls that maintain the state of Excel within the loop (e.g. Excel.Application.Visible), when they only need to be called once. I'm also not clear from the code clip why you'd need to call a Save(), either time; this could be expendable as well. This clean up should also make your code run faster.

---

Good luck,

Terry

---

Around line 30 you have

Excel.Application.ActiveWorkbook.Save()
Excel.Application.ActiveWorkbook.Close(False);
Excel.Application.Quit

You shouldn't call Excel.Application.Quit there. Also, I always recommend to put anything that uses OLE inside a Try..Catch block and catch OleRuntimeError and RuntimeError.