Forum Discussion

nancypnp's avatar
nancypnp
Occasional Contributor
10 years ago

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

Hi All, I am trying to compare two excel files with same sheet names and trying to save the result into a new excel file compariosn is done using DDT as i dont want to open the file, jus...
nancypnp's avatar
nancypnp
Occasional Contributor
10 years ago
Hi All,

I have changed the method as below, without using DDT

but 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


    ComparisionFlag = True  


  CurrentRow = 1


  RtnValue = False


  Redim TitleArray(8)


 


  LogFolder = Log.AppendFolder("Comparing Excel Sheets")


  


  RtnValue = False 


  Set Excel = Sys.OleObject("Excel.Application")


  set RefFile = Excel.Workbooks.Open(RefFileName)


  set FileToCompare = Excel.Workbooks.Open(FileToCompareName)


  RefFileColumnCount = RefFile.Sheets(RefSheetName).UsedRange.Columns.Count


  RefFileRowCount = RefFile.Sheets(RefSheetName).UsedRange.Rows.Count


  FileToCompareColumnCount = FileToCompare.Sheets(FileToCompareSheet).UsedRange.Columns.Count


  FileToCompareRowCount = FileToCompare.Sheets(FileToCompareSheet).UsedRange.Rows.Count


  ExcelColumnDifference = RefFileColumnCount - FileToCompareColumnCount


         


  If ExcelColumnDifference <> 0 Then


    Log.Message("The number of columns in:" & vbCrLf & "  " & RefSheetName & " of (" & RefFileName _


    & ") is " & vbCrLf & RefFileColumnCount)


    Log.Message("The number of columns in:" & vbCrLf + "  " & SheetToCompare & " of (" & FileToCompareName _


    & ") is " & vbCrLf & FileToCompareColumnCount)


    Log.Error ("The number of columns in both the Excel sheets are not equal")          


  Else


    For CurrentRow = 2 To RefFileRowCount 


      For ColumnIndex = 1 To RefFileColumnCount


        RefCellTextTitle = VarToStr(RefFile.Sheets(RefSheetName).Cells(2,ColumnIndex).text)


        RefCellText = VarToStr(RefFile.Sheets(RefSheetName).Cells(CurrentRow+1,ColumnIndex).text)


        FileToCompareCellText = VarToStr(FileToCompare.Sheets(RefSheetName).Cells(CurrentRow+1,ColumnIndex).text)


        


        If CurrentRow = 2 And RefCellTextTitle <> "" Then


          TitleArray(ColumnIndex) = RefCellTextTitle


        End If


        


        If Utilities.CompareStr(RefCellText , FileToCompareCellText) <> 0 Then


          If VarToStr(RefFile.Sheets(RefSheetName).Cells(CurrentRow,1).text) <> "" Then


            PanelNode = VarToStr(RefFile.Sheets(RefSheetName).Cells(CurrentRow+1,1).text)


            'log.Message("For Node: "&PanelNode& ""&vbLf&"" & TitleArray(ColumnIndex)& " Version: Before:" &RefCellText& ", After:" &FileToCompareCellText)


            Call SetValueToExcelUsingColumnDA(DistributedDownLoadSheet,nRowIndex,"SW_Version_Difference",_


             "For Node: "&PanelNode& ""&vbLf&"" & TitleArray(ColumnIndex)& " Version: Before:" &RefCellText& ", After:" &FileToCompareCellText&""&vbLf) 


          End If


          ComparisionFlag = False 


        End If


      Next


    CurrentRow = CurrentRow + 1 


    Next


  End If


 


  If ComparisionFlag Then


    Log.Message("Data in both the Excel Sheets are Equal")


    Call SetValueToExcelUsingColumnDA(DistributedDownLoadSheet,nRowIndex,"SW_Version_Difference","Software Version:Before Download and After Download is same") 


    RtnValue = True


  End If 


  


  set Excel = Nothing


  Log.PopLogFolder()


End Sub






Public function SetValueToExcelUsingColumnDA(sheetname,nRow,sCol,szdata)


   


  set Excel = Nothing


  Set Excel = GetObject(FileName)  


  usedColumnsCount = Excel.Sheets(sheetname).UsedRange.Columns.Count

(I am getting error)


  For i = 1 to usedColumnsCount 


    if  sCol = Excel.Sheets(sheetname).cells(1,i).text then


      nCol = i


      exit for


    end if


  Next  


  Excel.Sheets(sheetname).Cells(nRow,nCol).Value= Excel.Sheets(sheetname).Cells(nRow,nCol).Value +szdata 


  set Excel = Nothing


 


End function