List out just the text file name

[Kinki]

Hi,
I have some text files in the D:/temp/temp and I want to list them out in column B of a new spreadsheet. I know of .FoundFiles method which doesn't print out what i want.

Does anyone knows how to apply just the file eg. D:/temp/temp/testdata.txt into cell eg B6 onwards as testdata?


Regards.

 

[masim]

Hie
Try the following...

Code:

Sub Button1_Click()

With Application.FileSearch

.LookIn = "D:\temp\temp"

.FileType = msoFileTypeAllFiles
.SearchSubFolders = True
.Execute
End With


cnt = Application.FileSearch.FoundFiles.Count

For i = 1 To cnt
rng = "B" & i + 5
If Right(Application.FileSearch.FoundFiles.Item(i), 3) = "txt" Then
Range(rng).Value = Application.FileSearch.FoundFiles.Item(i)
End If

Next i

End Sub

Hope this would be helpful.

Regards
Asim

 

[Kinki]

Hi Asim,
It doesn't work well. I want only the value of testData only which is in D:\temp\temp\ drive and the file is testData.txt

Regards,
valerie

 

[Ivan F Moala]

Perhaps something like this ?

Sub TestListFiles()
Dim strFile As String
Dim x As Double

x = 6
strFile = Dir("D:\temp\temp \*.txt")

Columns("B:B").Clear
Do While Len(strFile) > 0
    Cells(x, 2) = strFile
    x = x + 1
    strFile = Dir()
Loop

'// Sort & Format cell range
With Range(Cells(6, 2), Cells(6, 2).End(xlDown))
    .Columns.AutoFit
    .Sort Key1:=Range("B6"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End With

End Sub

 

[Kinki]

Hi

Thank you thank you. Near to it but how do i remove the extension of the file in the value.
Value display should be testdata instead of testdata.txt.

Regards,
valerie

 

[Ivan F Moala]

Look up the Left command.

Eg, while in VBE hit F1 and search for Left

 

[Kinki]

thank you for the additional tips :wink: .

I have used delimiter instead

Dim file As Variant
file = Split(Trim(strFile), ".txt")
MsgBox file(0)

 

[Norie]

How about something like this?

Dim I As Integer
Dim strFileName As String
    
    With Application.FileSearch
        .NewSearch
        
        .LookIn = "D:\temp\temp"
        .Filename = "*.txt"
        .SearchSubFolders = True
        .Execute
    
        For I = 1 To .FoundFiles.Count
        
            strFileName = Mid(.FoundFiles(I), InStrRev(.FoundFiles(I), "\") + 1)
            strFileName = Left(strFileName, Len(strFileName) - 4)
            Range("B" & I + 5) = strFileName
        Next I
    End With

 

[Kinki]

thank you thank you. it is also applicable.