How to get row count if xls file is generating dynamically.

[venkatbatchu]

Hi,

My Assumptions:

Excel file name is changing dynamically and saving this filie in specific folder (Make sure that folder name is not varying)

From the above what i am trying to say is whatever the excel file name that will be saved in a specific folder with this

We would fetch the last modified file name that is the excel file which it is modified in that specific folder that means after saving this file in specific folder the excel file name becomes the last modified file name.Once you fetch this then u could use the datatable.Import (Last modified file name).
Once u imported this to data table then u could find out the row count using Datatable.GetRowcount


Please find the below code to fetch the last modified file name in specific folder.

Code:

Function GetNewestFile(ByVal sPath)

   sNewestFile = Null   ' init value

   Set oFSO = CreateObject("Scripting.FileSystemObject")
   Set oFolder = oFSO.GetFolder(sPath)
   Set oFiles = oFolder.Files

   ' enumerate the files in the folder, finding the newest file
   For Each oFile In oFiles
     On Error Resume Next
     If IsNull(sNewestFile) Then
       sNewestFile = oFile.Path
       dPrevDate = oFile.DateLastModified
     Elseif dPrevDate < oFile.DateLastModified Then
       sNewestFile = oFile.Path
     End If
     On Error Goto 0
   Next

   If IsNull(sNewestFile) Then sNewestFile = ""

   GetNewestFile = sNewestFile

End Function
sPath = "c:\my test"  'Here my test is the folder name
Set oFSO = CreateObject("Scripting.FileSystemObject")
sNewestFile = GetNewestFile(sPath)
If sNewestFile <> "" Then
   WScript.Echo "Newest file is " & sNewestFile
Else
   WScript.Echo "Directory is empty"
End If

Please let me know for further clarification.

Regards,
Venkat.Batchu