Multidimensional Array for Filesystem Object

[BikeToWork]

I want to use a two dimensional array to pull file properties such as Date Created, Size, etc. for a given file using the filesystem object in vba code. The first dimension of the array is the index of the file number in the directory (0,1,2,3, etc.). How can one redim preserve the first dimension of the array? I don't know in advance how big the first dimension of the array is going to be although the second dimension will be the same size every time. Does anyone have code for how to loop through files in a directory using the filesystem object in vba and save the filename and properties to a table? Thanks in advance for any help.

 

[MajP]

Are you hard set on using md arrays? I would think for ease of use I would use a user defined type or a custom class. Then make a collection populating it with the user defined type or the custom class. Would be a whole lot easier when it comes to writing to the table. Or just skip the whole class and save to a table/recordset. There is plenty of code for getting the file structure if you google.

 

[BikeToWork]

I am not wed to the md array, by any means. I have not written any classes (yet). What would this look like? Thanks for your response.

 

[JoeAtWork]

You can only redimension the last element of an array. Since you say the size of one of the dimensions will always be the same, why don't you just switch the dimensions around so that the static one is first and the dynamic one is second?

 

[MajP]

If you build a custom class and a custom collection, this becomes very easy to do and very intuitive. However, if you have never worked with class objects the learning curve may be steep. However, if you have the time to invest, you really should understand this concept. I will greatly expand what you can program in vba/vb.
Here is a pretty good example.

http://www.databaseadvisors.com/new...ng custom collections in microsoft access.asp

I thought you could use a user defined type instead of a custom class and add it to a collection, but I think that may not be doable/easy without defining the user defined type in a class anyways. You can add a user defined type to an array

first you need to define the Type.

Public Type MY_ARRAY_TYPE
lValue as Long
sTemp as String
iCount as Integer
End Type
The first line declares the type. It is Public so it can be accessed anywhere in the project. Then next three lines simply declare the values that are to be used in the type. You can have any almost variable type within a Type, including other Types. The last line closes the Type. If you are not familiar with Types then you should read up on them. They can be very useful in VB, especially when dealing with API calls, or arrays.
Now you declare the array like this:

Dim uArray() as MY_ARRAY_TYPE

This again may take some learning but it gets a lot more intuitive and easy to work with. Because the values of the type could have the same names as the actual file properties.

Public Type MY_File_TYPE
name as string
dateCreated as date
fileSize as long
.....
End Type