Forum Discussion

csachanandani's avatar
csachanandani
Occasional Contributor
2 years ago

TestComplete started failing tests that read data from Excel with Macros

I had TestComplete tests that would read data from excel with macros and now have started receiving "Excel internal error 'Element not found'." when I started running them now. Has anyone ever experienced this?

The code from reading excel is:(Project Path fetches the location where project is located)

fileLocation = Project.Path
excelFile = Excel.Open(fileLocation+"Test Data\DataSheet.xlsx")
excelSheet = excelFile.SheetByTitle["Test Data"]

  • rraghvani's avatar
    rraghvani
    Champion Level 3
    2 years ago

    DataSheet.xlsx or DataSheet.xlsm? So the Open method is not working? What's the exact error message?

     

    Are you able to open the file normally?

     

    Can you create a blank spreadsheet with a just a few items, and try to open that?

     

    If you do an internet search for "Excel element not found", you will come across many results. The error is specific to Excel, and not TestComplete.

  • rraghvani's avatar
    rraghvani
    Champion Level 3
    2 years ago

    Is it reading the correct file? And is the dialog about giving permissions to run Excel Macros preventing the file from being read?

    • csachanandani's avatar
      csachanandani
      Occasional Contributor
      2 years ago

      Yes, it reads file location, the file has all correct permissions - no changes on that, and then fails at the excelFile = Excel.Open(fileLocation+"Test Data\DataSheet.xlsx") line. Hard time understanding why the tests are failing. 

  • rraghvani's avatar
    rraghvani
    Champion Level 3
    2 years ago

    Ensure there's no other instances of Excel running. 

     

    Also, I've just noticed, your path is not using double back slash. It should be like,

    Excel.Open(fileLocation + "Test Data\\DataSheet.xlsx")

// OR

Excel.Open(fileLocation + "\\Test Data\\DataSheet.xlsx")

     

    • csachanandani's avatar
      csachanandani
      Occasional Contributor
      2 years ago

      \\ or \ doesnt matter as it picks correct path. And no excel app is running in background. I am clueless why it is not working. It randomly works and then stops.

  • rraghvani's avatar
    rraghvani
    Champion Level 3
    2 years ago

    Using a single-backslash is used for escaping certain characters. A double-backslash returns a single slash. Also, the example shows

    Are you calling the Open method more than once?

     

    I'm curious to know, what is the full path if you were to run,

    fileLocation = Project.Path + "Test Data\DataSheet.xlsx"
Log.Message(fileLocation)
//excelFile = Excel.Open(fileLocation+"Test Data\DataSheet.xlsx")
//excelSheet = excelFile.SheetByTitle["Test Data"]

     

    • csachanandani's avatar
      csachanandani
      Occasional Contributor
      2 years ago

      excelFile = Excel.Open("C:\\Automation\\Test Data\\DataSheet.xlsm") didnt work either. Also, Open method is called only once and then we read data from different sheets.