File Name List

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
(e-mail address removed)
Mike Etzkorn said:
Below is what I have put together to generate a list of all files located
Click to expand...
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
Click to expand...
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()
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 said:
Below is what I have put together to generate a list of all files located
Click to expand...
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
Click to expand...
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.
 
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
 
Back
Top