Hi

I am trying to create a Macro that returns to a spreadsheet a list of names
of sub-directories within a particular directory.

For example, I am trying to return a list of company names within my sales
team directory

eg.

R:\Team 318\ **** my sales team:)
\ACME
\TOYOTA
\GM

would return....

ACME
TOYOTA
GM

Any hints?

Regards

Andy

Try:

Function ListSubFldrs(ByVal R As String) As Variant
Set FSO = CreateObject("Scripting.FileSystemObject")
ListSubFldrs = ""
For Each fldr In FSO.GetFolder(R).SubFolders
ListSubFldrs = ListSubFldrs & "," & fldr.Name
Next
ListSubFldrs = Split(Mid(ListSubFldrs, 2), ",")
set FSO = Nothing
End Function

Sub xy()
xx = ListSubFldrs("c:\ajay")
End Sub

The variable xx is an array in option base 0 containg all the sub folders
within C:\ajay

"Andy" wrote:

Hi

I am trying to create a Macro that returns to a spreadsheet a list of names
of sub-directories within a particular directory.

For example, I am trying to return a list of company names within my sales
team directory

eg.

R:\Team 318\ **** my sales team:)
\ACME
\TOYOTA
\GM

would return....

ACME
TOYOTA
GM

Any hints?

Regards

Andy

maybe a stupid question.... but do i put the function script in the same
module as the one below?

what do you mean array? could you give me an example?

thanks AA2e72E!

rgds andy

"AA2e72E" wrote:

Try:

Function ListSubFldrs(ByVal R As String) As Variant
Set FSO = CreateObject("Scripting.FileSystemObject")
ListSubFldrs = ""
For Each fldr In FSO.GetFolder(R).SubFolders
ListSubFldrs = ListSubFldrs & "," & fldr.Name
Next
ListSubFldrs = Split(Mid(ListSubFldrs, 2), ",")
set FSO = Nothing
End Function

Sub xy()
xx = ListSubFldrs("c:\ajay")
End Sub

The variable xx is an array in option base 0 containg all the sub folders
within C:\ajay

"Andy" wrote:

Hi

I am trying to create a Macro that returns to a spreadsheet a list of names
of sub-directories within a particular directory.

For example, I am trying to return a list of company names within my sales
team directory

eg.

R:\Team 318\ **** my sales team:)
\ACME
\TOYOTA
\GM

would return....

ACME
TOYOTA
GM

Any hints?

Regards

Andy

An Array is an array, also called a matrix: they have dimensions. Their
values can be interrogated/assigned element by element.

Dim aa(10) ' creates a variant array of 10 elements

ab=Array("One","Two",Three") ' variant array of 3 elements

ac=Split("Monday,Tuesday,Wed,Thu") ' variant array of 4 elements

debug.print ac(0) ' will print Monday in the Immediate Window
debug.print ac(1) ' will print Tuesday in the Immediate Window
....
debug.print ac(4) ' will error because the indexing starts at 0 and the
highest index is 3

Do you get the idea?



"Andy" wrote:

maybe a stupid question.... but do i put the function script in the same
module as the one below?

what do you mean array? could you give me an example?

thanks AA2e72E!

rgds andy

"AA2e72E" wrote:

Try:

Function ListSubFldrs(ByVal R As String) As Variant
Set FSO = CreateObject("Scripting.FileSystemObject")
ListSubFldrs = ""
For Each fldr In FSO.GetFolder(R).SubFolders
ListSubFldrs = ListSubFldrs & "," & fldr.Name
Next
ListSubFldrs = Split(Mid(ListSubFldrs, 2), ",")
set FSO = Nothing
End Function

Sub xy()
xx = ListSubFldrs("c:\ajay")
End Sub

The variable xx is an array in option base 0 containg all the sub folders
within C:\ajay

"Andy" wrote:

Hi

I am trying to create a Macro that returns to a spreadsheet a list of names
of sub-directories within a particular directory.

For example, I am trying to return a list of company names within my sales
team directory

eg.

R:\Team 318\ **** my sales team:)
\ACME
\TOYOTA
\GM

would return....

ACME
TOYOTA
GM

Any hints?

Regards

Andy

Thanks....clear as mud...now to bridge the gap...

How do I display this Array in a spreadsheet as a list starting at A1?

Regards, Andy

"AA2e72E" wrote:

An Array is an array, also called a matrix: they have dimensions. Their
values can be interrogated/assigned element by element.

Dim aa(10) ' creates a variant array of 10 elements

ab=Array("One","Two",Three") ' variant array of 3 elements

ac=Split("Monday,Tuesday,Wed,Thu") ' variant array of 4 elements

debug.print ac(0) ' will print Monday in the Immediate Window
debug.print ac(1) ' will print Tuesday in the Immediate Window
...
debug.print ac(4) ' will error because the indexing starts at 0 and the
highest index is 3

Do you get the idea?



"Andy" wrote:

maybe a stupid question.... but do i put the function script in the same
module as the one below?

what do you mean array? could you give me an example?

thanks AA2e72E!

rgds andy

"AA2e72E" wrote:

Try:

Function ListSubFldrs(ByVal R As String) As Variant
Set FSO = CreateObject("Scripting.FileSystemObject")
ListSubFldrs = ""
For Each fldr In FSO.GetFolder(R).SubFolders
ListSubFldrs = ListSubFldrs & "," & fldr.Name
Next
ListSubFldrs = Split(Mid(ListSubFldrs, 2), ",")
set FSO = Nothing
End Function

Sub xy()
xx = ListSubFldrs("c:\ajay")
End Sub

The variable xx is an array in option base 0 containg all the sub folders
within C:\ajay

"Andy" wrote:

Hi

I am trying to create a Macro that returns to a spreadsheet a list of names
of sub-directories within a particular directory.

For example, I am trying to return a list of company names within my sales
team directory

eg.

R:\Team 318\ **** my sales team:)
\ACME
\TOYOTA
\GM

would return....

ACME
TOYOTA
GM

Any hints?

Regards

Andy