Transpose Question

[GTO]

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

[Kenneth Hobs]

Does this help?
Range("A1").Resize(UBound(a) + 1).value = WorksheetFunction.Transpose(a)

[Bob Phillips]

I haven't seen that, but how many do you have?

[GTO]

Hi Fellas,

Okay, where's the embarrased icon? Given Bob's response, I suspected it must be a Mark problem and not Transpose. Went and found the example I was thinking of and re-tested. My array using Transpose is goobered someplace... (Originally, after finding that if the source data was not excessive, it would return the transposed array, I assumed their was some sort of limit I was running into. Upon retest, I found that I had not originally noticed that the return was garbage)

I'll try and re-write and see if I can figure. Non time-critical, but if I can't figure it in a couple of days, I will certainly post the wb.

Ken, I have not tried your suggestion yet, but will.

Thank you both,

Mark

[GTO]

Sorry Bob - I did not answer your question. The example data was +16k, to return about 3k records.