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

[iamsekhar]

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

I have explained scenario in attached screen shot in details and also provided object repository screen shot and script which qtp generated.

SwfWindow("Lilly Science Grid").Dialog("File Download").Activate
SwfWindow("Lilly Science Grid").Dialog("File Download").WinButton("Open").Click
Window("Microsoft Excel - d1560244-f72e-4fa").WinObject("NetUIHWND").
Window("Microsoft Excel - d1560244-f72e-4fa").WinObject("NetUIHWND").Click 351,16

Please let me know if you need more information to trouble shoot.
I appreciate your help.

[rajeshwar]

Try Using Regular Expression for "regexpwndtitle" property(for identifying the excel sheet)and [oSheet].usedrange.rows.count for getting rowcount.

[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

[iamsekhar]

(08-18-2010 06:15 PM)venkatbatchu Wrote:  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

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

[iamsekhar]

(08-20-2010 08:56 AM)iamsekhar Wrote:  

(08-18-2010 06:15 PM)venkatbatchu Wrote:  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

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

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

---

(08-24-2010 07:30 AM)iamsekhar Wrote:  

(08-20-2010 08:56 AM)iamsekhar Wrote:  

(08-18-2010 06:15 PM)venkatbatchu Wrote:  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

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

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 ?

Can you please suggest, is there any other way we can automate above scenario if we dont have folder access?
Thanks for your help
Sekhar

[venkatbatchu]

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


Please let me know for further clarification.


Regards,
Venkat.Batchu

[iamsekhar]

(08-24-2010 10:01 AM)venkatbatchu Wrote:  Hi 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.


Please let me know for further clarification.


Regards,
Venkat.Batchu

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


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

[venkatbatchu]

Hi Sekhar,


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.


Please let me know for further clarification.

Regards,
Venkat.Batchu

[iamsekhar]

(08-25-2010 01:15 PM)venkatbatchu Wrote:  Hi Sekhar,


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.


Please let me know for further clarification.

Regards,
Venkat.Batchu

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.

[venkatbatchu]

Hi Sekhar,

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)

Regards,
Venkat.Batchu