List of Folders

I need to be able to create a list of folders in a directory in an excel
spreadsheet. The spreadsheet is basically an index for users to use rather
than navigating through Windows Explorer.
I've tried different code off the web, but can't seem to find what I need.
Can anyone help me please?

Check out this website and see if it gets you close enough:

http://www.vba-programmer.com/Snippe...d_Subdirs.html

HTH

Die_Another_Day

Thank you, but I can't get into the website. It may be my browser. I have a
lot of problems getting into most of the Microsoft websites, although I
realise this isn't a microsoft site.

"Die_Another_Day" wrote:

> Check out this website and see if it gets you close enough:
>
> http://www.vba-programmer.com/Snippe...d_Subdirs.html
>
> HTH
>
> Die_Another_Day
>
>

The free Excel add-in "List Files" will list files or folders from specified directories.
Download from... http://www.realezsites.com/bus/primitivesoftware
--
Jim Cone
San Francisco, USA

"marianne" <[email protected]>
wrote in message
I need to be able to create a list of folders in a directory in an excel
spreadsheet. The spreadsheet is basically an index for users to use rather
than navigating through Windows Explorer.
I've tried different code off the web, but can't seem to find what I need.
Can anyone help me please?

"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.
>

thank you for the link. I tried the add-in but it takes too long and the
links don't work.

Marianne

"Jim Cone" wrote:

> The free Excel add-in "List Files" will list files or folders from specified directories.
> Download from... http://www.realezsites.com/bus/primitivesoftware
> --
> Jim Cone
> San Francisco, USA
>
> "marianne" <[email protected]>
> wrote in message
> I need to be able to create a list of folders in a directory in an excel
> spreadsheet. The spreadsheet is basically an index for users to use rather
> than navigating through Windows Explorer.
> I've tried different code off the web, but can't seem to find what I need.
> Can anyone help me please?
>

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)

Try this

Sub Finddir()
Dim FSO As Object
Dim fDir As Object
Dim fSubDir As Object
Dim i As Long
Set FSO = CreateObject("Scripting.FileSystemObject")
Set fDir = FSO.GetFolder("C:\")
For Each fSubDir In fDir.SubFolders
i = i + 1
Cells(i, "A").Value = fSubDir.Name
Next fSubDir
End Sub

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"marianne" <[email protected]> wrote in message
news:[email protected]...
> I need to be able to create a list of folders in a directory in an excel
> spreadsheet. The spreadsheet is basically an index for users to use rather
> than navigating through Windows Explorer.
> I've tried different code off the web, but can't seem to find what I need.
> Can anyone help me please?

Marianne,

The "List Files" program only provides hyperlinks to files not folders.
However, if you select a folder name in the list and click the
orange/tan bar at the top of the list the folder opens.
--
Jim Cone
San Francisco, USA
http://www.officeletter.com/blink/specialsort.html



"marianne"
<[email protected]>
wrote in message
news:[email protected]...
thank you for the link. I tried the add-in but it takes too long and the
links don't work.

Marianne

"Jim Cone" wrote:

> The free Excel add-in "List Files" will list files or folders from specified directories.
> Download from... http://www.realezsites.com/bus/primitivesoftware
> --
> Jim Cone
> San Francisco, USA
>
> "marianne" <[email protected]>
> wrote in message
> I need to be able to create a list of folders in a directory in an excel
> spreadsheet. The spreadsheet is basically an index for users to use rather
> than navigating through Windows Explorer.
> I've tried different code off the web, but can't seem to find what I need.
> Can anyone help me please?
>