How to name opened workbooks

I figured out how to open five workbook, count the number of rows, close them, and print out the results.

To do this, I only opened one workbook at a time, set it as active, saved the count in a variable, and closed the workbook.

What I want to do now, though, is make sure the dates align on every row. I want to do this with all five workbooks open at once. To do this, don't I have to give each workbook a name? If so, then how do I assign the name and do I have to dim that name?

Okay. I was on the right track... here's my code thus far:

Option Explicit
Option Base 1

Sub CompareDatesAcrossWorkbooks()

Dim Filename(5) As String
'CLfn As String, GCfn As String, USfn As String, ECfn As _
String, ESfn As String
'Dim CLrc As Integer, GCrc As Integer, USrc As Integer, ECrc _
As Integer, ESrc As Integer 'These are number of rows in each file
Dim tWB As Workbook, aWB As Workbook
Dim i As Integer, j As Integer, match As Integer, nomatch As _
Integer, nr As Integer
Dim Workbook(5) As Workbook

Set tWB = ThisWorkbook

Columns("A:D").ClearContents

Filename(1) = "C:\Users\drkle\Desktop\Desktop Transfer Material\Stock Market\TradeStation\Correlation data\CLdata.csv"
Filename(2) = "C:\Users\drkle\Desktop\Desktop Transfer Material\Stock Market\TradeStation\Correlation data\GCdata.csv"
Filename(3) = "C:\Users\drkle\Desktop\Desktop Transfer Material\Stock Market\TradeStation\Correlation data\USdata.csv"
Filename(4) = "C:\Users\drkle\Desktop\Desktop Transfer Material\Stock Market\TradeStation\Correlation data\ECdata.csv"
Filename(5) = "C:\Users\drkle\Desktop\Desktop Transfer Material\Stock Market\TradeStation\Correlation data\ESdata.csv"

match = 0
nomatch = 0

For i = 1 To 5:
    Workbooks.Open Filename(i)
    Workbook(i) = Filename(i)
    'Set aWB = Workbook(i)
Next i

Range("A1").CurrentRegion.Select
nr = Selection.Rows.Count

For j = 2 To nr:
    If Workbook(1).Cells(j, 1) = Workbook(2).Cells(j, 1) And _
    Workbook(2).Cells(j, 1) = Workbook(3).Cells(j, 1) And _
    Workbook(3).Cells(j, 1) = Workbook(4).Cells(j, 1) And _
    Workbook(4).Cells(j, 1) = Workbook(5).Cells(j, 1) Then
        match = match + 1
    Else
        nomatch = nomatch + 1
    End If
Next j

For k = 1 To 5:
    Workbook(i).Close SaveChanges:=False
Next k

Range("A1").Value = match 'Doesn't matter whether .Value is at the end
Range("A2") = nomatch

Range("B1") = "dates matched across all five WBs"
Range("B2") = "dates did not match across all five WBs"

End Sub

In the "For i" loop, as written I get an "object variable or with block variable not set." If I comment out the second line and uncomment out the third line, though, then it works. Why?

Also in the "For j" loop, I get the same "object variable or with block variable not set" on the long compound if line (four conditions). Why?

Thank you!

I appreciate the brevity of your version. I didn't know you could combine sPath & avsFile like that.

What about mine is giving the "object variable or with block variable not set" error? I can't see what I failed to initialize. If it's something about my not understanding details of the objects then I really want to learn so I don't make such mistakes in the future.

Just to clarify, with regard to the "For i" loop if I comment out the second line and uncomment out the third line, then it works and all five workbooks open. I still run into that same error in the "For j" loop, though.

Originally Posted by shg

1. Workbook is a data type in the Excel object model. You don't want to use it as a variable name.

2. Object variables require the Set keyword.

I replaced Workbook() with Asset().

I also set aWB = ActiveWorkbook.

I'm still getting errors in the same places. One thing I'm noticing in the Locals window is that when a workbook opens...

    Set Asset(i) = aWB

...is not assigning the newly opened workbook to Asset(i). When a workbook opens, isn't it active by default? If not, then how do I assign the newly-opened workbook to the Asset array? I tried with the line

Set Asset(i) = Filename(i)

but this results in a mismatch error (I'm guessing because the former is an array of workbooks and the latter is an array of strings).

Thanks!

Originally Posted by shg

Post your code, Mark.

Sub CompareDatesAcrossWorkbooks()

Dim Filename(5) As String
Dim tWB As Workbook, aWB As Workbook
Dim i As Integer, j As Integer, match As Integer, nomatch As _
Integer, nr As Integer, k As Integer
Dim Asset(5) As Workbook

Set tWB = ThisWorkbook
Set aWB = ActiveWorkbook 'Forgot this line

Columns("A:D").ClearContents

Filename(1) = "C:\Users\drkle\Desktop\Desktop Transfer Material\Stock Market\TradeStation\Correlation data\CLdata.csv"
Filename(2) = "C:\Users\drkle\Desktop\Desktop Transfer Material\Stock Market\TradeStation\Correlation data\GCdata.csv"
Filename(3) = "C:\Users\drkle\Desktop\Desktop Transfer Material\Stock Market\TradeStation\Correlation data\USdata.csv"
Filename(4) = "C:\Users\drkle\Desktop\Desktop Transfer Material\Stock Market\TradeStation\Correlation data\ECdata.csv"
Filename(5) = "C:\Users\drkle\Desktop\Desktop Transfer Material\Stock Market\TradeStation\Correlation data\ESdata.csv"

match = 0
nomatch = 0

For i = 1 To 5:
    Workbooks.Open Filename(i)
    'Set Asset(i) = Filename(i)
    Set Asset(i) = aWB
Next i

Range("A1").CurrentRegion.Select
nr = Selection.Rows.Count

For j = 2 To nr:
    If Asset(1).Cells(j, 1) = Asset(2).Cells(j, 1) _
    And Asset(2).Cells(j, 1) = Asset(3).Cells(j, 1) _
    And Asset(3).Cells(j, 1) = Asset(4).Cells(j, 1) _
    And Asset(4).Cells(j, 1) = Asset(5).Cells(j, 1) Then
        match = match + 1
    Else
        nomatch = nomatch + 1
    End If
Next j

For k = 1 To 5:
    Asset(i).Close SaveChanges:=False
Next k

Range("A1").Value = match 'Doesn't matter whether .Value is at the end
Range("A2") = nomatch

Range("B1") = "dates matched across all five WBs"
Range("B2") = "dates did not match across all five WBs"

End Sub

Originally Posted by rorya

You only set aWb once, so it is the workbook that was active at that moment. It doesn't adjust as you open workbooks to always be whichever one is active. But you don't need it anyway - what you want, as shg said earlier, is:

For i = 1 To 5:
     Set Asset(i) = Workbooks.Open(Filename(i))
Next i

Got it.

Continuing on, I still get an error here:

    If Asset(1).Cells(j, 1) = Asset(2).Cells(j, 1) _
    And Asset(2).Cells(j, 1) = Asset(3).Cells(j, 1) _
    And Asset(3).Cells(j, 1) = Asset(4).Cells(j, 1) _
    And Asset(4).Cells(j, 1) = Asset(5).Cells(j, 1) Then

"Object doesn't support this property or method." I think I'm trying to use the Cells property on a workbook... is that a problem?

This portion works now. Thanks so much shg and rorya!