Good Day:

I am trying to use the following code to retrieve filenames from a drive and post them in excel. It works admirably, but I'm having two problems.

1)I need the file extensions to be listed as well for the server I'll upload to later.

    ex.) examplefile.pdf not just examplefile

2)I need to prevent certain file types from being added (same reason).

    ex.) examplefile.doc

3)Any uppercase file extensions must be converted to lowercase

    ex.) examplefile.PDF --> examplefile.pdf

4)Special characters in the File Name (!,@,#,$,% etc.) must be replaced be underscore.

That said, here's the code:

Sub GetFiles()
    Dim ary
    Set pa = Application.FileSearch
    With pa
        .LookIn = "X:\"
        .SearchSubFolders = False
        .Filename = "*.*"

        If .Execute(SortBy:=msoSortByFileName) > 0 Then

            For i = 1 To .FoundFiles.Count
                ary = Split(.FoundFiles(i), "\")
                Range("A500").End(xlUp).Offset(1, 0).Value = _
                Left(ary(UBound(ary)), _
                InStr(ary(UBound(ary)), ".") - 1)
            Next i

        End If

    End With
End Sub

I've tried using the name function, among other things, but it doesn't work with wildcards. I apologize in advance if the answer to this question is obvious.

Thanks in advance,

John Smith.

-John Smith