How can I open and define two Excel files in VBA?

Viewed 824

As a part of a bigger macro, and need to open and define two workbooks and sheets (I am aware of that I define my worksheets as Variant, I need this for future operations). I get a mistake when I try to set value to SheetRI. What can it be?

 Sub compareQRTsAll()

    Dim ActiveWb As Workbook
    Dim ActiveSh As Worksheet
    Dim SheetFasit As Variant
    Dim SheetRI As Variant
    Dim FolderFasit As String
    Dim FileFasit As String
    Dim FolderRI As String
    Dim FileRI As String
    Dim WbFasit As Workbook
    Dim WbRI As Workbook
    Dim WbFasitPath As String
    Dim strRangeToCheck As String
    Dim nShFasit As Integer
    Dim nShRI As Integer
    Dim iRow As Long
    Dim iCol As Long
    Dim i As Integer
    Dim j As Integer
    i = 2
    j = 6

    Set ActiveWb = ActiveWorkbook
    Set ActiveSh = ActiveWb.Worksheets(1)
    strRangeToCheck = "A1:AAA1000"
    ActiveSh.Range("A2:D10000").Clear

    FolderFasit = ActiveSh.Range("J6")
    FolderRI = ActiveSh.Range("J7")

    Do While ActiveSh.Cells(j, 8) <> ""

    FileFasit = Dir(FolderFasit & "\*" & ActiveSh.Cells(j, 8) & "*.xls*")
    Set WbFasit = Workbooks.Open(Filename:=FolderFasit & "\" & FileFasit)
    SheetFasit = WbFasit.Worksheets(1).Range(strRangeToCheck)
    nShFasit = WbFasit.Sheets.Count

    FileRI = Dir(FolderRI & "\*" & ActiveSh.Cells(j, 8) & "*.xls*")
    Set WbRI = Workbooks.Open(Filename:=FolderRI & "\" & FileRI)
    SheetRI = WbRI.Worksheets(1).Range(strRangeToCheck) '<-------------THIS DOESN'T WORK
    nShRI = WbRI.Sheets.Count


    If nShFasit <> nShRI Then
        MsgBox "QRT " & ActiveSh.Cells(j, 8) & " has different number of sheets in fasit and in RI. Further check will not be performed"

    ElseIf nShFasit = nShRI And nShFasit = 1 Then

        For iRow = LBound(SheetFasit, 1) To UBound(SheetFasit, 1)
            For iCol = LBound(SheetFasit, 2) To UBound(SheetFasit, 2)
                If SheetFasit(iRow, iCol) = SheetRI(iRow, iCol) Then

                    ' Do nothing.
                Else
                    ActiveSh.Cells(i, 1) = "Check row " & iRow & ", column " & iCol & " in " & ActiveSh.Cells(j, 8)
                    ActiveSh.Cells(i, 2) = SheetFasit(iRow, iCol)
                    ActiveSh.Cells(i, 3) = SheetRI(iRow, iCol)
                    i = i + 1
                End If
            Next iCol
        Next iRow
     
    End If


    'close workbooks
    Dim wb As Workbook
    For Each wb In Workbooks
        If Not wb Is ActiveWb Then
            wb.Close SaveChanges:=False
        End If
        Next wb

        j = j + 1
    Loop
End Sub
1 Answers
Related