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:

Please Login or Register  to view this content.

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...

Please Login or Register  to view this content.

...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

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

Got it.

Continuing on, I still get an error here:

Please Login or Register  to view this content.

"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!