List ALL Folders within a folder

Akashwani

Akashwani

Well-known Member
Joined
Mar 14, 2009
Messages
2,911
<TABLE style="WIDTH: 849pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=1132><COLGROUP><COL style="WIDTH: 849pt; mso-width-source: userset; mso-width-alt: 41398" width=1132><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; WIDTH: 849pt; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl65 height=17 width=1132>Hi,</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl65 height=17></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl65 height=17>First off I would like to say that I do not understand VBA.</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl65 height=17>I'm not 100% sure how to explain this, but I will do my best.</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl65 height=17></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl65 height=17>Here's the scenario..</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl65 height=17>I would like to list ALL the Folder names within a particular Folder (MyExcelStuff),this folder gets added to daily.....</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl65 height=17></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl65 height=17>Once the list has been created I want to use it in a Data Validation list.</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl65 height=17></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl65 height=17>I would like to select a Folder from this list and have the Workbook within it opened.</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl65 height=17></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl65 height=17>I will then need to know the Workbook name so that I can use part of it within a formula.</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl65 height=17></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl65 height=17>Here is an example of the file paths that I have if it helps...</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl65 height=17></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl65 height=17>='C:\Documents and Settings\Ak\My Documents\MyExcelStuff\ExcelPart???\[TestPart?????.xls]Sheet1'!</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl65 height=17>='C:\Documents and Settings\Ak\My Documents\MyExcelStuff\ExcelPart????\[TestPart???????.xls]Sheet1'!</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl65 height=17>='C:\Documents and Settings\Ak\My Documents\MyExcelStuff\ExcelPart?????\[TestPart?????.xls]Sheet1'!</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl65 height=17></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl65 height=17>All the data EXCEPT for the ???? is constant.</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl65 height=17></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl65 height=17>So, when I open my workbook, I need a macro to run and to find ALL the Folders within Folder MyExcelStuff,
it then needs to update my workbook on sheet DataValidation (column F2 down) with ALL the file names.


</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl65 height=17>On sheet LookUp cell C1, I will have my Data Validation list that will need to be updated (this I think I can do using Offset)....
=OFFSET(DataValidation!$F$2,0,0,COUNTA(DataValidation!$F:$F),1)
</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl65 height=17>When I select a Workbook from this list the file needs to be opened (I guess by using a code in Worksheet_Change).</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl65 height=17>In cell D1 I need a formula to extract the ???? part of the workbook that has been opened (this I think I can work out).</TD></TR></TBODY></TABLE>

Any advice and help is greatly appreciated.

Ak

Come on England!
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
So, when I open my workbook, I need a macro to run and to find ALL the Folders within Folder MyExcelStuff,
it then needs to update my workbook on sheet DataValidation (column F2 down) with ALL the file names....

On sheet LookUp cell C1, I will have my Data Validation list that will need to be updated ...

When I select a Workbook from this list, the file needs to be opened
Click to expand...

This code should get you a list of ExcelPart??? folder names. That is as far as I understand exactly enough to code for, for what you need.


Rich (BB code): 
Private Sub WorkBook_Open()
Dim DirName As String
Dim arrNames As Variant
Dim j As Long
   j = 0
Dim FName As String
Dim Path As String
Dim NextCel As Range
Set NextCel = Sheets("DataValidation").Range("F2")

'Clear the contents of Column F, Row 2 and below
Sheets("DataValidation").Range("F2:F65536").ClearContents

Path = "'C:\Documents and Settings\Ak\My Documents\MyExcelStuff\'"

DirName = Dir(Path, vbDirectory) 'Get the first entry.

Do While DirName <> "" ' Start the loop.
  'Ignore the current directory and the encompassing directory.
  If DirName <> "." And DirName <> ".." Then
  'Use bitwise comparison to make sure DirName is a directory.
     If GetAttr(Path & DirName) = vbDirectory Then
        'Add DirName to arrFolders
        ReDim Preserve arrNames(j)
        arrNames(j) = DirName
        j = j + 1
     End If
  End If
  DirName = Dir 'get next file
Loop
'Loop Dir thru arrNames to get File Names
'Or Paste arrNames in DataValidation!F2
'or paste Names in LookUp!C1
'Or whatever you really want
End Sub
 
Upvote 0
Hi Sam,

Thanks so much for your reply and code.
When I open my workbook, I get Bad file name or number (ERROR 52) on this line...

DirName = Dir(Path, vbDirectory) 'Get the first entry.

I have had a look at Excel hep, but this doesn't mean a thing to me!!!

Bad file name or number (Error 52)

