How to Get Folder Names

[GC.]

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

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

Kind regards.

GC.

 

[JulieD]

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

 

[GC.]

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

 

[mangesh_yadav]

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

 

[JulieD]

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

 

[mangesh_yadav]

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

 

[GC.]

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

 

[mangesh_yadav]

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

 

[GC.]

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.

 

[mangesh_yadav]

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_yadav]

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

 

[GC.]

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

 

[JulieD]

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

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.

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

 

[mangesh_yadav]

try this:

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, "\")
xLen = Len(x(0))
myDir = x(UBound(x) - 1)

If InStr(1, k, myDir, 1) = 0 Then
Range(Rng).Value = myDir
m = m + 1
k = k & "|" & myDir
End If

Next i

End Su

 

[GC.]

I'm extremely grateful from the bottom of my heart Mangesh & JulieD...

@JulieD: Yes you're right...I'm doing the copying pasting stuff...sinc
I don't know anything about VB...anything...so once again apologize fo
the miscommunication there...however as Mangesh...last script ha
finally worked the way I wanted to...so won't be editing you
script...however if you believe that I should checkout your script...
would be extremely grateful if you could post it once again lik
Mangesh...enabling me to perform the old copying pasting thingy...


@Mangesh: Man O man...simply out of this world...I never knew tha
forums could be so excellent...and people like you...simply great!

All the way you kept a very close follow-up and provided my th
TOP-OF-THE-LINE support and that too for FREEE....SIMPLY MARVELLOUS!

I've no words to describe how helpful you guyz have been...

BTW: Here're the results that I'm getting...

I applied this script of yours Mangesh...

---------------------------------------------------------------------------------------
Sub IndexFiles()

With Application.FileSearch
.LookIn = "E:\winxp"
.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, "\")
xLen = Len(x(0))
myDir = x(UBound(x) - 1)

If InStr(1, k, myDir, 1) = 0 Then
Range(Rng).Value = myDir
m = m + 1
k = k & "|" & myDir
End If

Next i

End Sub
---------------------------------------------------------------------------------------

And achieved these results...

I386
80
801
802
803
1033
04 Windows Media Player 9.0 for 2K XP
98MAPI
98OMI
ACROBAT
Advanced.XP.Tweak.v2.4.251.WinXP.CRACKED-EiTheL
ANSI
ApplicationWizardXP
ASYNC
Black 4 WinXP
BLAINF
BRI
CIMV2R5
CITRIX
CMMGR
CollegeMessenger_XP
comboXP
COMCTL
COMPDATA
Controls
Cursor XP
CursorXP Plus 1.30 Enhanced
DEFAULT
DEVUPGRD
DirectX9
DLL
DMICALL
DOCS
DotNEt Framework
Drinkit XP
DRW
DTCINF
DVD
EASTMAN
EICON
Emergency RecoveryXP
ENTINF
EQN
ethat251
EX40
EX40A
EX40SP3
EX40SP4
EX50SP1
EX50SP2
ExploreexassistXP
FAX
FILES
FONTS
FontZipXP
GdiPlus
HBarXP
HideFolderXP
HotKeyControlXP
HPTOOLS
IAS
IBMAV
ICM
IconXP 2
IEMIG
KeyremapperXP
LAN tak XP
LANG
LICENSE
LogModeXP
Longhorn Transformer
MemStatXP
Microsoft BootVis-Tool
MODEMSHR
MPS
MSGQUEUE
MSI
MSNEXPLR
MSP
MSTSC_HPC
MSWINCRT
NECKBD
NECPA
NECWPS
NETBEUI
ObjectBarXP
OCTOPUS
OEWAB
PBA
PERINF
power toolzXP
power toysXP
PRI
PRINT
PWS
REALPORT
RescueXP
RUMBA
SBSINF
searchXP
SETUP
skinsXP
SNA
SRVINF
STH VCD Player 5.5 (Cracked Version)
SYSTEM32
techfactXP
Theme XP
TIGERJET
TOOLS
TRANSACT
TruelockXP
Tuneup XP
TweakXp
TweakXP 2003
UPDATE_FOR_WINDOWSXP
Updates
USMT
VALUEADD
VCRTL
WBEMODBC
WIA
WIN9XUPG
Windows Media Palyer Update
Windows Media Player FunPack Xmas Edition
Windows XP CD Key and Product ID Changer
WindowsXP Home Xmas FunPack
WINNTUPG
WinXP
WinXP Professional SP1 CD Boot Floppy
WMP
Xp Vidia Saver

Just performed an extra step of sorting the list...

Once again grateful for your courtesy of helping me out

Kind regards.

NB: The topic can be closed (if desired).

GC

 

[Bob Phillips]

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)

 

[JulieD]

Hi GC

it really doesn't matter to me if you test my code or not, if you have a
workable solution - great - that's really what counts in the end.

Cheers
JulieD

 

[GC.]

Thank you once again for your reply JulieD and you're right on th
point...

One more little question...is there any forum of this same sort o
Microsoft Access? If so kindly provide me the link...will be grateful


Kind regards.

GC

 

[JulieD]

Hi GC

there are a number of great access groups, a good all purpose one is
- microsoft.public.access.gettingstarted

then some specific ones are
- microsoft.public.access.queries
- microsoft.public.access.forms
- microsoft.public.access.reports

you could also check out
www.mvps.org/access for some web-based info.

Cheers
JulieD