Excel error

[ḥari]

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]

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]

@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

[rraghvani]

Your function doesn't looks correct

[ḥari]

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

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

[rraghvani]

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]

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]

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]

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

[rraghvani]

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]

I missed extension while typing here sorry for that, I will change that variable name also sorry. My typing mistakes.