Indexing a CD in Excel?

Posted by Matthew Halliday on January 09, 2002 3:06 AM

I have a series of folders on a set of 30-odd CDs - some 500 folders on each CD - each folder contains a series of text files - with only a few in each folder begining with AA. I want to list ALL the AA numbers only (so search on AA*) either in a workbook with the numbers in consecutive order in collumns across the page or in an Access database, as we need to refer back to them frequently, but I want to produce an index of some form to save trawling through 30+ - and growing daily - CDs. I'd like to do it in either Excel - as it's what we all use - or VB6. The folder structure is &LT;drive>:\xxxxx\AAxxxxx, eg d:\41111\AA10222 . I can do it in a UNIX script, but not everyone knows or has access to Unix. Any ideas for starters?

Thanks

Posted by Matthew Halliday on January 09, 2002 3:19 AM

can list ALL the contents of the cd into a text file and import that - if it helps, but it lists it in 4 collumns and all the AA numbers are in the first collumn or some of the 2nd. This unfiltered file end up at around 75 pages!
I can get the AA numbers listed into 10 collumns in Excel - and that's the format I'd like.

Posted by faster on January 09, 2002 8:38 AM

A version of this code was posted earlier. Run it
in a blank worksheet. It uses the current Excel
directory. To set the directory select open file,
navigate to the desired directory, and select cancel.
You can also hard code the current directory. Search
for it in help.

Sub GetAllFilesInDirectory()
Dim i

With Application.FileSearch
.NewSearch
.FileType = msoFileTypeAllFiles ' msoFileTypeExcelWorkbooks
.LookIn = CurDir()
.SearchSubFolders = True 'true searches subfolders, false does not
.Execute

For i = 1 To .FoundFiles.Count
Range("A" & i) = .FoundFiles(i)
MyLen = Len(Range("A" & i))
Next i

End With

End Sub

Posted by faster on January 09, 2002 8:50 AM

Does Ignore All not do it? After you search and it stops at this error do ignore all.

Posted by Matt on January 11, 2002 5:07 AM

Thanks for that.

I did spend some time doing a search through the list but was probably looking for the wrong thing. With Application.FileSearch .NewSearch .FileType = msoFileTypeAllFiles ' msoFileTypeExcelWorkbooks .LookIn = CurDir() .SearchSubFolders = True 'true searches subfolders, false does not .Execute For i = 1 To .FoundFiles.Count Range("A" & i) = .FoundFiles(i) MyLen = Len(Range("A" & i)) Next i End With

Posted by faster on January 11, 2002 11:00 AM

Filter on AA

With Application.FileSearch .NewSearch .FileType = msoFileTypeAllFiles ' msoFileTypeExcelWorkbooks .LookIn = CurDir() .SearchSubFolders = True 'true searches subfolders, false does not .Execute For i = 1 To .FoundFiles.Count Range("A" & i) = .FoundFiles(i) MyLen = Len(Range("A" & i)) Next i End With

This may work better.

Sub GetAllFilesInDirectory()
Dim i
Dim MyString As String

'set searchstring
Dim SearchString As String
SearchString = "AA"

With Application.FileSearch
.NewSearch
.FileType = msoFileTypeAllFiles ' msoFileTypeExcelWorkbooks
.LookIn = CurDir()
.SearchSubFolders = True 'true searches subfolders, false does not
.Execute

'set this range to the desired starting cell
Range("A1").Select

For i = 1 To .FoundFiles.Count
MyString = .FoundFiles(i)
If InStr(1, MyString, SearchString, 1) Then
Selection = .FoundFiles(i)
Selection.Offset(1, 0).Select
End If
Next i

End With

End Sub