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 did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
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
 

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
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.
 

pbornemeier

Well-known Member
Joined
May 24, 2005
Messages
3,890
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,553
Messages
5,636,982
Members
416,953
Latest member
broexc

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
Top