GTO
04-05-2009, 06:08 AM
Greetings to all,
I happened to be writing a possible solution to listing all the workbooks in a given sub-directory (http://vbaexpress.com/forum/showthread.php?t=26088) which reminded me to ask a question that's been bugging me.
Now in reference to the particular thread/problem, what I had come up with was building an array of filenmames (see below), transposing said, and inserting the transposed array where wanted.
As to being reminded to ask this however, I have done this in a couple of instances recently (trying to Transpose an array), and it seems to me that if the first array built contains too many elements, Transpose fails.
Am I building the array incorrectly, or, is there a limit to Transpose (and if so, what is it)?
Thank you so much,
Mark
Sub GetFilenames()
Dim fso As Object 'FileSystemObject
Dim fol As Object 'Folder
Dim fil As Object 'File
Dim rngDest As Range
Dim aryWorkbookNames()
'// Change the starting cell as desired//
Set rngDest = ActiveSheet.Range("B1")
Set fso = CreateObject("Scripting.FileSystemObject")
Set fol = fso.GetFolder(ThisWorkbook.Path)
ReDim aryWorkbookNames(0)
For Each fil In fol.Files
If fil.Type = "Microsoft Excel Worksheet" Then
aryWorkbookNames(UBound(aryWorkbookNames())) = fil.Name
ReDim Preserve aryWorkbookNames(UBound(aryWorkbookNames()) + 1)
End If
Next
ReDim Preserve aryWorkbookNames(UBound(aryWorkbookNames()) - 1)
rngDest.Resize(UBound(aryWorkbookNames)) = _
Application.WorksheetFunction.Transpose(aryWorkbookNames)
End Sub
I happened to be writing a possible solution to listing all the workbooks in a given sub-directory (http://vbaexpress.com/forum/showthread.php?t=26088) which reminded me to ask a question that's been bugging me.
Now in reference to the particular thread/problem, what I had come up with was building an array of filenmames (see below), transposing said, and inserting the transposed array where wanted.
As to being reminded to ask this however, I have done this in a couple of instances recently (trying to Transpose an array), and it seems to me that if the first array built contains too many elements, Transpose fails.
Am I building the array incorrectly, or, is there a limit to Transpose (and if so, what is it)?
Thank you so much,
Mark
Sub GetFilenames()
Dim fso As Object 'FileSystemObject
Dim fol As Object 'Folder
Dim fil As Object 'File
Dim rngDest As Range
Dim aryWorkbookNames()
'// Change the starting cell as desired//
Set rngDest = ActiveSheet.Range("B1")
Set fso = CreateObject("Scripting.FileSystemObject")
Set fol = fso.GetFolder(ThisWorkbook.Path)
ReDim aryWorkbookNames(0)
For Each fil In fol.Files
If fil.Type = "Microsoft Excel Worksheet" Then
aryWorkbookNames(UBound(aryWorkbookNames())) = fil.Name
ReDim Preserve aryWorkbookNames(UBound(aryWorkbookNames()) + 1)
End If
Next
ReDim Preserve aryWorkbookNames(UBound(aryWorkbookNames()) - 1)
rngDest.Resize(UBound(aryWorkbookNames)) = _
Application.WorksheetFunction.Transpose(aryWorkbookNames)
End Sub