Create array of file names/sheet names

Two part question:

1) I'm relatively new to arrays, but what I need to do is generate a list of file names and the sheets within each one. I would like to use an array for this, but since I don't have much experience.... well....that's why I'm here. Can someone point me in the right direction?

2) And the second part of this.... I was planning on using the FileSystemObject to determine the files in a selected folder and loop through that list of files, opening each one and harvesting the required info (file name and all sheet names). Should I use the FSO or is there something built into Excel that might be better (and also limit the number of dependencies for this little "project" of mine).

Thanks in advance.

Matt W

Matt

See how this goes.

Please Login or Register  to view this content.

Put it into a general module, change the path name as required (I'd pick a small test directory with only a couple of files), and see how it goes.

rylo

W/O FSO

Please Login or Register  to view this content.

Originally Posted by rylo

Matt

See how this goes.

Please Login or Register  to view this content.

Put it into a general module, change the path name as required (I'd pick a small test directory with only a couple of files), and see how it goes.

rylo

rylo:
It works great! Thanks.

So if I'm not mistaken (from my online searching I've been doing regarding this) you've created a UDT jagged array. Is that correct? Or is a UDT array (by nature) considered "jagged"?

Thanks again,
Matt W

Originally Posted by jindon

W/O FSO

Please Login or Register  to view this content.

jindon:
Works great!
Help me out here...
Can you explain what's going on with this line?

Please Login or Register  to view this content.

Thanks!
Matt W

Matt

"Jagged". Don't know the term. Can you explain / define, or post a reference that defines things.

rylo

Matt W,

It's called "Jagged Array", Arrays in an Array.
Used 2 array variables, a & w
a For 2D array, which holds fn(FileName) in its 1st row and array of sheet names (w).

Please Login or Register  to view this content.

Since we don't know the size (number of files in a directory), we need to expand the size of array a as if finds new file.

Please Login or Register  to view this content.

Store file name in 1st row and array of sheet names in 2nd row of "n"th column.

Does this help ?

Originally Posted by jindon

Matt W,

It's called "Jagged Array", Arrays in an Array.
Used 2 array variables, a & w
a For 2D array, which holds fn(FileName) in its 1st row and array of sheet names (w).

Please Login or Register  to view this content.

Since we don't know the size (number of files in a directory), we need to expand the size of array a as if finds new file.

Please Login or Register  to view this content.

Store file name in 1st row and array of sheet names in 2nd row of "n"th column.

Does this help ?

Yup. One last question though... What's the deal with the colon in the middle of the code? Is that just a way of putting two lines of code onto one line? I've never seen a colon used like that before in VBA.

Thanks again!
Matt W

Originally Posted by BVHis

One last question though... What's the deal with the colon in the middle of the code? Is that just a way of putting two lines of code onto one line?

That's what it does.

Originally Posted by jindon

That's what it does.

Cool! Thanks again!

Matt W