Monday, 19 June 2017
Open and define two excel files in VBA
Answer
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
Subscribe to:
Post Comments (Atom)
-
A fair amount of the second act of The Dark Knight Rises has a class warfare plotline. This is foreshadowed in the trailers with Selina Ky...
-
I'm still trying to wrap my head around how the following expression results in undefined behavior: a = a++; Upon searching SO...
-
i have added this sql in my code , function devenir_client_dataforform() { $type = $_POST['clientType']; //$produit...
No comments:
Post a Comment