Forum Discussion

VenkataRanga's avatar
VenkataRanga
Occasional Contributor
11 years ago

Runtime Error when opening and closing Excel Spreadsheet continuously

Dear All,

     When I executing my project, I get intermittent runtime errors when opening and closing Excel Spreadsheet continuously. Below code I am using.

set xlobj= Sys.OleObject("Excel.Application")
Set workbookObj=xlobj.Workbooks.Open(Project.Path + "\Data_Input\Data_Input.xlsx")
Set wrksheetObjtest1=workbookObj.Worksheets(SheetName)
I saw a thread with same issue created in long back ago( http://smartbear.com/forums/f74/t53094/runtime-error-opening-excel-spreadsheet/ ) .

Thanks in advance!

2 Replies

  • Andrey_M's avatar
    Andrey_M
    Contributor
    11 years ago

    Hi.

     

    Try not to recreate new connection to Excel everytime you need it.

    You may create it once and assign to global (project) variable.

     

    e.g.:

     

    'do it once in your project

    set Project.Variables.xlobj= Sys.OleObject("Excel.Application")

     

    Set workbookObj=Project.Variables.xlobj.Workbooks.Open(Project.Path + "\Data_Input\Data_Input.xlsx")

    [ some code]

    Project.Variables.xlobj.WorkbookObj.Close()

    'close workbook but don't "close" Excel itself (project variable which keeps connection still valid, you may reuse it for another workbook processing)

     

    I use similar approach in my projects, I generate hundreds of workbooks without single issue.

     

    Sorry if I've made mistakes in VBS syntax, I use JS.

     

    • VenkataRanga's avatar
      VenkataRanga
      Occasional Contributor
      11 years ago

      Hi Andrey,

       

            I will try this approach, hope it will resolve my issue.

            Thanks for the support,