Hi,We've "Export To Excel" option in our application, when I export, it will write the data to excel file and store it in a folder. I'm doing this export through TestComplete.From the folder, I want to pick the latest / recent excel file for further comparison testing, how should I tell TC to pick the latest file, is there a way to do so? Please suggest.

Hi Anil,I recommend that you use an approach described in the Find last changed file using JScript forum thread.Does this information help?

Hi Irina,Thank you for replying.The code is working, but it is printing all the files from the folder. Actually I want the code to print the latest file (only one file) from the folder...

Hi Anil, I'm really sorry for confusing you. Try using the following code:Function LastModifiedFile Dim FolderName, FolderInfo, Num, i, FileDate, a() FolderName = "E:\Anil-Backup\Test Complete\Projects_Old\In4Suite Project\Exported Reports\" Set FolderInfo = aqFileSystem.GetFolderInfo(FolderName) Num = FolderInfo.Files.Count Log.Message "The folder contains " &Num &" files." For i = 0 to Num-1 ReDim Preserve a(i) FileDate = FolderInfo.Files.Item(i).DateLastModified a(i) = FileDate Next NewestTime = a(0) LatestFileNumber = 1 For i = 0 to Num-1 if(aqDateTime.Compare(NewestTime, FileDate) < 0) then NewestTime = a(i) LatestFileNumber = i+1 end if Next sPath = FolderInfo.Files.Item(LatestFileNumber -1).Path Log.Message NewestTime Log.Message sPathEnd Function

Pick Recent / Latest Excel File | SmartBear Community

irina_lukina
Super Contributor
13 years ago
I'm glad it helped :)
mesg2anil
Regular Contributor
13 years ago
Hi Irina,

I'm troubling you again... have a question... :)

Below subroutine will call the function LastModifiedFle and get the excel file name(the one you helped me with), but I'm facing a problem here.

1. If I get the filename from Function to Subroutine, can I open the excel file using the below line...

