DDT excel driver and Sys.OleObject("Excel.Application") are throwing exception

Question

Hi All,I am trying to compare two excel files with same sheet names and trying to save the result into a new excel filecompariosn is done using DDT as i dont want to open the file, just want to readand result update to new excel i am using Sys.OleObject("Excel.Application")&nbsp;as i need to open excel and set the resultplease do help me with this Function CompareExcelCellValues(nRowIndex,SheetName,RefFileDriver, FileToCompareDriver) Dim NumberOfColumns, ColumnIndex, CurrentRow, RtnValue Dim RefCellText,FileToCompareCellText, ComparisionFlag, TitleArray() &nbsp;&nbsp; &nbsp; ComparisionFlag = True &nbsp; &nbsp; CurrentRow = 0 &nbsp; RtnValue = False &nbsp; Redim TitleArray(8) &nbsp; &nbsp; NumberOfColumns = RefFileDriver.ColumnCount &nbsp;&nbsp; &nbsp; If RefFileDriver.EOF() And FileToCompareDriver.EOF() then&nbsp; &nbsp; &nbsp; RtnValue = True &nbsp; &nbsp; Log.Message("EOF reached for both the files in the begining itself."+vbCrLf+_ &nbsp; &nbsp; "No Data is present.") &nbsp; &nbsp; CompareExcelCellValues = RtnValue &nbsp; &nbsp; Exit Function &nbsp;&nbsp; &nbsp; Else &nbsp; &nbsp; &nbsp; &nbsp; While Not RefFileDriver.EOF() And Not FileToCompareDriver.EOF() &nbsp; &nbsp; &nbsp; For ColumnIndex = 0 To NumberOfColumns - 1 &nbsp; &nbsp; &nbsp; &nbsp; RefCellText = VarToStr(RefFileDriver.value(ColumnIndex)) &nbsp; &nbsp; &nbsp; &nbsp; RefCellTextName = VarToStr(RefFileDriver.ColumnName(ColumnIndex)) &nbsp; &nbsp; &nbsp; &nbsp; FileToCompareCellText = VarToStr(FileToCompareDriver.value(ColumnIndex)) &nbsp; &nbsp; &nbsp; &nbsp; FileToCompareCellTextName = VarToStr(FileToCompareDriver.ColumnName(ColumnIndex)) &nbsp; &nbsp; &nbsp; &nbsp; If CurrentRow = 0 And RefCellText &lt;&gt; "" Then &nbsp; &nbsp; &nbsp; &nbsp; TitleArray(ColumnIndex) = RefCellText 'TitleArray(0) = "" &nbsp; &nbsp; &nbsp; End If &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; If Utilities.CompareStr(RefCellText , FileToCompareCellText) &lt;&gt; 0 Then &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; If VarToStr(RefFileDriver.value(0)) &lt;&gt; "" Then &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; PanelNode = VarToStr(RefFileDriver.value(0)) ' &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;DDT.CloseDriver(RefFileDriver.Name) ' &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;DDT.CloseDriver(FileToCompareDriver.Name) &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Call SetValueToExcelUsingColumnDA(DistributedDownLoadSheet,nRowIndex,"SW_Version_Difference",_ &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "For Node: "&amp;PanelNode&amp; ""&amp;vbLf&amp;"" &amp; TitleArray(ColumnIndex)&amp; " Version: Before:" &amp;RefCellText&amp; ", After:" &amp;FileToCompareCellText&amp;""&amp;vbLf)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; End if &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ComparisionFlag = False &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; End If &nbsp; &nbsp; &nbsp; &nbsp; Next &nbsp; &nbsp; &nbsp; &nbsp; CurrentRow = CurrentRow + 1 'go to next row &nbsp; &nbsp; &nbsp; RefFileDriver.Next() &nbsp; &nbsp; &nbsp; FileToCompareDriver.Next()&nbsp; &nbsp; &nbsp; &nbsp; If RefFileDriver.EOF() XOR FileToCompareDriver.EOF() then&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; RtnValue = False&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CompareExcelCellValues = RtnValue &nbsp; &nbsp; &nbsp; &nbsp; Exit Function &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; End If&nbsp; &nbsp; &nbsp; Wend &nbsp; &nbsp; End If&nbsp; &nbsp; DDT.CloseDriver(RefFileDriver.Name) &nbsp; DDT.CloseDriver(FileToCompareDriver.Name) &nbsp; If ComparisionFlag Then &nbsp; &nbsp; Log.Message("Data in both the Excel Sheets are Equal") &nbsp; &nbsp; Call SetValueToExcelUsingColumnDA(DistributedDownLoadSheet,nRowIndex,"SW_Version_Difference","Software Version:Before Download and After Download is same")&nbsp; &nbsp; &nbsp; RtnValue = True &nbsp; End If&nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; CompareExcelCellValues = RtnValue End Function &nbsp; Public function SetValueToExcelUsingColumnDA(sheetname,nRow,sCol,szdata) &nbsp; Set Excel = Sys.OleObject("Excel.Application") &nbsp; if Excel.Visible then&nbsp; &nbsp; &nbsp;usedColumnsCount = Excel.Sheets(sheetname).UsedRange.Columns.Count &nbsp; &nbsp; For i = 1 to usedColumnsCount&nbsp; &nbsp; &nbsp; &nbsp; if &nbsp;sCol = Excel.Sheets(sheetname).cells(1,i).text then &nbsp; &nbsp; &nbsp; &nbsp; nCol = i &nbsp; &nbsp; &nbsp; &nbsp; exit for &nbsp; &nbsp; &nbsp; end if &nbsp; &nbsp; Next &nbsp; &nbsp; &nbsp; &nbsp; Excel.Sheets(sheetname).Cells(nRow,nCol).Value= Excel.Sheets(sheetname).Cells(nRow,nCol).Value +szdata&nbsp; &nbsp; &nbsp; End If End function

