Forum Discussion

dpeterson's avatar
dpeterson
Occasional Contributor
11 years ago

Force Recalculation of Datasheet

I'm building data-driven tests that include date/time sensitive information. The data sheets include Excel formulas to do things like compute the current date, the week prior to the current date, etc.



The problem is that the computed date values are all based on the last time the workbook was saved. So if I add a new record to the data sheet on Tuesday and execute my test, date values are as expected. However, if I run the same test on Wednesday, without making any changes to the workbook, the date values are all based on Tuesday. Certainly not what I expected, or intended.



So the questions are:



1. When the data-driven test begins to execute, is there a way to force the worksheet to recalculate, thus updating the date values?



2. Does anyone have a suggestion for a better/simpler way to accomplish the same thing?



One final caveat: Test projects and all their component data files, are maintained in a source code revision system, specifically TFS. I don't know if this makes a difference.
  • AlexKaras's avatar
    AlexKaras
    11 years ago
    Hi David,



    It is my understanding, that all means that read data from the Excel sheets (e.g. ADO, ODBC, data-driven drivers in TestComplete, etc.) just read data from the sheet. In order for the data to be recalculated, the file must be opened in Excel (which recalculates data on file open). So I think that your approach should be like this:

    -- Open file in Excel using it as a COM server (http://support.smartbear.com/viewarticle/20878/ might provide you with the overall idea of how to do this) before starting your tests. This should update data in the file;

    -- Save updated file and close it (and exit Excel);

    -- Use data-driven approach in TestComplete as usual.
  • AlexKaras's avatar
    AlexKaras
    Champion Level 3
    11 years ago
    Hi David,



    It is my understanding, that all means that read data from the Excel sheets (e.g. ADO, ODBC, data-driven drivers in TestComplete, etc.) just read data from the sheet. In order for the data to be recalculated, the file must be opened in Excel (which recalculates data on file open). So I think that your approach should be like this:

    -- Open file in Excel using it as a COM server (http://support.smartbear.com/viewarticle/20878/ might provide you with the overall idea of how to do this) before starting your tests. This should update data in the file;

    -- Save updated file and close it (and exit Excel);

    -- Use data-driven approach in TestComplete as usual.
  • dpeterson's avatar
    dpeterson
    Occasional Contributor
    11 years ago
    Starting from Alexei's suggestion, I came up with a solution that seems to be working. It starts with a function I wrote named "refresh_Workbook":




     

      function refresh_Workbook(fname) {

     

       

     

      var BIT_READONLY = 1;

     

      var BIT_HIDDEN = 2;

     

      var BIT_SYSTEM = 4;

     

      var BIT_DIRECTORY = 16;

     

      var BIT_ARCHIVE = 32;

     

      var BIT_ALL = 255;

     

       

     

      try {

     

      var fso = Sys.OleObject("Scripting.FileSystemObject");

     

      var file = fso.GetFile(fname);

     

      var wasReadOnly = file.Attributes & BIT_READONLY;

     

      // Save current read/write status.

     

       

     

      file.Attributes = file.Attributes & ~BIT_READONLY;

     

      // Set file to read/write.

     

       

     

      var xl = Sys.OleObject("Excel.Application");

     

      var workbook = xl.Workbooks.Open(fname);

     

      xl.DisplayAlerts = false;

     

      workbook.Save();

     

      xl.Quit();

     

       

     

      if (wasReadOnly) {

     

      file.Attributes = file.Attributes | BIT_READONLY;

     

      // Set file to read only.

     

      }

     

      return true;

     

      }

     

      catch(err) {

     

      Log.Error("refreshWorkbook()", "Error " + err.number + ", " + err.description);

     

      xl.Quit();

     

      return false;

     

      }

     

      }

     

      // function refreshWorkbook



     



    The var BIT_xxx I use as constants to identify the status bits for a file.



    The function first generates a file system object pointing at my Excel workbook so I can examine the status bits for that file. I keep the file's current read/write status for future reference.



    I then set the file to read/write status by toggling the read-only bit off. This is done by doing a bitwise or of the bitwise negation of the BIT_READONLY flag (sounds a bit like a DR. Seuss tongue-twister).



    I then create an Excel application object so I can open the workbook and save it again. This forces the recalculation, as was suggested.



    If necessary, I then reset the read only bit for the file.



  • dpeterson's avatar
    dpeterson
    Occasional Contributor
    11 years ago
    Yes, I like using the aqFileSystem object better. The function is cleaner and I think easier to read. This will be important if my client should ever have to update it after I've moved on. Here is the new function, I'll try to get the formatting right this time:




     function refresh_Workbook(fname) {


       if (aqFileSystem.Exists(fname)) {


         try {


           var wasReadOnly = aqFileSystem.CheckAttributes(fname, aqFileSystem.faReadOnly); // Save current read-only status for futur reference.


           // Use Excel to open, save and close the workbook. This forces the recalculation.


           var fsResult = aqFileSystem.ChangeAttributes(fname, aqFileSystem.faReadOnly, aqFileSystem.fattrFree); // Set file to read/write status.


           var xl = Sys.OleObject("Excel.Application");


           var workbook = xl.Workbooks.Open(fname);


           xl.DisplayAlerts = false; // Suppress confirmation dialog.


           workbook.Save();


           xl.Quit();


           // Restore the read only bit to its orginal state.


           if (wasReadOnly) { 


             fsResult = aqFileSystem.ChangeAttributes(fname, aqFileSystem.faReadOnly, aqFileSystem.fattrSet); // Set file back to read-only status.


           }


           return true;


         } catch(err) {


           Log.Error("refreshWorkbook()", "Error " + err.number + ", " + err.description);


           xl.Quit();


           return false;


         }


       } else {


         Log.Warning("refresh_Workbook()", "Excel workbook file '" + fname + "' not found.");


         return false;


       }


     } // function refreshWorkbook()

  • dpeterson's avatar
    dpeterson
    Occasional Contributor
    11 years ago
    Paul: I would also prefer that method, but client requirements are that the data be maintained in the spreadsheet.



    Alexei: Thanks for the suggestion. I think that will work.



    Dave
  • AlexKaras's avatar
    AlexKaras
    Champion Level 3
    11 years ago
    Hi David,



    Thank you for the update. It looks like that the problem is solved.

    One note: while it is perfectly OK to use FSO object, TestComplete also provides aqFileSystem.CheckAttributes and aqFileSystem.ChangeAttributes methods that can be used for the same.
  • dpeterson's avatar
    dpeterson
    Occasional Contributor
    11 years ago
    I'm familiar with FSO, so that's what I grab when I reach into the toolbox. I will have to take a look at the AQ utility though. It might be a little easier to read. Thanks for the tip.



    Dave