Open and define two excel files in VBA

Question

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 futhure operations). I get a mistake when I try to set value to SheetRI. Does anyone see what it can be? Thanks in advance!

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 what is the value of "strRangeToCheck" 2 What is the exact error message that you are getting? — Siddharth Rout
strRangeToCheck = "A1:AAA1000". And I get 1004 (Application-defined or Object-defined error) — Marichka Sakhanda
Set SheetRI = WbRI.Worksheets(1).Range(strRangeToCheck) You have Set or you do not have Set? The top code doesn't match with the below code — Siddharth Rout

Marichka Sakhanda Marichka Sakhanda · Accepted Answer · 2017-06-02T12:56:20

The problem was in range strRangeToCheck = "A1:AAA1000". Some of my files are saved as .xls, and there is no AAA column on Excel 2003.

    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:IV1000"
ActiveSh.Range("A2:D10000").Clear


' If you know the data will only be in a smaller range, reduce the size of the ranges above.

'FolderFasit = InputBox("Enter path to the forder with correct QRTs")
'FolderRI = InputBox("Enter path to the forder with QRTs from RI")
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)
Debug.Print FileRI
SheetRI = WbRI.Worksheets(1).Range(strRangeToCheck)
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

Open and define two excel files in VBA

1 Answers