Dear Helpers,

First of all I would like to thank you so very much for some help tha
I got from this site...

I was searching for keywords "extract file names from folders" and
got many results but went with 'this page' (http://tinyurl.com/4gk7n)
And the modified code post from VIKRAM...just did what I wanted t
do...here'z the CODE:

============================
Sub IndexFiles()
With Application.FileSearch
.LookIn = "C:\MyMusic"
.FileType = msoFileTypeAllFiles
.SearchSubFolders = True
.Execute
End With
cnt = Application.FileSearch.FoundFiles.Count
For i = 1 To cnt
Rng = "A" & i
Range(Rng).Value = Application.FileSearch.FoundFiles.Item(i)
Next i
End Sub
=============================

Now...here'z a little additional stuff that I would like Excel t
perform...

After making this macro as guided by Vikram in the above hyperlink,
am able to get the path to all files...like this:

I:\New Songs\DAP Songs\(AA_AB_LAUT_CHALEN)-MERA_DIL_TERA_DEEWANA.mp3
I:\New Songs\DAP Songs\(DIL_SE)-DILSERE_DILSERE.mp3
I:\New Songs\BEST OF STERIO NATION\01 - ISHQ HOGAYA.mp3
I:\New Songs\NAYEE PADOSAN\01 - SARI SARI RAINA.mp3
I:\New Songs\Aashiqui - Abhijeet\01 - Track 1.rmj

Now what exactly I wanna do is to get the names of the Folders (ONLY
as highlighted in red colour in the above lines) instead o
files...like I want instead of above five lines...

DAP Songs
Best of STERIO NATION
NAYEE PADOSAN
Aashiqui - Abhijeet

NB: As DAP Songs has two files in it, so it appeared twice in the abov
list...but since it is only one directory, I want it in my list onc
only...

Hope to receive help ASAP :) THANK YOU GUYz once again in advance fo
any help.

Kind regards.

GC

--
GC
-----------------------------------------------------------------------
GC.'s Profile: http://www.excelforum.com/member.php...fo&userid=1665
View this thread: http://www.excelforum.com/showthread.php?threadid=31863

Will anybody respond here please...I'll be extremely grateful for any
help.

Kind regards.

GC.


--
GC.
------------------------------------------------------------------------
GC.'s Profile: http://www.excelforum.com/member.php...o&userid=16652
View this thread: http://www.excelforum.com/showthread...hreadid=318633

bit dirty but works.

Sub IndexFiles()
With Application.FileSearch
.LookIn = "C:\MyMusic"
.FileType = msoFileTypeAllFiles
.SearchSubFolders = True
.Execute
End With
cnt = Application.FileSearch.FoundFiles.Count
m = 1
For i = 1 To cnt

Rng = "A" & m


