VBA for loading and accessing values from a 2D array

[trishcollins]

I am having trouble working with a 2 dimensional array. I have worked with a 1D array before, no problem. The Named Table I am loading from is called "EA-Libraries_Data", and it contains the list of the other tables on the same sheet that need to be populated. There are 3 tables. The second column of that table contains the drive letter to be used to find the data.

EA Libraries Drive
EA_WIP_DIR R:\
EASV2_DIR S:\
EA_Official_Dir T:\


I am trying to populate the three tables on the page with the subfolder names. I want to assign the EA Libraries name to the MyArrayTable variable, and the Drive to the MyArrayDir, so that as it goes through each loop, it takes the next Table name and the next Drive name. The only issue is, I can't seem to figure out how to reference the second column of the array. Here is the code:

Sub ListMyDir()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.Calculation = xlCalculationManual
    Application.DisplayStatusBar = True
    Application.StatusBar = "Importing Beehive Directories"
    Dim tblrow As Integer
    Dim myTable As ListObject
    Dim MyArray As Variant
    Dim TempArray As Variant
    Dim MyArrayTable As String
    Dim MyArrayDir As String
    Dim x As Integer
    Set myTable = ActiveSheet.ListObjects("EA_Libraries_Data")
    TempArray = myTable.DataBodyRange
    MyArray = Application.Transpose(TempArray)
    tblrow = 1
    On Error Resume Next
    For x = 1 To 3
' assign the array library name and drive
[B]    MyArrayTable = MyArray(x, 1)
    MyArrayDir = MyArray(x, 2)[/B]
    ActiveSheet.ListObjects(MyArrayTable).DataBodyRange.Delete
    Set MyObject = New Scripting.FileSystemObject
'assign the array drive as the source
    Set mySource = MyObject.GetFolder[B](MyArray(x, 2))[/B]
    On Error Resume Next
    For Each mySubFolder In mySource.SubFolders
    For Each myFile In mySource.Files
        ActiveSheet.ListObjects(MyArrayTable).ListRows.Add AlwaysInsert:=True
        ActiveSheet.ListObjects(MyArrayTable).DataBodyRange(tblrow, 1).Value = MyArrayDir
        tblrow = tblrow + 1
    Next
    Next
    Next x
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.StatusBar = ""
    Application.Calculation = xlCalculationAutomatic
End Sub

 

[Norie]

How many columns are in the table?

Have you checked the dimensions of the array after you've tranposed it?

Transposing an array will swap rows and columns and if you've only got one column you'll end up with a one-dimensional array.

 

[trishcollins]

The table has two columns. Column 1 is "EA Libraries" and column 2 is "Drive". I just couldn't cut and paste the table properly in this forum.

EA Libraries Drive
EA_WIP_DIR R:\
EASV2_DIR S:\
EA_Official_Dir T:\

 

[Norie]

If you had 3 rows as shown your array would be 1x3 by 1x2 at the start, after you transpose it it'll be 1x2 by 1x3.

Try not transposing the array.

 

[trishcollins]

If you had 3 rows as shown your array would be 1x3 by 1x2 at the start, after you transpose it it'll be 1x2 by 1x3.

Try not transposing the array.

Interesting. So I removed the transpose and got a little further. It will populate the first table it comes to in the list of tables ("EA_WIP_Dir" and Drive "R:\"), but not the second and third tables. I have a message box displaying the Table Name and the Drive it selects from the array in each loop, and it is accessing the correct row and column for the second and third rows, however, only the first table is being populated. I have tried sorting the list of table names to see if it was the table itself, or the code, and sure enough, whatever is in the first row, is the table that is populated, and the others are left blank. So, now I don't think it's an array problem, but another problem.

I am basically trying to populate tables with subfolder names, so they can be used in a dropdown menu on another sheet. I have used this successfully before but in a slightly different way without the array, so I was thinking that was the problem. Any suggestions?

Sub ListMyDir()
'    Application.ScreenUpdating = False
'    Application.DisplayAlerts = False
    Application.Calculation = xlCalculationManual
    Application.DisplayStatusBar = True
    Application.StatusBar = "Importing Beehive Directories"
    Dim tblrow As Integer
    Dim myTable As ListObject
    Dim MyArray As Variant
    Dim MyArrayTable As String
    Dim MyArrayDir As String
    Dim x As Integer
    Set myTable = ActiveSheet.ListObjects("EA_Libraries_Data")
    MyArray = myTable.DataBodyRange
    tblrow = 1
    On Error Resume Next
    For x = 1 To 3
    MyArrayTable = MyArray(x, 1)
    MyArrayDir = MyArray(x, 2)
    MsgBox "My Table: " & MyArrayTable & "  My Directory:  " & MyArrayDir
    ActiveSheet.ListObjects(MyArrayTable).DataBodyRange.Delete
    Set MyObject = New Scripting.FileSystemObject
    Set mySource = MyObject.GetFolder(MyArrayDir)
'    On Error Resume Next
    For Each mySubFolder In mySource.SubFolders
    ActiveSheet.ListObjects(MyArrayTable).ListRows.Add AlwaysInsert:=True
        ActiveSheet.ListObjects(MyArrayTable).DataBodyRange(tblrow, 1).Value = mySubFolder
        tblrow = tblrow + 1
    Next
    Next x
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.StatusBar = ""
    Application.Calculation = xlCalculationAutomatic
End Sub

 

[trishcollins]

Never mind, I found the problem. The tblrow = 1 needed to be in the loop, it was before it, so the second and third tables were having issues going to a row that didn't exist

It's all good. Thanks!