Folder Name Extraction

Microsoft Excel 2003 (full product)
January 5, 2010 at 06:36:44
Specs: Windows XP
I have about 1,000 data discs and I want to make a list of their contents. The main item would be to extract the folder names from each disc using a Macro in Excel. It is possible that may be a file mixed in with these folders (e.g. there are three folders and one file on the root disc). Does anyone have a way to easily extract these names? A side would be to also include any sub-folders and files, but not necessary.

See More: Folder Name Extraction

Report •

January 5, 2010 at 12:43:48

This solution allows you to select files using the Open File dialog, from inside Excel.
All files selected are appended to any data already in Column A of Worksheet 'Sheet1'
The Drive and Path are placed in column A
The filename is placed in Column B.

When you run the Macro again, the next selection of files is added to the existing paths/filenames.

Create a Workbook named Getfilename.xls
On Sheet1 create a Command Button from the Control Toolbox. If this isn't visible, right click on an existing toolbar and check the Control Toolbox.
Select the button Icon and draw a button
Right-click the button and select Command Button - Edit and change the name to something useful !
Right-click the button again and select View Code
In the code window that opens enter this:

Option Explicit

Private Sub CommandButton1_Click()
Dim rngSave As Range
Dim lngCount As Long
Dim strPathFile As String
Dim strFname As String
Dim intLastDiv As Integer
Dim n As Integer

On Error GoTo ErrHnd

With ActiveSheet
    'find the last used cell in column A
    Set rngSave = .Range("A65534").End(xlUp)
    ' Open the file dialog
    With Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = True
        'Copy all path/filenames into column A
        For lngCount = 1 To .SelectedItems.Count
            strPathFile = .SelectedItems(lngCount)
            'find last "/" in path/filename
            intLastDiv = 0
            For n = 1 To Len(strPathFile)
                If Mid(strPathFile, n, 1) = "\" Then
                    intLastDiv = n
                End If
            Next n
            rngSave.Offset(lngCount - 1, 0) = strPathFile
            'split filename from path
            'save path in column A
            rngSave.Offset(lngCount - 1, 0).Value = Left(strPathFile, intLastDiv)
            'save filename in column B
            rngSave.Offset(lngCount - 1, 1).Value = Right(strPathFile, Len(strPathFile) - intLastDiv)
        Next lngCount
    End With
    'set column widths to fit
    .Range("A1:B" & Format(rngSave.Row + lngCount, "##0")).Columns.AutoFit
End With
Exit Sub

'error handler
End Sub

Note that Private Sub CommandButton1_Click() and End Sub will already be there, so don't duplicate them
Option Explicit goes before Private Sub CommandButton1_Click()

Use Alt +f11 to return to the Excel Window. From the Control Toolbox toolbar select the Exit Design Mode icon, and close the toolbar.

The Command button will now run the macro.

This routine worked on hard drive partitions, and a CD drive and also worked on a USB connected floppy as well as on a network drive.


Report •

January 6, 2010 at 04:34:52
This worked very well, thank you. Not to be a pain in the ass, as you set it up to be fool proof and in this case it is much appreciated, but is there a step which can be added where everything on the drive selected is automatically transferred? In this case, I will need to select the files I would like to list by opening each folder on the disc. Ideally, it would be great to be able to hit said button and everything on the disc is listed in Excel. As I said there are 1,000+ discs with varying amounts of folders and sub-folders on them with each containing varying amounts of files. Your code is functional and way more than I was able to muster, but it still results in one helluva an arduous task to sift through all of these data discs. Again, I don't mean to be a pain, but I thought that asking an obvious expert couldn't hurt. THANK YOU!


I also note that it is pasting the new files over the last one from the previous folder (i.e. the last files listed is replaced by the first file from the next folder).

Report •

January 6, 2010 at 06:28:27

Thanks for your comments.

Unfortunately I don't know of a way to traverse the directory tree automatically from Excel. I did look for an option that allowed for a group of folders to be selected, using the Windows File Open API from inside Excel, but the find folder option does not respond to the Multi-select flag. I also tried the Windows Browse Folder API but this has no multi-select option either.

