List of Folders

"marianne" wrote:

> I need to be able to create a list of folders in a directory in an excel spreadsheet.

I'll assume that you mean from an excel spreadsheet; I'll also assume that
your spreadsheet contains a list of folder names.

Try:

Create a module and add this declaration:

Declare Function MakeSureDirectoryPathExists Lib "imagehlp.dll" (ByVal
lpPath As String) As Long

Then

Sub CreateFolder(ByVal Folder as string)
MakeSureDirectoryPathExists Folder
End Sub


This code is tolerant:

1. If the folder exists, it will leave it untouched and not generate an error
2. If your folder is, say, c:\my folder\my user\tasks\, and say c:\my
folder\my user\ exixts already, it will simply create your folder i.e it will
create from scratch or complete the path.
3. Make sure that the folder ends with \

Write another sub to loop through your list (presumably in a column) and
call CreateFolder.

I'm sorry, I mustn't have explained myself properly. I have a very long list
of folders in 1 particular directory, and they're a long way into the
directories. I would like to be able to list these folders in an excel
spreadsheet. So something with GetFolders(Foldername). This is so that users
will be able to just go to the folder they need without having to navigate
their way through Windows explorer.

Marianne

"AA2e72E" wrote:

> "marianne" wrote:
>
> > I need to be able to create a list of folders in a directory in an excel spreadsheet.
>
> I'll assume that you mean from an excel spreadsheet; I'll also assume that
> your spreadsheet contains a list of folder names.
>
> Try:
>
> Create a module and add this declaration:
>
> Declare Function MakeSureDirectoryPathExists Lib "imagehlp.dll" (ByVal
> lpPath As String) As Long
>
> Then
>
> Sub CreateFolder(ByVal Folder as string)
> MakeSureDirectoryPathExists Folder
> End Sub
>
>
> This code is tolerant:
>
> 1. If the folder exists, it will leave it untouched and not generate an error
> 2. If your folder is, say, c:\my folder\my user\tasks\, and say c:\my
> folder\my user\ exixts already, it will simply create your folder i.e it will
> create from scratch or complete the path.
> 3. Make sure that the folder ends with \
>
> Write another sub to loop through your list (presumably in a column) and
> call CreateFolder.
>

Try:

Function EnumFldrs(ByVal Fldr As String) As String
Set xy =
CreateObject("Scripting.FileSystemObject").GetFolder("c:\OURFILES")
EnumFldrs = Fldr
For Each fle In xy.SubFolders
Select Case TypeName(fle)
Case "Folder"
EnumFldrs = EnumFldrs & "," & fle
End Select
Next
set xy=nothing
End Function

Sub xx()
Debug.Print EnumFldrs("c:\ourfiles")
End Sub

In sub xx, assign

fldlist = EnumFldrs("your folder")

This is a comma separated list of folders.

Split(fldList,",")

will create a 0 based array; use this as necessary: you might have to loop

"marianne" wrote:

> I'm sorry, I mustn't have explained myself properly. I have a very long list
> of folders in 1 particular directory, and they're a long way into the
> directories. I would like to be able to list these folders in an excel
> spreadsheet. So something with GetFolders(Foldername). This is so that users
> will be able to just go to the folder they need without having to navigate
> their way through Windows explorer.
>
> Marianne
>
> "AA2e72E" wrote:
>
> > "marianne" wrote:
> >
> > > I need to be able to create a list of folders in a directory in an excel spreadsheet.
> >
> > I'll assume that you mean from an excel spreadsheet; I'll also assume that
> > your spreadsheet contains a list of folder names.
> >
> > Try:
> >
> > Create a module and add this declaration:
> >
> > Declare Function MakeSureDirectoryPathExists Lib "imagehlp.dll" (ByVal
> > lpPath As String) As Long
> >
> > Then
> >
> > Sub CreateFolder(ByVal Folder as string)
> > MakeSureDirectoryPathExists Folder
> > End Sub
> >
> >
> > This code is tolerant:
> >
> > 1. If the folder exists, it will leave it untouched and not generate an error
> > 2. If your folder is, say, c:\my folder\my user\tasks\, and say c:\my
> > folder\my user\ exixts already, it will simply create your folder i.e it will
> > create from scratch or complete the path.
> > 3. Make sure that the folder ends with \
> >
> > Write another sub to loop through your list (presumably in a column) and
> > call CreateFolder.
> >

Correction:

Change

CreateObject("Scripting.FileSystemObject").GetFolder("c:\OURFILES")

to

CreateObject("Scripting.FileSystemObject").GetFolder(Fldr)