Forum Discussion

JennyH11's avatar
JennyH11
Frequent Contributor
7 years ago
Solved

Do I need to load the excel spreadsheet as part of the test for data validation?

Hi

 

Many of the legacy tests I have use an excel spreadsheet for data validation and as part of this are opening the spreadsheet required before the point where it's needed in the test. The test steps include opening, clicking enable editing and then closing the spreadsheet. I would much prefer not having to include these steps each time as it seems unnecessary and takes up valuable time.

 

Is it necessary to have to open the spreadsheet before referring to it in a test? And what can be done instead?

 

 

Thanks!

13 Replies

Sort By
  • JennyH11's avatar
    JennyH11
    Frequent Contributor
    7 years ago
    Copy Link

    Thanks for the replies.

     

    I think I might understand this a bit better now..

     

    We use this spreadsheet for entering dates in the UI of the AUT. The dates are calculated by formulas in the spreadsheet, therefore, without opening the spreadsheet first the dates cannot be calculated. If the spreadsheet is not opened first the test will input the dates as stored at the last time of opening the spreadsheet.

     

    With this in mind, I'm not sure there is a way to use this spreadsheet without opening it first. A quick google suggests that there is no way to update the data in a spreadsheet without opening it first but maybe there is a way to use the data without opening that allows TestComplete to access it?

     

     

    Any thoughts? Ideas?

    • tristaanogre's avatar
      tristaanogre
      Esteemed Contributor
      7 years ago
      Copy Link

      What are the date forumulas used by the spreadsheet?  You could, rather than pulling the dates from the sheet, use various aqDateTime functions to generate the required dates and aqConvert.DateTimeToFormatStr to format the resulting date into the desired string format.  Might be a LOT easier than having to open and close the spreadsheet each time.

      • cunderw's avatar
        cunderw
        Community Hero
        7 years ago
        Copy Link

        I would second tristaanogre, if you are just needing calculated dates for input, using a built in / more native way of date manipulation would be a much better solution. 

    • tristaanogre's avatar
      tristaanogre
      Esteemed Contributor
      7 years ago
      Copy Link

      The only reason why your tasks may need to be running as they are would be if you need to write to the Excel file... but if all you're doing is reading, then you shouldn't need those opening steps.

  • JennyH11's avatar
    JennyH11
    Frequent Contributor
    7 years ago
    Copy Link

     

    Some examples of the dates we're calculating are: Today, Today +1, Today + 2 weeks, Next Monday, Next April, Next Monday.

     

    If we can do this in TestComplete that would be great.

     

    Thanks!

    • cunderw's avatar
      cunderw
      Community Hero
      7 years ago
      Copy Link

      That can absolutely be done in Test Complete with aqDateTime and the addTime method.

       

      https://support.smartbear.com/testcomplete/docs/reference/program-objects/aqdatetime/index.html

       

      function ChangingTime() {
  var Time1, Time2, Time3, Time4, Time5, Time6, Time7;

  Time1 = aqDateTime.Now();
  Time2 = aqDateTime.AddDays(Time1, 5);
  Time3 = aqDateTime.AddHours(Time1, 7);
  Time4 = aqDateTime.AddMinutes(Time1, 24);
  Time5 = aqDateTime.AddMonths(Time1, 2);
  Time6 = aqDateTime.AddSeconds(Time1, 15);
  Time7 = aqDateTime.AddTime(Time1, 9, 3, 24, 51);

  Log.Message(aqConvert.DateTimeToStr(Time1));
  Log.Message(aqConvert.DateTimeToStr(Time2));
  Log.Message(aqConvert.DateTimeToStr(Time3));
  Log.Message(aqConvert.DateTimeToStr(Time4));
  Log.Message(aqConvert.DateTimeToStr(Time5));
  Log.Message(aqConvert.DateTimeToStr(Time6));
  Log.Message(aqConvert.DateTimeToStr(Time7));
}
    • tristaanogre's avatar
      tristaanogre
      Esteemed Contributor
      7 years ago
      Copy Link

      Today -> aqConvert.DateTimeToFormatStr(aqDateTime.Today(), '%m/%d/%y') -> '06/12/2018'

      Today + 1 -> aqConvert.DateTimetoFormatStr(aqDateTime.AddDays(aqDateTime.Today(), 1), '%m/%d%Y') -> '06/13/2018'

      Today + 2 -> aqConvert.DateTimetoFormatStr(aqDateTime.AddDays(aqDateTime.Today(), 2), '%m/%d%Y') -> '06/14/2018'

       

      Getting "next Monday" or "next April" is going to be a bit trickier... I'm sure I could work it out given the time... I'm guessing that it will take some sort of switch statement to take the current date, determine day of week, and then depending on that result do another "AddDays" to get the following Monday... same thing with Month to get the current month, calculate then how many months to add and use aqDateTime.AddMonths to get to the "next April" date.

       

      But yes... it should be possible to use aqDateTime methods (https://support.smartbear.com/testcomplete/docs/reference/program-objects/aqdatetime/methods.html) to do exactly what you want without needing the spreadsheet to do the calculations

  • AdamUK's avatar
    AdamUK
    Occasional Contributor
    7 years ago
    Copy Link

    I've used Excel spreadsheets in my test easily enough, you create a variable and map it to the spreadsheet.  But that was only for data driven testing where it would use a different / the next row for each iteration of the loop.

     

    But yeah, don't see why you couldn't just call today and then add a unit per loop.  Not sure of the code but should be fairly easy to find.

  • JennyH11's avatar
    JennyH11
    Frequent Contributor
    7 years ago
    Copy Link

    Thanks everyone for the responses on this.

     

    I'll look into it a bit more & try it out. The information you've given is amazingly helpful! Thank you :)

  • JennyH11's avatar
    JennyH11
    Frequent Contributor
    7 years ago
    Copy Link
    I'm struggling with getting a spreadsheet \ variable working so thought it might be a good time to give this a go.
     
    One issue though - and this might be a really silly question - how do I tell the keyword test to enter the date calculated by the aqdatetime \ aqconvert methods? I'd assume it's using Keys or SetText at some point but am not sure how to do this.
     
    I'm starting off small with trying to do today + 1. Also, what should the "Input Date" be if I want it to be dynamic & adjust to whatever "today" is depending when the test is run.
     
    Thanks,
    Jenny