Macro - Insert File Names

HELP PLEASE

Board Regular
Joined
Jan 8, 2005
Messages
156
Hello. I have a file on my Desktop called "Names" and in the folder is a number of .txt files. Is there a way I can import the name of the .txt files from this folder into column A of a spreadsheet?
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Hello. I have a file on my Desktop called "Names" and in the folder is a number of .txt files. Is there a way I can import the name of the .txt files from this folder into column A of a spreadsheet?

Greetings,

If I'm correct in presuming you're referring to a folder, a simple way would be to put this code in a standard module and either have the workbook housed in the same folder, or change:

fso.GetFolder(ThisWorkbook.Path & Application.PathSeparator)

...to an accurate pathway.

In a Standard Module:

Code:
Option Explicit
 
Sub Files_List()
Dim fso As Object
Dim fol As Object
Dim fil As Object
Dim lRow As Long
 
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    Set fol = fso.GetFolder(ThisWorkbook.Path & Application.PathSeparator)
    
    For Each fil In fol.Files
        If fil.Type = "Text Document" Then
            lRow = lRow + 1
            Cells(lRow, 1).Value = fil.Name
        End If
    Next
End Sub

Hope this helps,

Mark
 
Upvote 0
This code will do what you are after without using FSO which does not work with Excel version 2007:
Code:
Sub ListFilesInFolder()
'List all filenames in the listed folder in Column A
Dim f()
Dim strTemp As String
Dim intCount As Integer
Dim ws As Worksheet
ChDir "C:\Users\John\Desktop\Names"
    intCount = 0
    strTemp = Dir("*.txt")
    Do While strTemp <> Empty
        intCount = intCount + 1
        ReDim Preserve f(1 To intCount)
        f(intCount) = strTemp
        strTemp = Dir
    Loop
Set ws = ActiveWorkbook.ActiveSheet
ws.Cells(1, 1).Resize(UBound(f), 1).Value = _
            WorksheetFunction.Transpose(f)
End Sub
Change the pathway in the code to your Desktop path.

This will put the list of filenames in Column A of the active worksheet.
 
Upvote 0
This will do as you asked. Note. It will overwrite any existing data in column A of the active sheet.
Code:
Sub List_Files()
    Dim lngX As Long
    Dim strPath As String
    Dim strExtension As String
    
    strPath = Environ("userprofile") & "\Desktop\Names"
    strExtension = "txt"   'make this an asterisk (*) for all files
    dirOutput = Dir(strPath & "\*." & strExtension)
    
    Do While dirOutput <> ""
        lngX = lngX + 1
        Cells(lngX, 1) = dirOutput
        dirOutput = Dir
    Loop
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top