Hi,
Can anyone help me how I can retrieve a label text or dropdown list selected text and save it on excel file using vb code.
I tried from calling the element but still nothing.
<CodedStep("Store Data in Excel")> _
Public Sub Transfers_Between_CodedStep()
Dim dataSourcePath As String = Me.ExecutionContext.DeploymentDirectory + "\Data\domainResults.xlsx" 'this method is calling a link from an excel file and pass it on url path to load it
Dim myPath As String = "C:\Users\Charitos\Documents\TransferInputs.xlsx"
If Not System.IO.File.Exists(myPath) Then
System.IO.File.Copy(dataSourcePath, myPath)
End If
Dim excelApp As New Microsoft.Office.Interop.Excel.Application()
Dim workbook As Microsoft.Office.Interop.Excel.Workbook = excelApp.Workbooks.Open(myPath)
System.Threading.Thread.Sleep(1000)
ActiveBrowser.RefreshDomTree()
excelApp.Cells(Data.IterationIndex + 2, 1) = Pages.AlHilalEbankingSolution1.TransactionSpan.AssertContent() //This is where I want to call the element or some other way from the webpage that is still open to retrieve the text and store it on excel.
excelApp.Visible = True
excelApp.ActiveWorkbook.Save()
workbook.Close(False, Type.Missing, Type.Missing)
excelApp.Workbooks.Close()
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook)
excelApp.Quit()
GC.Collect()
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp)
End Sub
End Class
8 Answers, 1 is accepted
Rank 1
In matter of fact. I want to use the same procedure as the 'Extract' and after that the 'Verification' but because the selected dropdown list may have different values. I need the 'Extract' command but without verifying what kind of text should retrieve.
The concept is, I select something from the dropdown list from the first webpage. And I have a button "Submit" where directs me to another page where I see my input data for confirmation.
Thank you for reaching us out.
There are few things you have listed and I will try to address these all. As you might have noticed the Extraction of an element's value or text content is quite similar as to verify it. This could be easily seen in Test Studio in the context menu of a Verification step and the section Change Role... Please read further for the extraction step here.
As I am not quite sure if you are using the testing framework only or Test Studio I would like to share the the coded approach for extraction as well. It would be useful to clarify that basically you do not need to write that extracted value to an external file if you intend to use it during run-time only.
Though depending on your requirements you might need to store that for your reference. Here is a sample code which demonstrates how to write some data to an external excel file. I hope it will be useful for you as well.
Please let me know if you would need any further assistance on the topic. Thanks!
Regards,
Elena Tsvetkova
Progress Telerik
Rank 1
Thank you a lot for replying to me. Now I figured out how extraction and verification works and that's great. I have a last question about extraction. Can I extract the selected value but only take the first 5 letters or numbers. For Example the element value is "Hello World" and I would like to extracted and stored in databind the first five letters "Hello". Is it possible?
Thank you for your cooperation. I am glad to know I managed to help you.
The next query you have could be easily accomplished in a coded step with a common C# approach to edit strings. There is no way to modify the extracted value within the UI of Test Studio but once you have the initial extracted string, modify it as per the requirements and simply assign it to another variable which could be then used for data binding.
I hope this will be useful as well.
Regards,
Elena Tsvetkova
Progress Telerik
Rank 1
Hello Elena,
I didn't found the answer I was looking for. I attached you some screenshots of my steps and vb coding I have.
On what I attached you I want for example the extracted value "123456789 - 5.50AED" to get and store on the excel only the first 9 characters
Best Regards
Thank you for the screenshots.
You would need to modify the extracted values before you write these to the excel file. Technically once there is a value extracted for the variable fromaccountselected (e.g. after step 10) use it in a coded step to modify the string as per your requirements and store the new desired string to a variable. Then use that new variable to enter its value in the excel file.
As fat as I could see from the screenshot the current coded step is placed somewhere after all of the extractions. Thus you could use it to modify the strings - simply do this before writing to the excel. In my previous reply I shared the MS docs article how to edit a string in C# which you will need to implement.
To be more clear the actions should be similar to these below:
1. Extract an element's value and store in a variable (could be either in code or through the UI).
2. In a coded step using the C# capabilities to edit string, modify the extracted variable value and store it to a different variable.
3 .Use the newly created variable to pass as an argument to write in the excel file.
Please let me know if that makes sense to you or further assistance will be required.
Regards,
Elena Tsvetkova
Progress Telerik
Rank 1
Hello Elena,
Your last comments was helpful but I did manage to isolate the string and save the exact characters I wanted by calling on vb the .Substring(num,num)
My last questions are about the date is stored in excel.
1)The label of the date which is extracted to string as dd/mm/yyyy on excel file stored it as mm/dd/yyyy. I tried to copy and paste the label text to excel manually to see it's behavior, it stores it correct as dd/mm/yyyy.
2)When I call the date from excel to verified a value. It expects the value to be for example "01/10/2018 00:00:00". Where the time is coming from?
I attached you some screenshots that may help
Thank you for getting back.
Both of your questions are actually related to specifics of Excel, the different formatting it provides for the cells and how the .Net framework process these. This is actually beyond the scope of Test Studio support as it is custom code implementation. Although I searched the web for you and here is a forum post which might be helpful for you in regards the first topic.
The second topic is related to how Excel stores dates and time. If you change the formatting of a date formatted cell to General you will see this is actually a serial number. Here you could find further details on the topic. This is something you have to keep in mind when you will be using dates in Excel.
I hope the additional notes will be useful to you to find the proper approach to handle these queries.
Regards,
Elena Tsvetkova
Progress Telerik