Forum Discussion

Azeddin_Margani's avatar
Azeddin_Margani
Contributor
8 years ago

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 DDT.ExcelDriver to loop through the data rows.

 

The value of the CardNumber is extracted from:

var CardNum = tabControl.m_tpgCards.RegisteredCardsForm.m_Cards.wSelectedItems;

 

Any suggestion on how to code this using JavaScript have given the value of CardNum (above)  for each row?.

 

Thanks in advanced...

 

Azeddin.

 

 

 

 

  • 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;   
                    }  
             
        }