Forum Discussion

ḥari's avatar
ḥari
Frequent Contributor
2 years ago

Excel error

Hi team,
I am facing error while fetching datas from excel in execution time alternatively (one time it's executing and another time it throws error as "excel internal error element not found" And " The access to the excel file is blocked. Possible because it is open in excel"

But no excel file is opened in my machine i have restarted, cleared temp files and task manager as well.

I tried to watch that excel variable in "add watch" While getting that error excel variable is returning as "NULL"

The same script are executing fine in other machines only in my machine i am getting error alternatively.

I have attached the error log report below for your reference

Can any one give me a solution for this??

It's a great help for me!

Many Thanks!
  • rraghvani's avatar
    rraghvani
    Champion Level 3

    This is a file \\DMMobile.xlsx and this is a directory \\DMMobile. However, \\DMMobile could be a file without an extension. How does TestComplete know what you are referring to, if you have not explicitly defined the filename?

     

    You're passing in a filename to getExcelData, should that not be SheetTitle? And the function name should be getExcelSheet as you are returning an excel sheet.

    function getExcelData(filename)
    {
        var excelFile = Excel.Open(envVarfolder + "\\DMMobile.xlsx");
        var excelSheet = excelFile.SheetByTitle(fileName);
        return excelSheet;
    }

     

    Name your variables and functions correctly, so that other people can easily read your code. 

    • ḥari's avatar
      ḥari
      Frequent Contributor
      I missed extension while typing here sorry for that, I will change that variable name also sorry. My typing mistakes.
  • rraghvani's avatar
    rraghvani
    Champion Level 3

    With regards to the original error you are receiving, I suggest you create a new project, and write your code to read the appropriate data from your Excel file. Also, read the Excel file from a different location such as C:\Temp. This is just to test that reading of Excel works for you.

    • ḥari's avatar
      ḥari
      Frequent Contributor
      I created new project and I can able fetch the excel datas from C:\Temp
  • rraghvani's avatar
    rraghvani
    Champion Level 3

    That good. We're making progress 😀

     

    So it's either an issue with TC not having appropriate permissions to read from %userprofile% or your coding needs to be corrected.

    • ḥari's avatar
      ḥari
      Frequent Contributor
      Thanks for your patience and guiding me.

      Did you think I need to correct my coding?

      I have an doubt here if coding wrong means how the same coding executing in other workstation. Because of this only I didn't concentrate in coding.

      How to identify TC doesn't have permission to read datas from %userprofile% ?
    • ḥari's avatar
      ḥari
      Frequent Contributor
      Ok i will share this link to my service team. Let you know what's happening!
      • ḥari's avatar
        ḥari
        Frequent Contributor

        rraghvani Is there is any possibilities that excel file got corrupted

        Because I tried to fetch new excel file inside %userprofile% that works for me and then I tried to access the old excel file it throws same error.

  • rraghvani's avatar
    rraghvani
    Champion Level 3

    Use Excel application to open the file and see what happens.

    • ḥari's avatar
      ḥari
      Frequent Contributor

      It's opening but this particular excel files I am getting error, while executing in new project as well and in C:\\temp location aswell. 

       

      Totally confused😕

       

  • rraghvani's avatar
    rraghvani
    Champion Level 3

    Can you upload your project & Excel file, so that I can have a look.

    • ḥari's avatar
      ḥari
      Frequent Contributor
      Project is not an issue sharing here, but i have lot of scripts in that project (size of the project and it takes lot of time) and excel files contains customer data I don't have permission to share here.
  • rraghvani's avatar
    rraghvani
    Champion Level 3

    I don't want your actual project, just the new project that you had created to test that Excel works (which is now not working)

    • ḥari's avatar
      ḥari
      Frequent Contributor

      I will share the screenshot of those project and excel file. 

       

      That Test. Xlsx excel is fetching the data I have debug the irl and that screenshot also added. 

       

      That DMTRegression. Xlsx won't fetching data getting error as "element not found"

  • rraghvani's avatar
    rraghvani
    Champion Level 3

    I was hoping you can share the scripts, so that I don't have to type this out to test it!

     

    You are still not indenting your code!

    • ḥari's avatar
      ḥari
      Frequent Contributor

      Expected. I can type and send, could you please change the excelFile name and folder as per the screenshot please. The below code is executed fine it's fetching the url

       

      Using mobile for typing sorry for not indenting code here

      Function excelTest() {
      envVarfolder = "C:\\Temp"
      Var excelFile = Excel. Open (envVarfolder + "Test.xlsx")
      Var excelSheet = excelFile.SheetByTitle(" Rate")
      Let RowCount = excelSheet. RowCount
      Let ColumnCount = excelSheet. ColumnCount

      For(let i = 1; i<=RowCount; i ++)
      Let url = ""
      For (let j= 1;j<=ColumnCount;j++)
      url = (varToString(excelSheet.cell(j, i). Value))

      Browsers. Item(browser[chrome]). Run(url) 
      Var page = Sys.Browsers(browsers[chrome].page("*")) ;
      }



  • rraghvani's avatar
    rraghvani
    Champion Level 3

    Here's an example. My Excel spreadsheet looks like this,

    And here's JavaScript code to read and open the URLs in Chrome and Edge,

     

    function TestWeb()
    {
        var excelFile = Excel.Open("C:\\temp\\Websites.xlsx");
        var excelSheet = excelFile.SheetByTitle("Websites");
        var rows = excelSheet.RowCount;
        var cols = excelSheet.ColumnCount;
        Log.Message("Rows: " + rows);
        Log.Message("Columns: " + cols);
        
        var browsers = ["chrome", "edge"];
        
        for (var x = 1; x <= rows; x++) {
            for (var y = 2; y <= cols; y++) {
                var url = excelSheet.Cell(y, x).Value
                Log.Message(url);
                
                for (var k = 0; k < browsers.length; k++) {
                    Browsers.Item(browsers[k]).Run(url);
                    var page = Sys.Browser(browsers[k]).Page("*");
                }
            }
        }
    }

     

     

    Make sure you fully understand what the code is doing, and how it's written, including layout.

    • ḥari's avatar
      ḥari
      Frequent Contributor
      Some changes I can understand the coding anyway it's executing fine only, what's the issue with excels