Call Excel.Workbooks.Open("E:\Anil-Backup\Test Complete\Projects_Old\In4Suite Project\Exported Reports\" &LastModifiedFle)

I tried, but it is not recognizing the file, above line is reading only till folder Exported Reports, it is not taking &LastModifiedFle to read the filename, how to get this read the filename? TestResults zip file is attached for more details with screenshot in the .mht file. Code is below

Sub chek'Open Excel Connection

Dim LastModifiedFle

Set Excel = Sys.OleObject("Excel.Application")

Call Excel.Workbooks.Open("E:\Anil-Backup\Test Complete\Projects_Old\In4Suite Project\Exported Reports\" &LastModifiedFle) 'getting the filename from Function

Log.Message LastModifiedFle

chek Excel = .OleObject("Excel.Application") Excel.Workbooks.Open("E:\Anil-Backup\Test Complete\Projects_Old\In4Suite Project\Exported Reports\" &LastModifiedFle) .Message LastModifiedFle

End Sub

Function LastModifiedFle

Dim FolderName, FolderInfo, Num, i, FileDate, NewestTime, LatestFileIndex

FolderName = "E:\Anil-Backup\Test Complete\Projects_Old\In4Suite Project\Exported Reports"

Set FolderInfo = aqFileSystem.GetFolderInfo(FolderName)

Num = FolderInfo.Files.Count

Log.Message "The folder contains " &Num &" files."

NewestTime = aqDateTime.SetDateTimeElements(1000, 1, 1, 0, 0, 0)

LatestFileIndex = 0

For i = 0 to Num-1

FileDate = FolderInfo.Files.Item(i).DateLastModified

if(aqDateTime.Compare(NewestTime, FileDate) < 0) then

NewestTime = FileDate

LatestFileIndex = i

end if

Next

sPath = FolderInfo.Files.Item(LatestFileIndex).Path

fName = FolderInfo.Files.Item(LatestFileIndex).Name

Log.Message NewestTime &": " & sPath

Log.Message NewestTime &": " & fName

LastModifiedFle = fName 'I'm passing the excel filename to function and passing it to above subroutine

End Function
TestResults.zip631 KB
irina_lukina
Super Contributor
13 years ago
Hi Anil,

The problem is that you scratch the value returned by the LastModifiedFle function at the beginning of the chek'Open Excel Connection routine. To avoid the mentioned error, you just need to delete the Dim LastModifiedFle line from the code of the chek'Open Excel Connection routine. Does it work now?
mesg2anil
Regular Contributor
13 years ago
Hi Irina,

I did following changes in the script... This time I'm getting "Type mismatch: 'LastModifiedFile'" error at line: ExlValue = LastModifiedFile

Function checkexcelcells()

checkexcelcells()
Dim ExlValue

ExlValue = LastModifiedFile        'Type mismatch: 'LastModifiedFile'" error at line, I tried giving () at end of LastModifiedFile(), still dint work :(

'Open Excel Connection

Set Excel = Sys.OleObject("Excel.Application")

' Call Excel.Workbooks.OpenXML("E:\Anil-Backup\Test Complete\Projects_Old\In4Suite Project\Exported Reports\"&ExlValue)

Set OpenExcel = Excel.Workbooks.OpenXML("E:\Anil-Backup\Test Complete\Projects_Old\In4Suite Project\Exported Reports\" &ExlValue)

Call OpenExcel            'Instead of Calling the excel directly (commented line above), used Set OpenExcel and calling OpenExcel

Delay 500

End Function

'===============================================================================
Sub LastModifiedFile()    'Added '( )' here

Dim FolderName, FolderInfo, Num, i, FileDate, NewestTime, LatestFileIndex

FolderName = "E:\Anil-Backup\Test Complete\Projects_Old\In4Suite Project\Exported Reports\"

Set FolderInfo = aqFileSystem.GetFolderInfo(FolderName)

Num = FolderInfo.Files.Count

Log.Message "The folder contains " &Num &" files."

NewestTime = aqDateTime.SetDateTimeElements(1000, 1, 1, 0, 0, 0)

LatestFileIndex = 0

For i = 0 to Num-1

FileDate = FolderInfo.Files.Item(i).DateLastModified

if(aqDateTime.Compare(NewestTime, FileDate) < 0) then

NewestTime = FileDate

LatestFileIndex = i

end if

Next

sPath = FolderInfo.Files.Item(LatestFileIndex).Path

fName = FolderInfo.Files.Item(LatestFileIndex).Name

Log.Message NewestTime &": " & sPath

Log.Message NewestTime &": " & fName

LastModifiedFile = fName

End Sub

irina_lukina

Super Contributor

13 years ago

Try running the code below:



Function checkexcelcells()



  Dim ExlValue 

  ExlValue = LastModifiedFile    

 ' Open Excel Connection

  Set Excel = Sys.OleObject("Excel.Application")

  Call Excel.Workbooks.Open("E:\Anil-Backup\Test Complete\Projects_Old\In4Suite Project\Exported Reports\" & ExlValue)

  Delay 5000



End Function



'=============================================================================== 



Function LastModifiedFile



  Dim FolderName, FolderInfo, Num, i, FileDate, NewestTime, LatestFileIndex

  FolderName = "E:\Anil-Backup\Test Complete\Projects_Old\In4Suite Project\Exported Reports\"

  Set FolderInfo = aqFileSystem.GetFolderInfo(FolderName)

  Num = FolderInfo.Files.Count

  Log.Message "The folder contains " &Num &" files."

  NewestTime = aqDateTime.SetDateTimeElements(1000, 1, 1, 0, 0, 0)

  LatestFileIndex = 0



  For i = 0 to Num-1

 

    FileDate = FolderInfo.Files.Item(i).DateLastModified

    if(aqDateTime.Compare(NewestTime, FileDate) < 0) then 

      NewestTime = FileDate 

      LatestFileIndex = i 

    end if 



  Next 



  sPath = FolderInfo.Files.Item(LatestFileIndex).Path

  fName = FolderInfo.Files.Item(LatestFileIndex).Name

  Log.Message NewestTime &": " & sPath

  Log.Message NewestTime &": " & fName

  LastModifiedFile = fName



End Function

Does it work now?

mesg2anil
Regular Contributor
13 years ago
Hi Irina...

Thank you once again... its working :)

Forum Discussion

Pick Recent / Latest Excel File

Related Content

How to Validate recently downloaded output Excel Files

WinForms existing name mappings broken with latest update

Not able to load DataSource properties from Excel on latest READY! API1.9.0 .

Latest TestComplete 15.56.2.7 worth upgrading to ?

Excel-Write value

Recent Discussions

The StartMenuExperienceHost.exe process crashed.

General Exception raised within Run Browser action

Unstable test results during automation testing

TestComplete with Word document

Remote browser clear cache