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!
Debugging
Script Extensions
Script Tests
Test Results
Test Run
Variables
web testing
  • rraghvani's avatar
    rraghvani
    Champion Level 3
    2 years ago

    Are you able to provided the exact coding and spreadsheet, so that we can try and reproduce it?

     

    Have you tried repairing your installation of Office/Excel?

    • ḥari's avatar
      ḥari
      Frequent Contributor
      2 years ago
      rraghvani i did repair of excel/office application in my workstation. Still I am getting same error in test complete
  • rraghvani's avatar
    rraghvani
    Champion Level 3
    2 years ago

    Also ensure you have sufficient access to files and folders, as this can cause an impact too. I don't know where you are reading your Excel file from.

     

     

    • ḥari's avatar
      ḥari
      Frequent Contributor
      2 years ago
      I have access to those files and folders, I am fetching those excel files from user profile "environment variable"
  • rraghvani's avatar
    rraghvani
    Champion Level 3
    2 years ago

    In your original project, read the Excel file from C:\Temp, and see if it works.

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

      Yep it's fetching datas. So, it's a problem with that generic function or %userprofile%.

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

    You will have to get your computer looked at by your IT, as I'm unable to suggest anything else.

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

    You say that the scripts work fine on another machine, accept for yours .Which usually indicates there's some sort of issue with your machine, which could be relating to Office/Excel.

    • ḥari's avatar
      ḥari
      Frequent Contributor
      2 years ago
      Yeah rraghvani that what I am also thinking but i don't have any access to do in my machine, today I requested to check those things. After they completed repair I will check it and let you know
  • rraghvani's avatar
    rraghvani
    Champion Level 3
    2 years ago

    What do you mean by I am fetching those excel files from user profile "environment variable"?

    • ḥari's avatar
      ḥari
      Frequent Contributor
      2 years ago
      rraghvani excel file are accessed from user profile (In run -> ℅userprofile%-> enter)
  • rraghvani's avatar
    rraghvani
    Champion Level 3
    2 years ago

    Your files should be in %userprofile%\Documents and not in %userprofile%. You may have access to that folder when using Explorer, but it doesn't necessary mean that TestComplete will have the appropriate permissions to access it.

     

    I suggest you create a folder C:\Temp, copy your Excel in that folder, and then try to run your script using that path to see if it works.

     

     

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

      rraghvanithe below code is the generic function created for access the excel files.

      Function environmentVar() {
      envVar = aqEnvironmentVariable.GetEnvironmentVariable("USERPROFILE")
      envVarfolder = envVar + "\\DMData\\"
      If (! aqFileSystem.Exists(envVarfolder))

      aqFileSystem.CreateFolder(envVarfolder) 

      Else

      Log.Message ("Folder Exists") 

      If(! aqFileSystem. Exists (envVarfolder + "\\DMMobile.xlsx")) 
      {
      Log.Error("Excel file doesn't exists")
      Runner. Stop() ;
      }
      }


      Function getExcelData(filename)
      {
      Var excelFile = Excel.Open( envVarfolder +\\DMMobile.xlsx")
      Var excelSheet = excelFile.SheetByTitle(fileName)
      Return excelSheet
      }


      Here in function getExcelData variable excelFile is getting null so the error shows as sheetByTitle of null.

      The customer requirement is they need to access the excel from environment variable so that might be easy to copy the excel folders over there and execute the script.

      As I mentioned previously the same script in other workstation working fine. They also access from the environment variable only.

      If because this code am getting error means let me know

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

      May I know the reason why it's not looks correct. 

       

       

      Could you please check now I missed some lines while typing sorry

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

    I see the code like this,

    function environmentVar()
{
    envVar = aqEnvironmentVariable.GetEnvironmentVariable("USERPROFILE");
    envVarfolder = envVar + "\\DMData\\";
    if (!aqFileSystem.Exists(envVarfolder))
        aqFileSystem.CreateFolder(envVarfolder);
    else
        Log.Message("Folder Exists");

    if (!aqFileSystem.Exists(envVarfolder + "\\DMMobile")) 
    {
        Log.Error("Excel file doesn't exists");
        Runner.Stop();
    }
}

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

    You create a folder called DMData in %userprofile%, if it does not exist. You then check if the folder DMMobile does not exist, and output "Excel file doesn't exists" - are you checking the folder or file?

     

    Then you get the Excel file DDMobile.xlsx from folder %userprofile%\DMData - which will not exist, if you've created the folder.

     

    I don't really understand what's going on. Why can't you just read the Excel file as normal!?

     

     

     

     

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

      In function environmentVar there I am checking envVarfolder+DMData folder not exists then it create that folder.

      DMMobile is a excel file name if that file not exists it throwing error as file not exists in DMData folder.

      Function getExcelData here filename as sheet name.

      Because I have lot of excel files and sheets also so if some one need to execute the script they need to copy the excel file in same location for that i am using userprofile. If any one need to execute the script they need to copy the excel files in userprofile DMData folder then only they can execute. 

       

      I think now you have a clear understanding on this code. Still not yet let me know

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

    Should you not be then checking for the filename that has .xlsx e.g.

     if (!aqFileSystem.Exists(envVarfolder + "\\DMMobile.xlsx"))

     as opposed the folder name

     if (!aqFileSystem.Exists(envVarfolder + "\\DMMobile"))

    ?

     

     

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

      DMMobile is an excel file know so it might to have extension for that excel file .Xlsx right. If I am wrong correct me. 

       

      For file name(ie sheetname) no need of extension .Xlsx