Results 1 to 6 of 6
  1. #1
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    File Lister Macro (Excel 2002)

    I know the work has probably been done before, but I could not come up with anything in Search.

    I need a macro that will pull the list of files from the ...pathMy Music directory and list them in Columns A and B. Without exception, every file name is in the following format: <font color=blue>Artist - Title.mp3</font color=blue>.

    If someone could help me with the code that would pull the directory contents over to the spreadsheet (perhaps, Column C), I know how to write the formula that will separate the Artist and Title into the separate columns in A and B.

    Please? <img src=/S/please.gif border=0 alt=please width=31 height=23>
    - Ricky

  2. #2
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: File Lister Macro (Excel 2002)

    Tricky

    Try the following Macro

    Sub BulkFiles()
    Dim FS As Variant
    Dim FO As Variant
    Dim FL As Variant
    Dim MyFile As String
    Dim MyPath As String

    MyPath = "C:temp"

    Set FS = CreateObject("Scripting.FileSystemObject")
    Set FO = FS.getfolder(MyPath)

    Range("A1").Activate
    For Each FL In FO.Files
    ActiveCell.Value = MyPath
    ActiveCell.Offset(0, 1) = FL.Name
    ActiveCell.Offset(1, 0).Activate
    Next
    Set FS = Nothing
    Set FO = Nothing
    End Sub

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: File Lister Macro (Excel 2002)

    Take a look at this thread in the VB/VBA forum and see if it helps.

    Andrew C

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: File Lister Macro (Excel 2002)

    Ummm ... here's some "air code" to help you get started, but you'll have to debug it like crazy ... <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>. This should just get you the list, but parsing should be the easy part. Oh, it provides the complete pathname, but again, you should be able eliminate the path using Find-&-Replace.

    Public Sub LocateMP3Files()
    Dim lngFilesFound As Long, lngFCounter As Long
    Application.ScreenUpdating = False
    With Application.FileSearch
    .NewSearch
    .SearchSubFolders = True ' you may want to comment this line out
    .LookIn = "C:mydocuments
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: File Lister Macro (Excel 2002)

    Well, <img src=/w3timages/censored.gif alt=censored border=0>, I didn't know that stuff was already there!
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Thanks

    Thanks to all of you for showing me the way. <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    - Ricky

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •