08-18-2010, 06:15 PM (This post was last modified: 08-18-2010, 06:18 PM by 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
Hi Venkat,
Thanks for your reply and detailed explanation with script. I grately appreciated and very thankful to you.
The excel files are saving in server and path is consistent. For timebeing i dont have folder access now and it is in process to get access. Once i get i will use your code and update status.
Sekhar
08-24-2010, 07:30 AM (This post was last modified: 08-24-2010, 08:07 AM by iamsekhar.)
Hi Venkat,
There are some limitations i have't got server access where the xls files are storing dynamically. I am thinking other way to automate this scenario is
1. In File download dialouge we have "Open" or "Save" buttons. By clicking clicking Open or save i have save the file in local path and pass the file name
2. Above scenario i can use same script ?
Thanks for your help
Sekhar
The above script which i given is with out giving any file name while saving.
1.First click the Save button (Browser("xxx").Dialog("xxx").Winbutton("xxx").Click
2. Here you will not be knowing what is the file name which you have saved.
3. The above file will be saving in specific folder( My assumption: Here file name is dynamically varying but folder name is constant, i.e all files are by default saving in same folder)
4. By usin this folder you can find out the last modified file name i.e the last file which you have saved.
5. Now you know what is the last modified file i.e file which u saved recently
6. You will come to know what is the last modified file is by using the above script
7. Once u come to know then import the file to datatable i.e Datatable.Import "file name" this file name is the one whic u got it from script
8. After importing find the row count by using Datatable.GetRocount
Finally u will be having Rowcount in that particular Excel file.
Hi Venkat,
Thanks for your reply with detailed steps. Its really very great for your help. Requirement is slight changed, because we have to save file name in any drive (C or D) and give the name for xls and pass this excel file for row count. I have done in the following way. It is working but i am not sure this is correct coding practice or not? One more Question: I have many this kind of scenarios to validate xls row count. Is there any other way to write code optimization like function or Descriptive programming. Kindly suggest your approach.
Thanks for your help. Please refer below code FYI
Code:
Window("Lilly Science Grid").Page("Page_3").Link("25").Click
wait(2)
rowCnt = Window("Lilly Science Grid").Page("Page_3").Link("25").GetROProperty("text")
convLng = CLng(rowCnt)
msgbox rowCnt
wait(3)
SwfWindow("Lilly Science Grid").Dialog("File Download").Click 285,18
SwfWindow("Lilly Science Grid").Dialog("File Download").Activate
SwfWindow("Lilly Science Grid").Dialog("File Download").WinButton("Save").Click
wait(5)
Dialog("Save As").Activate
Dialog("Save As").WinEdit("File name:").Set "D:\tumo.xls"
Dialog("Save As").WinButton("Save").Click
Dialog("Save As_2").WinButton("Yes").Click
Dialog("Download complete").Click 321,184
Dialog("Download complete").WinButton("Close").Click
fileName = "D:\tumo.xls"
Set xlObj = CreateObject ("Excel.Application")
Set wBook = xlObj.workBooks.Open(fileName)
Set sheetName = wBook.worksheets("Sheet1")
'Set sheet = xlObj.ActiveWorkbook.worksheets("sheet1")
column_count = sheetName.usedrange.columns.count
row_count = sheetName.usedrange.rows.count
xlsRowCnt = row_count -1
msgbox xlsRowCnt
If xlsRowCnt = convLng Then
reporter.ReportEvent micPass, "Row Count Validation","Row Count matching with column header. Step.Pass"
else
reporter.ReportEvent micFail, "Row Count Validation","Row Count is not matching with column header.Step.Fail"
End If
wBook.close
Dialog("Download complete").Click 321,184
In above line of scrpt u have passed x, y coordinates as it is not suggestable why because that "Dialog window i.e Download status window" cooridnates will be varying based on the resolution so i request you not to pass coordinates instead u could click the appropriate action i.e hitting enter button or clickingclose button.
Hi Venkat,
Thanks for your reply. I will modify scrit as per your suggestion. I have doubt explained in attached document. Please suggest how to automate that scenario. Please correct me if something i am wrong. Plz let me know if need more information about scenario.
As your current issue is new one and request you to please open a new thread .
So that it may not confuse others (may be same issue will be facing some other guys)
