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

Related Content

Clone Assertions throws exception

Throwing exceptions in script extensions

Postman importing from link throws nullpointer exception

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

Required parameter in function comments don't throw exception ?

Recent Discussions

Problem with generated data table and keyword test

System checks for Object for every test

SE Tip : Drag-and-drop one object to another

Unable to re-cert TestComplete-Certification

Tech article will not submit