help on "scripting.filesystemobject"

[Joe]

Dear All,

I am using Excel 2003 SP2 and VB 6.3

I am trying to use Excel to copy from the server, any new data that
has come.
for that I use "scripting.filesystemobject" to get the list of files.
See the code below.

'****************************************************************

Function FoldersInFolder(ByVal myFolderName As String, ByVal Col As
Integer)

Dim FSO As Object
Dim myBaseFolder As Object
Dim myFolder As Object
Dim R As Integer

Set FSO = CreateObject("scripting.filesystemobject")
Set myBaseFolder = FSO.GetFolder(myFolderName)

R = 2
For Each myFolder In myBaseFolder.SubFolders
rng_Folders(R, Col) = myFolder.Name ' write the folder name on to
the worksheet
R = R + 1
Next myFolder

End Function

'****************************************************************

This works fine except for the speed. When I connect this to the
server folder, it takes exceptionally long time. I assume that it is
because it searches iteratively through all the subfolders and the
files in those..

I have about 100 folders on server, each of which have 50 MB of data.
Seaching through all those 100 x 50 MB is going to be time
consuming......

The question that I have is .... Is it possible to search for just
the list of Folders in that level alone ?? Program need not have to
search for further subfolders and the files... Hope this is
possible...

something like ...
For Each myFolder In myBaseFolder.SubFolders(Level 1)

Thank you very much....

Regards
Joe

 

[Jean-Yves]

Hi Joe,

Would the DIR function not do what you need ?
The example in help shows exactly what you want.
HTH
regards
JY

 

[Joe]

Hi Joe,

Would the DIR function not do what you need ?
The example in help shows exactly what you want.
HTH
regards
JY

















- Show quoted text -

Thanks JY..
I think this is sufficient...

this is my code

'***************************

Private Sub CommandButton1_Click()

Dim file_Name As String
Dim MyArray(300) As String

file_Name = Dir$("D:\Joe\", vbDirectory)

i = 1
Do While (Len(file_Name) > 0)

If file_Name <> "." And file_Name <> ".." And Len(file_Name) <> 0
Then
MyArray(i) = file_Name
i = i + 1
End If

file_Name = Dir$()

Loop

End Sub

'************************

Now I am looking at - How to remove the files? What I want is only
the list of folders...

Thanks again.....
Regards,
Joe.

 

[Joe]

Thanks JY..
I think this is sufficient...

this is my code

'***************************

Private Sub CommandButton1_Click()

Dim file_Name As String
Dim MyArray(300) As String

file_Name = Dir$("D:\Joe\", vbDirectory)

i = 1
Do While (Len(file_Name) > 0)

If file_Name <> "." And file_Name <> ".." And Len(file_Name) <> 0
Then
MyArray(i) = file_Name
i = i + 1
End If

file_Name = Dir$()

Loop

End Sub

'************************

Now I am looking at - How to remove the files? What I want is only
the list of folders...

Thanks again.....
Regards,
Joe.- Hide quoted text -

- Show quoted text -

........an Update.........

I could not find any direct option, but managed to exclude the file
names...

1. I use the following code to retrieve the list of FILES + FOLDERS
file_Name = Dir$(myFolderName & "\", vbDirectory)

2. I use the following code to retrieve the list of FILES alone
file_Name = Dir$(myFolderName & "\")

Now i will get two arrays.. One with Files+Folder and Other with Files
alone..
I will make another list of items that are in (1) but not in (2)...

Regards,
Joe