An error occurred trying to access the specified file. This error has the following causes and solutions:

  • A statement refers to a file with a file number or file name that is:
    • Not specified in the Open statement or was specified in an Open statement, but has since been closed. Specify the file name in an Open statement. Note that if you invoked the Close statement without arguments, you may have inadvertently closed all currently open files, invalidating all file numbers.
    • Out of the range of file numbers (1 – 511). If your code is generating file numbers algorithmically, make sure the numbers are valid.
  • There is an invalid name or number. File names must conform to operating system conventions as well as Basic file-naming conventions. In Microsoft Windows, use the following conventions for naming files and directories:
    • The name of a file or directory can have two parts: a name and an optional extension. The two parts are separated by a period, for example, myfile.new.
    • The name can contain up to 255 characters.
    • The name must start with either a letter or number. It can contain any uppercase or lowercase characters (file names aren't case-sensitive) except the following characters: quotation mark ("), apostrophe ('), slash (/), backslash (), colon (:), and vertical bar (|).
    • The name can contain spaces.
    • The following names are reserved and can't be used for files or directories: CON, AUX, COM1, COM2, COM3, COM4, LPT1, LPT2, LPT3, PRN, and NUL. For example, if you try to name a file PRN in an Open statement, the default printer will simply become the destination for Print # and Write # statements directed to the file number specified in the Open statement.
    • The following are examples of valid Microsoft Windows file names:
    <TABLE><TBODY><TR><TD>

    <CODE>LETTER.DOCMy Memo.TxtBUDGET.9212345678.901Second Try.Rpt</CODE>
    </PRE></TD></TR></TBODY></TABLE>
Click to expand...

The current file paths are...

C:\Documents and Settings\Ak\My Documents\MyExcelStuff\ExcelPart1
C:\Documents and Settings\Ak\My Documents\MyExcelStuff\ExcelPart2
C:\Documents and Settings\Ak\My Documents\MyExcelStuff\ExcelPart3

Then the workbook within will be...

TestPart1.xls
TestPart2.xls
TestPart3.xls

I hope that I have provided suitable information for you to resolve this.

Thanks Sam.

Ak

Come on England!
 
Upvote 0
Try removing the last \ from the path name in the code. The one after "MyExcelStuff."

You might have to remove the single quotes (') from around the path, also.
 
Upvote 0
Hi Sam,

I now get an error on this line....

If GetAttr(Path & DirName) = vbDirectory Then

File not found (Error 53)

The file was not found where specified. This error has the following causes and solutions:

  • A statement, for example, Kill, Name, or Open, refers to a file that doesn't exist. Check the spelling of the file name and the path specification.
  • An attempt has been made to call a procedure in a dynamic-link library (DLL) or Macintosh code resource, but the library or resource file name specified in the Lib clause of the Declare statement can't be found. Check the spelling of the file name and the path specification.
  • In the development environment, this error occurs if you attempt to open a project or load a text file that doesn't exist. Check the spelling of the project name or file name and the path specification.
For additional information, select the item in question and press F1 (in Windows) or HELP (on the Macintosh).
Click to expand...

Ak

Come on England!
 
Upvote 0
Hi Sam,

I have managed to resolve my problem by using these codes in a standard Module...

Code: 
Option Explicit
Sub TestListFilesInFolder()
Sheets("DataValidation").Select
Sheets("DataValidation").Range("F1:F65536").ClearContents
    'Workbooks.Add ' create a new workbook for the file list
    ' add headers
    With Range("G1")
        .Formula = "Folder List:"
        .Font.Size = 12
    End With
    ListFilesInFolder "C:\Documents and Settings\Ak\My Documents\MyExcelStuff", True
End Sub
 
 
Sub ListFilesInFolder(SourceFolderName As String, IncludeSubfolders As Boolean)
' lists information about the files in SourceFolder
Dim FSO As Scripting.FileSystemObject
Dim SourceFolder As Scripting.Folder, SubFolder As Scripting.Folder
Dim FileItem As Scripting.File
Dim r As Long
    Set FSO = New Scripting.FileSystemObject
    Set SourceFolder = FSO.GetFolder(SourceFolderName)
    r = Range("F65536").End(xlUp).Row + 1
    For Each FileItem In SourceFolder.Files
        ' display file properties
        Cells(r, 6).Formula = FileItem.Path
    r = r + 1 ' next row number
    Next FileItem
    If IncludeSubfolders Then
        For Each SubFolder In SourceFolder.SubFolders
            ListFilesInFolder SubFolder.Path, True
        Next SubFolder
    End If
    Columns("F:F").AutoFit
    Set FileItem = Nothing
    Set SourceFolder = Nothing
    Set FSO = Nothing
    ActiveWorkbook.Saved = True
End Sub

I found the above (edited) method here....
http://www.exceltip.com/st/List_fil...Runtime_using_VBA_in_Microsoft_Excel/446.html

I then have this in ThisWorkbook....
Code: 
Option Explicit
Private Sub WorkBook_Open()
TestListFilesInFolder
End Sub

I just need Vba to Open the file selected now!!

Thanks for your time and contribution Sam.

Ak



Come on England! (you muppets)
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question
Select the time from the list and search with Selenium VBA
Replies
6
Views
291
John_w
J
A
  • Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
1K
Attrazion
A
G
  • Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
864
gvsumich
G
D
  • Question
Problems Encountered When Generating VBA Excel Charts with 15-Minute Interval Data.
Replies
0
Views
177
dilshod_k
D
E
  • Question
VBA for Web Scraping help
Replies
0
Views
622
epactheactor
E

Forum statistics

Threads
1,216,070
Messages
6,128,618
Members
449,460
Latest member
jgharbawi

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top