I an gleaned this off the internet and modified it a little. This is a start, but I want to be able to select the file(s) later, and I am running this in Access off an command button.
I did an update after I found some coding changes see Orange text. I get a runtime error 429 Can't create object on the Bold red text. How do I go about makingg an excel and exporting the output to the new Excel file?
Code:
Public Sub comparetest_Click()
Dim varSheetA As Variant
Dim varSheetB As Variant
Dim strRangeToCheck As String
Dim iRow As Long
Dim iCol As Long
Dim NRow As Long
Dim NCol As Long
Dim xlApp As Excel.Application
Dim WkBkA As Excel.Workbook
Dim WkBkB As Excel.Workbook
Dim WkBkC As Excel.Workbook
strRangeToCheck = "A1:I137"
NRow = 1
NCol = 1
Set WkBkA = Workbooks.Open("C:\Temp\BalanceSheet.xlsx")
Set varSheetA = WkBkA.Worksheets("Data").Range(strRangeToCheck)
Set WkBkB = Workbooks.Open(FileName:="C:\Temp\BalanceSheet_old.xlsx")
Set varSheetB = WkBkB.Worksheets("Data").Range(strRangeToCheck)
Set xlApp = CreateObject("Excel.Applpication")
With xlApp
Set WkBkC = Workbooks.Add("C:\Temp\Comaprison.xls")
End With
varSheetA = Worksheets("Sheet1").Range(strRangeToCheck)
varSheetB = Worksheets("Sheet2").Range(strRangeToCheck)
For iRow = LBound(varSheetA, 1) To UBound(varSheetA, 1)
For iCol = LBound(varSheetA, 2) To UBound(varSheetA, 2)
If varSheetA(iRow, iCol) = varSheetB(iRow, iCol) Then
Else
Cells(NRow, NCol) = varSheetA(iRow, 2)
Cells(NRow, NCol + 1) = varSheetA(iRow, iCol)
Cells(NRow, NCol + 2) = varSheetB(iRow, iCol)
Cells(NRow, NCol + 3) = NRow
Cells(NRow, NCol + 4) = NCol
NRow = NRow + 1
End If
Next iCol
Next iRow
End Sub