nancypnp · Answer

Hi All,I have changed the method as below, without using DDTbut i am getting " subscript out of range" for cloumn count Sub CompareExcelSheets(nRowIndex,RefFileName, RefSheetName, FileToCompareName, FileToCompareSheet) Dim RefFileDriver, FileToCompareDriver, RtnValue Dim RefFileColumnCount, FileToCompareColumnCount, ExcelColumnDifference &nbsp; &nbsp; ComparisionFlag = True &nbsp; &nbsp; CurrentRow = 1 &nbsp; RtnValue = False &nbsp; Redim TitleArray(8) &nbsp; &nbsp; LogFolder = Log.AppendFolder("Comparing Excel Sheets") &nbsp;&nbsp; &nbsp; RtnValue = False&nbsp; &nbsp; Set Excel = Sys.OleObject("Excel.Application") &nbsp; set RefFile = Excel.Workbooks.Open(RefFileName) &nbsp; set FileToCompare = Excel.Workbooks.Open(FileToCompareName) &nbsp; RefFileColumnCount = RefFile.Sheets(RefSheetName).UsedRange.Columns.Count &nbsp; RefFileRowCount = RefFile.Sheets(RefSheetName).UsedRange.Rows.Count &nbsp; FileToCompareColumnCount = FileToCompare.Sheets(FileToCompareSheet).UsedRange.Columns.Count &nbsp; FileToCompareRowCount = FileToCompare.Sheets(FileToCompareSheet).UsedRange.Rows.Count &nbsp; ExcelColumnDifference = RefFileColumnCount - FileToCompareColumnCount &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; If ExcelColumnDifference &lt;&gt; 0 Then &nbsp; &nbsp; Log.Message("The number of columns in:" &amp; vbCrLf &amp; " &nbsp;" &amp; RefSheetName &amp; " of (" &amp; RefFileName _ &nbsp; &nbsp; &amp; ") is " &amp; vbCrLf &amp; RefFileColumnCount) &nbsp; &nbsp; Log.Message("The number of columns in:" &amp; vbCrLf + " &nbsp;" &amp; SheetToCompare &amp; " of (" &amp; FileToCompareName _ &nbsp; &nbsp; &amp; ") is " &amp; vbCrLf &amp; FileToCompareColumnCount) &nbsp; &nbsp; Log.Error ("The number of columns in both the Excel sheets are not equal") &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Else &nbsp; &nbsp; For CurrentRow = 2 To RefFileRowCount&nbsp; &nbsp; &nbsp; &nbsp; For ColumnIndex = 1 To RefFileColumnCount &nbsp; &nbsp; &nbsp; &nbsp; RefCellTextTitle = VarToStr(RefFile.Sheets(RefSheetName).Cells(2,ColumnIndex).text) &nbsp; &nbsp; &nbsp; &nbsp; RefCellText = VarToStr(RefFile.Sheets(RefSheetName).Cells(CurrentRow+1,ColumnIndex).text) &nbsp; &nbsp; &nbsp; &nbsp; FileToCompareCellText = VarToStr(FileToCompare.Sheets(RefSheetName).Cells(CurrentRow+1,ColumnIndex).text) &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; If CurrentRow = 2 And RefCellTextTitle &lt;&gt; "" Then &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; TitleArray(ColumnIndex) = RefCellTextTitle &nbsp; &nbsp; &nbsp; &nbsp; End If &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; If Utilities.CompareStr(RefCellText , FileToCompareCellText) &lt;&gt; 0 Then &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; If VarToStr(RefFile.Sheets(RefSheetName).Cells(CurrentRow,1).text) &lt;&gt; "" Then &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; PanelNode = VarToStr(RefFile.Sheets(RefSheetName).Cells(CurrentRow+1,1).text) &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'log.Message("For Node: "&amp;PanelNode&amp; ""&amp;vbLf&amp;"" &amp; TitleArray(ColumnIndex)&amp; " Version: Before:" &amp;RefCellText&amp; ", After:" &amp;FileToCompareCellText) &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Call SetValueToExcelUsingColumnDA(DistributedDownLoadSheet,nRowIndex,"SW_Version_Difference",_ &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;"For Node: "&amp;PanelNode&amp; ""&amp;vbLf&amp;"" &amp; TitleArray(ColumnIndex)&amp; " Version: Before:" &amp;RefCellText&amp; ", After:" &amp;FileToCompareCellText&amp;""&amp;vbLf)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; End If &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ComparisionFlag = False&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; End If &nbsp; &nbsp; &nbsp; Next &nbsp; &nbsp; CurrentRow = CurrentRow + 1&nbsp; &nbsp; &nbsp; Next &nbsp; End If &nbsp; &nbsp; If ComparisionFlag Then &nbsp; &nbsp; Log.Message("Data in both the Excel Sheets are Equal") &nbsp; &nbsp; Call SetValueToExcelUsingColumnDA(DistributedDownLoadSheet,nRowIndex,"SW_Version_Difference","Software Version:Before Download and After Download is same")&nbsp; &nbsp; &nbsp; RtnValue = True &nbsp; End If&nbsp; &nbsp;&nbsp; &nbsp; set Excel = Nothing &nbsp; Log.PopLogFolder() End Sub     Public function SetValueToExcelUsingColumnDA(sheetname,nRow,sCol,szdata)   &nbsp; &nbsp;   &nbsp; set Excel = Nothing   &nbsp; Set Excel = GetObject(FileName) &nbsp;   &nbsp; usedColumnsCount = Excel.Sheets(sheetname).UsedRange.Columns.Count  (I am getting error)   &nbsp; For i = 1 to usedColumnsCount&nbsp;   &nbsp; &nbsp; if &nbsp;sCol = Excel.Sheets(sheetname).cells(1,i).text then   &nbsp; &nbsp; &nbsp; nCol = i   &nbsp; &nbsp; &nbsp; exit for   &nbsp; &nbsp; end if   &nbsp; Next &nbsp;   &nbsp; Excel.Sheets(sheetname).Cells(nRow,nCol).Value= Excel.Sheets(sheetname).Cells(nRow,nCol).Value +szdata&nbsp;   &nbsp; set Excel = Nothing   &nbsp;   End function

nancypnp · Answer

And an addition to above.I am getting this error only when i run the code from main flow, but no error is showed when the routine is executed

Forum Discussion

DDT excel driver and Sys.OleObject("Excel.Application") are throwing exception

2 Replies

Related Content

Clone Assertions throws exception

Throwing exceptions in script extensions

Postman importing from link throws nullpointer exception

Unable to write more than 255 characters in excel cell using Sys.OleObject["Excel.Application"]

Previously working code w.r.t Sys.OleObject("Excel.Application") not working with 12.42

Recent Discussions

Runner.Stop(true) stops complete execution, not just the current test case

Screenshots in Keyword test to document steps

Is it possible to extract data from the cells of a grid which is not getting identified as a table

How to make objects visible in the object tree?

Execute parallel Test with Selenium Server