Forum Discussion

mesg2anil's avatar
mesg2anil
Regular Contributor
13 years ago

Pick Recent / Latest Excel File

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.

16 Replies

Sort By
  • mesg2anil's avatar
    mesg2anil
    Regular Contributor
    13 years ago
    Hi Irina,



    Long time... How are you doing?

    Thank you for replying!!



    I'm coding in VB and the code you sent me is in Jscript... :) I tried to convert it into VB, but getting stuck in between... below is the code



    Function LastModifiedFile

      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."

     

      Dim NewArray()

       

      For i = 0 to Num

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

          a = NewArray()                'AT THIS LINE I'M GETTING "SUBSCRIPT OUT OF RANGE: 'NewArray()"

          a(i) = FileDate

          Log.Message NewArray(i)

      Next

    'You can obtain the file name using the following code

          Log.Message FolderInfo.Files.Item(i).Path


    End Function



    Any suggestions on this please??

  • irina_lukina's avatar
    irina_lukina
    Super Contributor
    13 years ago

    Hi Anil,


    I've changed your code a little bit, and now it works fine:




    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) = aqConvert.DateTimeToStr(FileDate)

          Log.Message a(i)



          'You can obtain the file name using the following code

          Log.Message FolderInfo.Files.Item(i).Path

      Next



    End Function





    Is it what you need?

  • mesg2anil's avatar
    mesg2anil
    Regular Contributor
    13 years ago
    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...
  • irina_lukina's avatar
    irina_lukina
    Super Contributor
    13 years ago

    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 sPath



    End Function

  • mesg2anil's avatar
    mesg2anil
    Regular Contributor
    13 years ago
    Hi Irina,



    Thank you for replying... Why should you say sorry... in fact you are trying to help me in the script...!!



    Code is working fine and it picking only one file now... but it is not picking the correct file. I mean the latest file is not picked. Attaching the screenshot of the folder structure and the output of the script run in TC is also shown below...



    Output file generated:

    "E:\Anil-Backup\Test Complete\Projects_Old\In4Suite Project\Exported Reports\compareprices.xlsx 15:08:55 Normal"



    If you see the folder screenshot, it should pick MyResults.xlsx file, as the modified date of the file is "4/10/2012 3:06PM" all other files are less than today's date.
  • irina_lukina's avatar
    irina_lukina
    Super Contributor
    13 years ago

    Hi Anil,


    If you see the folder screenshot, it should pick MyResults.xlsx file, as the modified date of the file is "4/10/2012 3:06PM" all other files are less than today's date.


    According to the screenshot, the MyResults.xlsx file was modified on 4/10/2012 at 1:17 PM.


    To help us investigate the problem, please run the code below one more time:



    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 &": " & sPath



    End Function


    After the test run is over, please export the test results to an .mht file and post it here. Also, please post here the screenshot of the Exported Reports folder right after the test execution has stopped.


    Thanks.

  • mesg2anil's avatar
    mesg2anil
    Regular Contributor
    13 years ago
    Hi Irina,



    Thank you for quick reply.

    My bad, I mistyped the time wrong.



    As you said, I ran the script and posting the test results and screenshot of the folder here.

    It is still picking the same file what it picked last time.
  • irina_lukina's avatar
    irina_lukina
    Super Contributor
    13 years ago

    Hi Anil,


    I've deleted some lines and modified the mechanism a little bit. Please run the code below and provide me with the results.



    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

      Log.Message NewestTime &": " & sPath



    End Function

  • mesg2anil's avatar
    mesg2anil
    Regular Contributor
    13 years ago
    Hi Irina,



    Thank you very very much!! It is working as expected!!

    You are just superb...!!