Want to Pull File Names From Folder into Excel

Hi,

I'm looking for a way to import the names of all the files in a particular folder into excel.

The directory will always be the same so I want it re-scanned automatically every time I open the spreadsheet and then for the names of the files to be imported. Is there an easy way to do this? I've tried grabbing snippets of vba code and fiddling, but nothing is working quite right.

In addition to that, once all of the file names are imported I am wondering if it is possible to delete entire rows based on the extensions listed (ex. ".xml")? Many files are listed two or three times in the directory and I only need ones with certain extensions.

Thanks in advance for any help. I greatly appreciate it!

hope this help.

Sub ListAllFiles()

'Set a reference to 'Microsoft Scripting Runtime' under menu option:
' Tools > References > find and tick 'Microsoft Scripting Runtime'

Dim fso As New FileSystemObject
Dim fso_Folder As Folder
Dim fso_File As File
Dim file_count As Long

'Change the directory below to the one you want to search
Set fso_Folder = fso.GetFolder("C:\")
file_count = 0

For Each fso_File In fso_Folder.Files
file_count = file_count + 1
Cells(file_count, 1).Value = fso_File.Name
Next fso_File

Set fso = Nothing

End Sub

this module is to list all the files in the specified directory.

here you go boss.

Sub ListAllFiles()

'Set a reference to 'Microsoft Scripting Runtime' under menu option:
' Tools > References > find and tick 'Microsoft Scripting Runtime'

Dim fso As New FileSystemObject
Dim fso_Folder As Folder
Dim fso_File As File
Dim file_count As Long
Dim strExt As String

'Change the directory below to the one you want to search
Set fso_Folder = fso.GetFolder("C:\")
file_count = 0

'Here's your file extension
strExt = "xls"

For Each fso_File In fso_Folder.Files

If UCase(Right(fso_File.Name, (Len(fso_File.Name) - InStrRev(fso_File.Name, ".")))) = UCase(strExt) Then
    file_count = file_count + 1
    Cells(file_count, 1).Value = fso_File.Name
End If

Next fso_File

Set fso = Nothing

End Sub

Hi lytsty,

Welcome to the forum!!

This should do the job:

Option Explicit
Sub Macro1()
    
    'http://www.excelforum.com/excel-programming-vba-macros/858256-want-to-pull-file-names-from-folder-into-excel.html
    
    'List all file names with extensions not found in 'vayMyArray' residing in the 'strMyDir' directory.
    
    Dim strMyDir As String
    Dim varMyArray As Variant
    Dim objFSO As Object, _
        objFile As Object
    Dim lngMyRow As Long
    
    strMyDir = "G:\" 'Directory to look in. Don't forget the trailing backslash.
    varMyArray = Array("xml", "txt") 'Files with these extensions will be ignorged (i.e. not imported).
    
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    
    'Clear any existing imported file names from Col. A (starting at Row 2) from Sheet1. Change to suit.
    lngMyRow = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
    If lngMyRow >= 2 Then
        Sheets("Sheet1").Range("A2:A" & lngMyRow).ClearContents
    End If
    
    lngMyRow = 0 'Initialise variable.
        
    For Each objFile In objFSO.GetFolder(strMyDir).Files
        If IsNumeric(Application.Match(objFSO.GetExtensionName(strMyDir & objFile.Name), varMyArray, 0)) = False Then
            If lngMyRow = 0 Then 'Initial row number output. Change to suit.
                lngMyRow = 2
            Else
                lngMyRow = lngMyRow + 1
            End If
            Sheets("Sheet1").Range("A" & lngMyRow).Value = objFile.Name
        End If
    Next objFile
    
    Set objFSO = Nothing
    Set objFile = Nothing
        
End Sub

Regards,

Robert

Thanks for letting is know and you're welcome. If you could mark the thread as solved it would be appreciated.