extract folder name to excel

January 20, 2009 at 20:19:06
Specs: Windows XP, 3.0/2GB

Does anyone know how to extract the name of the subfolder and automatically paste to the excel sheet? eg i have a root folder called roottest and 150 subfolders and like to have the name of the 150 subfolder and display in each of the cell in excel.
many thanks

See More: extract folder name to excel

Report •


#1
January 20, 2009 at 23:20:31

managed to get the folder name...But any idea how to get the FILE NAME
thanks

Report •

#2
January 22, 2009 at 14:18:29

Here is some code that I think will help you.
I found pieces on the net after seeing your question.
I have modified the pieces to work with Excel and
to populate 2 columns of the Active worksheet.

I hope it is helpful.

[code]
-----
' Procedure : ShowFolderList
' Author : orange
' Date : 1/22/2009
' Purpose : To list subfolders and file names. You must enter the StartFolder
'explicitly. For each Folder/File combo, the Folder name is placed in Column 1
'of Worksheet. The file name is placed in column 2.
'
' NOTE: This routine is run from within Excel AND
' MUST have a reference to the Microsoft Scripting Runtime.
'
' Calls: ShowSubFolderList
' CalledBy: N/A
-
'
Sub ShowFolderList()
Dim fso As Object, file As Object, folder As Object, subfolder As Object, s As String
Dim startfolder As String
Dim i As Long
Dim j As Long
Dim MyTempList() As String
Dim lfldrnm As Integer
Dim FldrName As String
Dim FilName As String
Dim sTmp As String
'On Error GoTo ShowFolderList_Error
On Error Resume Next ' to avoib an Invalid procedure call?????

startfolder = "H:\My Downloads"

Set fso = CreateObject("Scripting.FileSystemObject")
Set folder = fso.GetFolder(startfolder)
Debug.Print "startfolder " & startfolder & vbCrLf
Cells(1, 1).Value = "Folder Name"
Cells(1, 2).Value = "File name"
i = 3
For Each file In folder.Files

s = s & folder.Name & "|" & file.Name & "^"

Next file

For Each subfolder In folder.SubFolders

Call ShowSubFolderList(subfolder, s)

Next subfolder
'
'***********************************************************************************
' This routine takes the long string of Folder and file names and marker characters
' and places each folder - file record in an array.
' Each element of the array is parsed to get the folder name and the file name.
' The folder and file name are placed into the same row of the active worksheet.
' The folder name goes in column 1, the filename in column 2.
'*******************************************************************************
'
MyTempList() = Split(s, "^")
For j = 0 To UBound(MyTempList) Step 1
lfldrnm = InStr(MyTempList(j), "|")
FldrName = Mid(MyTempList(j), 1, lfldrnm - 1)
FilName = Mid(MyTempList(j), lfldrnm + 1, Len(MyTempList(j)) - (lfldrnm + 1))
'Debug.Print MyTempList(j)
'Debug.Print FldrName & "***" & FilName
Cells(i, 1).Value = FldrName
Cells(i, 2).Value = FilName
i = i + 1
Next j

MsgBox "Procedure has finished " & Now() & vbCrLf & i - 3 & " Folder/File combos processed"
Set file = Nothing
Set subfolder = Nothing
Set fso = Nothing

On Error GoTo 0
Exit Sub

ShowFolderList_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ShowFolderList of VBA Document ThisWorkbook"

End Sub

---
' Procedure : ShowSubFolderList
' Author : Orange
' Date : 1/22/2009
' Purpose : Subroutine that works with ShowFolderList. This gets the folder
' and file name.
' Calls: ShowSubFolderList (recursive)
' CalledBy: ShowFolderList
-
'
Sub ShowSubFolderList(fld As Object, ByRef str As String)
Dim fil As Object, subfld As Object, arr() As Variant

For Each fil In fld.Files

str = str & fld.Name & "|" & fil.Name & "^"

Next fil

For Each subfld In fld.SubFolders
Call ShowSubFolderList(subfld, str)
Next subfld

Set fil = Nothing
Set subfld = Nothing

End Sub
[/code]


Report •

Related Solutions


Ask Question