Gary
Give a try to DataText to ColumnsDelimited by hyphen(-)
Ian & Sylvia Darcy Farrow.mp3 into Column A and B
Then DataText to columnsDelimited by period(.)
Note the step where you can "skip" a column
Gord Dibben MS Excel MVP
On Tue, 8 Jan 2008 12:01:05 -0800, Gary wrote:
Hi Don, - thank you so much for the code.
Three steps away from perfection!
1... Is there a way to make it include subdirectories?
2... Can it separate out the file name so the artist and song are in columns
A and B?
3... Can it remove (like find and replace) the file extension?
I know this is a big ask - but it would be sooo much easier if I could find
away to do all this.
Many thanks again
Gary
"Don Guillett" wrote:
Why bother with that when this is one way to import a list of all mp3 files
into an excel worksheet.
Ian & Sylvia - Darcy Farrow.mp3
Ian & Sylvia - Early Morning Rain.mp3
Ian & Sylvia - Four Strong Winds.mp3
Ian & Sylvia - Last Thing On My Mind.mp3
Sub FindFilesA()
Application.ScreenUpdating = False
Dim FN As String
Dim ThisRow As Long
Dim FileLocation As String
'change below to suit
'=============
FileLocation = "c:\mymusic\*.mp3"
'==========
FN = Dir(FileLocation)
Do Until FN = ""
ThisRow = ThisRow + 1
Cells(ThisRow, 1) = FN
FN = Dir
Loop
Application.ScreenUpdating = True
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"Gary" wrote in message
...
Hi Jim,
I have just realised there is probably an easier way to do this.
I am using a cmd prompt command to make a .txt file of all my karaoke
music:
dir *.mp3 list.txt /s /b
But the results give me too much information:
c:\documents and settings\gary\my documents\my karaoke\ARTIST\ARTIST -
SONG (CDG).mp3
Thus far I have been opening this file with MS Word to 'find and replace'
"c:\documents and settings\gary\my documents\my karaoke\" and "_(CDG).mp3"
which leaves me with:
ARTIST\ARTIST - SONG
I then save it and open this new '.txt' file in Excel and use the "\" as
the
delimiter so "ARTIST" is listed in column A and "ARTIST - SONG" is listed
in
column B. I then deleted column A, and save the remaining "ARTIST - SONG"
information back to a .txt file.
I open this new file, but this time I use the "-" as the delimiter to give
"ARTIST" and "SONG" in columns A and B. (this is where my orginal question
came in about how to remove the spaces this created in the "SONGS"
column).
Is there a way I can open the original list.txt file in excel and use a
macro to do all of this for me?
Many thanks
Gary