Another option is to see if what you want is available using Windows scripting language, and get the results into a text file. Then we can get the text file into Excel.

I have very little experience with this, but I have put together a short script file that creates a comma delimited text file containing the names of all subdirectories in a folder (it only does one level - so it is rather limited)

It is not exactly what you want, but it may be a starting point. You will see that the folder name and the output filename are hardcoded near the start of the code.

Copy this to a text file (use Notepad) and save it with a vbs file extension. It should run when double clicked or using 'Open with' and select 'Microsoft Windows based script host'.

Dim fso1, fso2
Dim flds, subfld, fld
dim file
Dim foldername, outputfilename
'use your first folder name here:
foldername="D:\Current Data\"
'create file system object for folder names
Set fso1 = CreateObject("Scripting.FileSystemObject")
Set flds = fso1.Getfolder(foldername)
Set subfld = flds.SubFolders
'create file system object for text file
Set fso2 = CreateObject("Scripting.FileSystemObject")
Set file = fso2.OpenTextFile(outputfilename, 2, True)

'get each folder name and save in text file comma delimited
For Each fld in subfld
   file.Write & chr(44)
file.WriteLine ""
set fso1=Nothing
set fso2=nothing

It should be possible to make this go through subfolders automatically.

There are probably other programs already written to do this, so a bit of Googling should come up with something better.

Once you have a text file, it can be imported into Excel.


Report •

Related Solutions

January 6, 2010 at 07:03:57
I really do appreciate your efforts on this. I tried the txt output option and it returned an empty txt file. The only change needed was to substitute the first folder path and name, correct? Also, does this line from the original code (in Excel) need to be adjusted to not have it write over the last file from the previous folder?

Set rngSave = .Range("A65534").End(xlUp)

I did google this type of program and there is one called whereisit (or something like that). I may look into this if I get burned out using your code. Thank you once again. Very kind of you.

Report •

January 6, 2010 at 07:49:38

Regarding the line
Set rngSave = .Range("A65534").End(xlUp)
This starts at the bottom of column A and goes back up the column to find the last row with data.

As a result each time it is run, it will start at a new row below existing data.

I just looked in the Programing forum, and did a search and here is a short program by 'Crossroad' . You can find it here - Response #3.

It worked well listing each directory and subdirectory with a list of files in each. Ugh! Why didn't I look here first !!!

On a large hard drive with over 2000 folders & 33,000 files it stopped short of the last 26 folders and 100 or so files. I am not sure what stopped it, but this is probably not going to be a problem for you with data separated onto many discs.

Once you have run this it should be fairly easy to get it into Excel and extract what you want.

As the program uses the same output filename you could use the Input box method to change the output filename each time.

Also if your disks are always going to be in the same drive e.g., E:\
then you could hard code this in the program rather than inputting it each time..

If you end up with 1000 files, we should be able to automate their upload to Excel. (give them sequential numbers starting with 0001)


Report •

January 6, 2010 at 08:09:08
Nanook, going along with Humar's idea of getting the info first into a txt file,
you might ask your question in the Programming forum.
A batch file should be able to get your desired info into a txt file.


Report •

January 6, 2010 at 10:08:55
I tried on the other .vbs forms listed in your link and it worked very nicely. The output in you command prompt is in a more search friendly form for what I am trying to accomplish, so I am using your script and saving the other for a future project. With regards to the data beginning on the next available cell, that is not what I am experiencing. When I pull the contents from the next folder, the first file overwrites the last cell with data in it instead of moving to the next cell. Again, thank you so much for your time and effort. I do feel like a mooching slacker.


Report •

January 6, 2010 at 10:36:00

Glad you are getting results.

You are right about the over writing - my mistake.
The Set rngSave = .Range("A65534").End(xlUp)
is starting at the last cell used, not the first empty cell.

Try this:
Set rngSave = .Range("A65534").End(xlUp).Offset(1, 0)

The Offset moves it down 1 row


Report •

Ask Question