myFile = Application.FileSearch.FoundFiles.Item(i)
myFileLen = Len(myFile)
myFileRev = StrReverse(myFile)
x = Split(myFileRev, "\")
xLen = Len(x(0))
myDir = Left(myFile, (myFileLen - (xLen + 1)))
If myDir < k Then
Range(Rng).Value = myDir
m = m + 1
End If
k = myDir


Next i
End Su

--
mangesh_yada

-----------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...fo&userid=1047
View this thread: http://www.excelforum.com/showthread.php?threadid=31863

Dear Mangesh,

I'm highly indebted for your kind reply...

The results that I've achieved after running your script is almos
something that I desire...here I'm doing the copying pasting of th
results...

E:\WinXP\I386
E:\WinXP\WindowsXP Utilities\techfactXP
E:\WinXP\WindowsXP Utilities\comboXP
E:\WinXP\WindowsXP Utilities\techfactXP
E:\WinXP\WindowsXP Utilities\comboXP
E:\WinXP\WindowsXP Utilities\techfactXP
E:\WinXP\WindowsXP Utilities\comboXP
E:\WinXP\WindowsXP Utilities\techfactXP
E:\WinXP\I386
E:\WinXP\I386\COMPDATA

Now what I'm encountering is the duplicate entries and the entire pat
to sub-folder...

In short, what I want is to get only the name of a single folder onc
(only) and the name of the folder (only) instead the entire path...

I hope that I clarified my point here...and am waiting for som
positive response.

Kind regards.

GC

--
GC
-----------------------------------------------------------------------
GC.'s Profile: http://www.excelforum.com/member.php...fo&userid=1665
View this thread: http://www.excelforum.com/showthread.php?threadid=31863

Hi GC

there's probably a better way, as this will only work on files that are in a
subdirectory directly under I:\New Songs\
but you could give it a try
-----
Sub IndexFiles()
With Application.FileSearch
..NewSearch
..LookIn = "I:\New Songs"
..SearchSubFolders = True
..Filename = "*.xls"
If .Execute() 0 Then
For i = 1 To .FoundFiles.Count
fname2 = Application.FileSearch.FoundFiles.Item(i)
fname = Mid(fname2, InStr(4, fname2, "\") + 1, InStr(9, fname2, "\") -
InStr(4, fname2, "\") - 1)
Rng = "A" & i
Range(Rng).Value = fname
Next
End If
End With
End Sub
---
hope this helps
Cheers
julieD

"GC." wrote in message
...

Dear Helpers,

First of all I would like to thank you so very much for some help that
I got from this site...

I was searching for keywords "extract file names from folders" and I
got many results but went with 'this page' (http://tinyurl.com/4gk7n).
And the modified code post from VIKRAM...just did what I wanted to
do...here'z the CODE:

============================
Sub IndexFiles()
With Application.FileSearch
LookIn = "C:\MyMusic"
FileType = msoFileTypeAllFiles
SearchSubFolders = True
Execute
End With
cnt = Application.FileSearch.FoundFiles.Count
For i = 1 To cnt
Rng = "A" & i
Range(Rng).Value = Application.FileSearch.FoundFiles.Item(i)
Next i
End Sub
=============================

Now...here'z a little additional stuff that I would like Excel to
perform...

After making this macro as guided by Vikram in the above hyperlink, I
am able to get the path to all files...like this:

I:\New Songs\DAP Songs\(AA_AB_LAUT_CHALEN)-MERA_DIL_TERA_DEEWANA.mp3
I:\New Songs\DAP Songs\(DIL_SE)-DILSERE_DILSERE.mp3
I:\New Songs\BEST OF STERIO NATION\01 - ISHQ HOGAYA.mp3
I:\New Songs\NAYEE PADOSAN\01 - SARI SARI RAINA.mp3
I:\New Songs\Aashiqui - Abhijeet\01 - Track 1.rmj

Now what exactly I wanna do is to get the names of the Folders (ONLY,
as highlighted in red colour in the above lines) instead of
files...like I want instead of above five lines...

DAP Songs
Best of STERIO NATION
NAYEE PADOSAN
Aashiqui - Abhijeet

NB: As DAP Songs has two files in it, so it appeared twice in the above
list...but since it is only one directory, I want it in my list once
only...

Hope to receive help ASAP :) THANK YOU GUYz once again in advance for
any help.

Kind regards.

GC.


--
GC.
------------------------------------------------------------------------
GC.'s Profile:
http://www.excelforum.com/member.php...o&userid=16652
View this thread: http://www.excelforum.com/showthread...hreadid=318633

I am grateful for your promptness JulieD but what I exactly want is th
name of the sub-folders which are in I:\New Songs\

I've re-edited my reply above and have coloured what I exactly want an
am quoting the same as under:

Now what exactly I wanna do is to get the names of the SUB FOLDER
(ONLY, as highlighted in red colour in the above lines) instead o
files...like I want instead of above five lines...

DAP Songs
Best of STERIO NATION
NAYEE PADOSAN
Aashiqui - Abhijeet

NB: As DAP Songs has two files in it, so it appeared twice in the abov
list...but since it is only one directory, I want it in my list onc
only...

Hope you got my point this time...

Waiting for another reply :(

Kind regards.

GC

--
GC
-----------------------------------------------------------------------
GC.'s Profile: http://www.excelforum.com/member.php...fo&userid=1665
View this thread: http://www.excelforum.com/showthread.php?threadid=31863

Hi GC

sorry, bad wording on my part - it will give you the names of the subfolders
under new songs, but not sub-sub folder names

Cheers
JulieD


"GC." wrote in message
...

I am grateful for your promptness JulieD but what I exactly want is the
name of the sub-folders which are in I:\New Songs\

I've re-edited my reply above and have coloured what I exactly want and
am quoting the same as under:

Now what exactly I wanna do is to get the names of the SUB FOLDERS
(ONLY, as highlighted in red colour in the above lines) instead of
files...like I want instead of above five lines...

DAP Songs
Best of STERIO NATION
NAYEE PADOSAN
Aashiqui - Abhijeet

NB: As DAP Songs has two files in it, so it appeared twice in the above
list...but since it is only one directory, I want it in my list once
only...

Hope you got my point this time...

Waiting for another reply :(

Kind regards.

GC.


--
GC.
------------------------------------------------------------------------
GC.'s Profile:
http://www.excelforum.com/member.php...o&userid=16652
View this thread: http://www.excelforum.com/showthread...hreadid=318633

Nah...JulieD...perhaps it was my mistake for not properly clarifying m
view point, however, kindly checkout this link to the image...the erro
which I'm getting on running your script...it is not even getting th
name of sub-folders (indeed I only need the name of the sub-folder
only, not the sub-sub folders)...

[image: http://imagesavers.com/041119/1100845851.JPG]

Hoping for the required help.

Kind regards.

GC.

JulieD Wrote:
Hi GC

sorry, bad wording on my part - it will give you the names of th
subfolders
under new songs, but not sub-sub folder names

Cheers
Julie

--
GC
-----------------------------------------------------------------------
GC.'s Profile: http://www.excelforum.com/member.php...fo&userid=1665
View this thread: http://www.excelforum.com/showthread.php?threadid=31863

Hi

the problems appear with copy & paste from the newsgroup post ...
1) remove the extra . where you see ..
2) change the LOOK IN to "I:\New Songs" (if that's the folder you want to
work on)
3) and ensure that the
fname =
line is all one line (ie go to the start of the next line & press the
backspace key a line return has been added)

let me know how you go

Cheers
JulieD


"GC." wrote in message
...

Nah...JulieD...perhaps it was my mistake for not properly clarifying my
view point, however, kindly checkout this link to the image...the error
which I'm getting on running your script...it is not even getting the
name of sub-folders (indeed I only need the name of the sub-folders
only, not the sub-sub folders)...

[image: http://imagesavers.com/041119/1100845851.JPG]

Hoping for the required help.

Kind regards.

GC.

JulieD Wrote:
Hi GC

sorry, bad wording on my part - it will give you the names of the
subfolders
under new songs, but not sub-sub folder names

Cheers
JulieD


--
GC.
------------------------------------------------------------------------
GC.'s Profile:
http://www.excelforum.com/member.php...o&userid=16652
View this thread: http://www.excelforum.com/showthread...hreadid=318633

This will give you only the directory

Sub IndexFiles()

With Application.FileSearch
.LookIn = "C:\MyMusic"
.FileType = msoFileTypeAllFiles
.SearchSubFolders = True
.Execute
End With
cnt = Application.FileSearch.FoundFiles.Count
m = 1
For i = 1 To cnt

Rng = "A" & m


myFile = Application.FileSearch.FoundFiles.Item(i)
x = Split(myFile, "\")
Debug.Print UBound(x)
xLen = Len(x(0))
myDir = x(UBound(x) - 1)
If myDir < k Then
Range(Rng).Value = myDir
m = m + 1
End If
k = myDir

Next i

End Su

--
mangesh_yada

-----------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...fo&userid=1047
View this thread: http://www.excelforum.com/showthread.php?threadid=31863

As for the error you mentioned:
The first 4 red lines are showing error as there are 2 preceeding dots
Remove 1 dot from each line.
In the second part of the error i.e. the 2 red lines (line 10 and 11)
these 2 lines should be continuous, so push back line 11 till i
continues with line 1

--
mangesh_yada

-----------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...fo&userid=1047
View this thread: http://www.excelforum.com/showthread.php?threadid=31863

did you try my second post... reposting the sub here again:

Sub IndexFiles()

With Application.FileSearch
.LookIn = "C:\MyMusic"
.FileType = msoFileTypeAllFiles
.SearchSubFolders = True
.Execute
End With
cnt = Application.FileSearch.FoundFiles.Count
m = 1
For i = 1 To cnt

Rng = "A" & m


myFile = Application.FileSearch.FoundFiles.Item(i)
x = Split(myFile, "\")
Debug.Print UBound(x)
xLen = Len(x(0))
myDir = x(UBound(x) - 1)
If myDir < k Then
Range(Rng).Value = myDir
m = m + 1
End If
k = myDir

Next i

End Su

--
mangesh_yada

-----------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...fo&userid=1047
View this thread: http://www.excelforum.com/showthread.php?threadid=31863

my second post should give you the following result

I386
techfactXP
comboXP
COMPDATA

based on your data below


E:\WinXP\I386
E:\WinXP\WindowsXP Utilities\techfactXP
E:\WinXP\WindowsXP Utilities\comboXP
E:\WinXP\WindowsXP Utilities\techfactXP
E:\WinXP\WindowsXP Utilities\comboXP
E:\WinXP\WindowsXP Utilities\techfactXP
E:\WinXP\WindowsXP Utilities\comboXP
E:\WinXP\WindowsXP Utilities\techfactXP
E:\WinXP\I386
E:\WinXP\I386\COMPDAT

--
mangesh_yada

-----------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...fo&userid=1047
View this thread: http://www.excelforum.com/showthread.php?threadid=31863

Yoohoooo Mangesh! The post # 11's script is almost exactly what
want...here are the results:

I386
techfactXP
comboXP
techfactXP
comboXP
techfactXP
comboXP
techfactXP
I386
COMPDATA
I386
EX40

Now the last thing is to omit the duplicate entries...is tha
possible?

Kind regards.

GC

--
GC
-----------------------------------------------------------------------
GC.'s Profile: http://www.excelforum.com/member.php...fo&userid=1665
View this thread: http://www.excelforum.com/showthread.php?threadid=31863

Take a look at http://tinyurl.com/5u5ff and use file.name for the exact file
name

--

HTH

RP
(remove nothere from the email address if mailing direct)


"GC." wrote in message
...

Dear Helpers,

First of all I would like to thank you so very much for some help that
I got from this site...

I was searching for keywords "extract file names from folders" and I
got many results but went with 'this page' (http://tinyurl.com/4gk7n).
And the modified code post from VIKRAM...just did what I wanted to
do...here'z the CODE:

============================
Sub IndexFiles()
With Application.FileSearch
LookIn = "C:\MyMusic"
FileType = msoFileTypeAllFiles
SearchSubFolders = True
Execute
End With
cnt = Application.FileSearch.FoundFiles.Count
For i = 1 To cnt
Rng = "A" & i
Range(Rng).Value = Application.FileSearch.FoundFiles.Item(i)
Next i
End Sub
=============================

Now...here'z a little additional stuff that I would like Excel to
perform...

After making this macro as guided by Vikram in the above hyperlink, I
am able to get the path to all files...like this:

I:\New Songs\DAP Songs\(AA_AB_LAUT_CHALEN)-MERA_DIL_TERA_DEEWANA.mp3
I:\New Songs\DAP Songs\(DIL_SE)-DILSERE_DILSERE.mp3
I:\New Songs\BEST OF STERIO NATION\01 - ISHQ HOGAYA.mp3
I:\New Songs\NAYEE PADOSAN\01 - SARI SARI RAINA.mp3
I:\New Songs\Aashiqui - Abhijeet\01 - Track 1.rmj

Now what exactly I wanna do is to get the names of the Folders (ONLY,
as highlighted in red colour in the above lines) instead of
files...like I want instead of above five lines...

DAP Songs
Best of STERIO NATION
NAYEE PADOSAN
Aashiqui - Abhijeet

NB: As DAP Songs has two files in it, so it appeared twice in the above
list...but since it is only one directory, I want it in my list once
only...

Hope to receive help ASAP :) THANK YOU GUYz once again in advance for
any help.

Kind regards.

GC.


--
GC.
------------------------------------------------------------------------
GC.'s Profile:
http://www.excelforum.com/member.php...o&userid=16652
View this thread: http://www.excelforum.com/showthread...hreadid=318633