Below is what I have put together to generate a list of all files located in a specific Directory. It generates the list and then puts it in column "A". In Column "B" it puts the last number in the name of the file after it removes the ".txt". My issue is I need to put the first letter of the fiel name in column "C". The only problem is the list it generates includes the entire path name ex: C:\My Documents\Mike\...\...\AlphaReport1.txt

I have it set that I get the "1" in coulmn "B", but I need to find a way to get the "A" in column "C". Any help would be great. I thought about trying to get it the same way I got the "1" out, but the file names won't always be the same length, and at this point I am lost as to what to do.

Sub FindFiles()
With Application.FileSearch
.NewSearch
.LookIn = ThisWorkbook.Path & "\ProgramData\FileData\" & "RawData"
.SearchSubFolders = False
.Filename = "*.txt"
.MatchTextExactly = True
.FileType = msoFileTypeExcelWorkbooks
If .Execute() 0 Then
For i = 1 To .FoundFiles.Count
Cells(i, 1) = Right(.FoundFiles(i), (Len(.FoundFiles(i)) - Len(mypath) - 1))
Next i
For i = 1 To .FoundFiles.Count
Cells(i, 1).Value = Replace(Cells(i, 1).Value, ":\", "C:\")
Cells(i, 2).Value = Right(Cells(i, 1).Value, 5)
Cells(i, 2).Value = Replace(Cells(i, 2).Value, ".txt", "")
Next i
Else
MsgBox "There were no files found."
End If
End With
End Sub

Thanks for any advise or assistance you can give me.

Mike

something like this to look from right to left should help
x = Application.Find("\", StrReverse(.FoundFiles(i))) - 2
You need to use StrReverse. If your version doesn't have it you can use this
in a regular module.

Public Function StrReverse(reverseString As String) As String
Dim i As Long
For i = Len(reverseString) To 1 Step -1
StrReverse = StrReverse & Mid(reverseString, i, 1)
Next i
End Function

--
Don Guillett
SalesAid Software

"Mike Etzkorn" wrote in message
...
Below is what I have put together to generate a list of all files located
in a specific Directory. It generates the list and then puts it in column
"A". In Column "B" it puts the last number in the name of the file after it
removes the ".txt". My issue is I need to put the first letter of the fiel
name in column "C". The only problem is the list it generates includes the
entire path name ex: C:\My Documents\Mike\...\...\AlphaReport1.txt

I have it set that I get the "1" in coulmn "B", but I need to find a way
to get the "A" in column "C". Any help would be great. I thought about
trying to get it the same way I got the "1" out, but the file names won't
always be the same length, and at this point I am lost as to what to do.

Sub FindFiles()
With Application.FileSearch
.NewSearch
.LookIn = ThisWorkbook.Path & "\ProgramData\FileData\" & "RawData"
.SearchSubFolders = False
.Filename = "*.txt"
.MatchTextExactly = True
.FileType = msoFileTypeExcelWorkbooks
If .Execute() 0 Then
For i = 1 To .FoundFiles.Count
Cells(i, 1) = Right(.FoundFiles(i), (Len(.FoundFiles(i)) -
Len(mypath) - 1))
Next i
For i = 1 To .FoundFiles.Count
Cells(i, 1).Value = Replace(Cells(i, 1).Value, ":\", "C:\")
Cells(i, 2).Value = Right(Cells(i, 1).Value, 5)
Cells(i, 2).Value = Replace(Cells(i, 2).Value, ".txt", "")
Next i
Else
MsgBox "There were no files found."
End If
End With
End Sub

Thanks for any advise or assistance you can give me.

Mike

Sub FindFiles()
Dim myPath as String
Dim lLen as Long, i as Long
myPath = ThisWorkbook.Path & "\ProgramData\FileData\RawData"
lLen = len(myPath) + 1
With Application.FileSearch
.NewSearch
.LookIn = mypath
.SearchSubFolders = False
.Filename = "*.txt"
' .MatchTextExactly = True
.FileType = msoFileTypeExcelWorkbooks
If .Execute() 0 Then
For i = 1 To .FoundFiles.Count
Cells(i, 1) = Right(.FoundFiles(i), Len(.FoundFiles(i)) - lLen)
Cells(i, 2).Value = Replace(Right(Cells(i, 1).Value, 5), ".txt", "")
Cells(i, 3).Value = Left(Cells(i,1).Value,1)
Next i
Else
MsgBox "There were no files found."
End If
End With
End Sub

Might work unless you wanted the path in column 1.

--
Regards,
Tom Ogilvy


"Mike Etzkorn" wrote in message
...
Below is what I have put together to generate a list of all files located
in a specific Directory. It generates the list and then puts it in column
"A". In Column "B" it puts the last number in the name of the file after it
removes the ".txt". My issue is I need to put the first letter of the fiel
name in column "C". The only problem is the list it generates includes the
entire path name ex: C:\My Documents\Mike\...\...\AlphaReport1.txt

I have it set that I get the "1" in coulmn "B", but I need to find a way
to get the "A" in column "C". Any help would be great. I thought about
trying to get it the same way I got the "1" out, but the file names won't
always be the same length, and at this point I am lost as to what to do.

Sub FindFiles()
With Application.FileSearch
.NewSearch
.LookIn = ThisWorkbook.Path & "\ProgramData\FileData\" & "RawData"
.SearchSubFolders = False
.Filename = "*.txt"
.MatchTextExactly = True
.FileType = msoFileTypeExcelWorkbooks
If .Execute() 0 Then
For i = 1 To .FoundFiles.Count
Cells(i, 1) = Right(.FoundFiles(i), (Len(.FoundFiles(i)) - Len(mypath)
- 1))
Next i
For i = 1 To .FoundFiles.Count
Cells(i, 1).Value = Replace(Cells(i, 1).Value, ":\", "C:\")
Cells(i, 2).Value = Right(Cells(i, 1).Value, 5)
Cells(i, 2).Value = Replace(Cells(i, 2).Value, ".txt", "")
Next i
Else
MsgBox "There were no files found."
End If
End With
End Sub

Thanks for any advise or assistance you can give me.

Mike

Where exactly would I put that into my current macro?

Thanks,

Mike

This would work except for I do still need the full path name in column "A" - since I use that to open the workbooks with outer macros. It there anyway to make what you provided me still have the full path in column "A".

Thanks,

Mike

Sorry, I forgot to mention, I am running ExcelXP and WindowsXP if that helps.