Forum Discussion

Azeddin_Margani's avatar
Azeddin_Margani
Contributor
8 years ago
Solved

To write test Results into a column in excel

Hi,   I am getting test data from an excel file and wish to write the result for a particular test into a specific column called "CardNumber" on the same row (in the same excel file) . I'm using DD...
  • Colin_McCrae's avatar
    Colin_McCrae
    8 years ago
    Azeddin_Margani wrote:

     

     inputRow = excelMainSheet.UsedRange.Rows.Count;

    excelMainSheet.Range("K" + inputRow).Value =CardNum;

     

    OK. You're not mixing COM and DDT. That's good. :)

     

    As previously explained, the above code is your problem. UsedRange gets the last row. So you are always writing to the last row.

     

    If this is looping through 100 rows of data, I assume you must have it in some sort of loop?

     

    In which case (pseudocode) you want something like:

     

    Loop Start

   Do your test and extract the value
   
   Excel.Cells(Loop, Column) = Your_Value

End Loop

     

    Or something like that.

     

    Basically, as shankar_r says, you need to track the row you are on. Which I presume you are (or will be) doing if you're using the same sheet for you input data?

     

    (Incidentally, I've used the "Cells" method in Excel. Which uses a numeric for both Row and Column. You can use Range, but it's really designed for updating multiple cells in one go. But it will work with a single cell value.)

shankar_r's avatar
shankar_r
Community Hero
8 years ago

I usually do like this,

 

                  excelObject = Sys.OleObject("Excel.Application");
                  excelObject.Visible = false;
                  excelObject.DisplayAlerts = false;
           
                  excelWorkbookObj = excelObject.Workbooks.Open(strexcelFileName);
                  excelMainSheet = excelWorkbookObj.Sheets.Item("Execution_Report");
                  inputRow = excelMainSheet.UsedRange.Rows.Count + 1;
            
                  excelMainSheet.Range("A" + inputRow).Value = vSNo;

  • Azeddin_Margani's avatar
    Azeddin_Margani
    Contributor
    8 years ago

    Hi Shankar,

     

    I still can't get it to write to the specified column. I've tried to change the range to different combination and still not writing into the specified column. What should happen, each time a row is executed, the value of CardNum should be inserted in column name "CardNumber" against that row.  

     

    Any suggestion?

     

    Regards,

    Az.

    • shankar_r's avatar
      shankar_r
      Community Hero
      8 years ago

      Can you tell me what kind of error you are getting.

       

      May be you should try something like,

       

      function fn_addexcelrowvalue(str_InputValue)
      {
                  var excelObject, excelWorkbookObj,excelMainSheet,inputRow;
                  try
                  {
                  
                        excelObject = Sys.OleObject("Excel.Application");
                        excelObject.Visible = false;
                        excelObject.DisplayAlerts = false;
                 
                        excelWorkbookObj = excelObject.Workbooks.Open(<Excel File Name>);
                        excelMainSheet = excelWorkbookObj.Sheets.Item("<Excel Sheet Name>");
                        inputRow = excelMainSheet.UsedRange.Rows.Count + 1;
                  
                        excelMainSheet.Range("A" + inputRow).Value = str_InputValue;
                        
                        excelWorkbookObj.Save();
                        excelWorkbookObj.Close();
                  }
                  catch(ex)
                  {
                        //Add exepection code
                  }
                  finally
                  {
                        excelObject.Visible = true;
                        excelObject.DisplayAlerts = true;
                        excelObject.Quit()
                        //CleaningUp
                        excelObject = null;
                        excelWorkbookObj = null;
                        excelMainSheet = null;   
                  }  
           
      }

      • Azeddin_Margani's avatar
        Azeddin_Margani
        Contributor
        8 years ago

        I'm not getting any error just - just not writing the specified column?.