Import text files contents and name of text file into Excel with a macro

[bushidowarrior]

Hello all,

I would like to know if it is possible to import the contents of a text file, and the text filename.

Cats.txt (Contents: grey, blue, black)
Dogs.txt (Contents: pink, red, orange)
Cow.txt (Contents: black, white)

- If I run the macro again with Updated text files, it will just update the information
- if I add new text files for example "Lion.txt", it will add it to road number 4.
- If the folder is empty, nothing will happen.

Thank you all

 

[bushidowarrior]

Sorry I forgot to mention that all these text files will be in a folder location which will always be the same.

 

[bushidowarrior]

Hello, and someone tell me where to start please. Thank you very much.

 

[bushidowarrior]

Hello all, I have made some progress.

I have this code now which can read the file names.

Sub LoopThroughFiles()
 
Dim oFSO As Object
Dim oFolder As Object
Dim oFile As Object
Dim i As Integer
 
Set oFSO = CreateObject("Scripting.FileSystemObject")
 
Set oFolder = oFSO.GetFolder("C:\Users\User\Desktop\TEST\")
 
For Each oFile In oFolder.Files
 
    Cells(i + 1, 1) = oFile.Name
 
    i = i + 1
 
Next oFile
 
End Sub

I need to now only make it search for text files and get the contents of the text files in the B column.

Any help would be greatly appreciated.

Thank you

 

[bushidowarrior]

I have progressed a little bit more now, as I have the code only searching for text files.

Sub findBSAfiles()
Dim objFSO    As Object
Dim objFolder As Object
Dim objFile   As Object
Dim strPath   As String
Dim strName   As String
' Specify the folder...
strPath = "C:\Users\User\Desktop\TEST\"
' Use Microsoft Scripting runtime.
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(strPath)
' Check extension of each file in folder.
For Each objFile In objFolder.Files
If Right(objFile.Name, 4) = ".txt" Then
Cells(i + 1, 1) = objFile.Name
 
    i = i + 1

End If
Next objFile ' Display file names in message box.
MsgBox strName

Set objFSO = Nothing
Set objFolder = Nothing
Set objFile = Nothing
End Sub

I just need would have guidance to get the contents of the text file please

 

[bushidowarrior]

I have it working now:

Option Explicit

Sub load()

    ' ADD REFERENCE TO MICROSOFT FILE SYSTEM OBJECT

    Dim objFSO As FileSystemObject
    Dim objFolder As Folder
    Dim objFile As File
    Dim objTextStream As TextStream
    Dim strPath As String
    Dim i As Long

    ' Specify the folder...
    strPath = "C:\Users\User\Desktop\TEST\"

    ' Use Microsoft Scripting runtime.
    Set objFSO = New FileSystemObject
    Set objFolder = objFSO.GetFolder(strPath)

    ' Check extension of each file in folder.
    For Each objFile In objFolder.Files
        If objFSO.GetExtensionName(objFile.Name) = "txt" Then
            Cells(i + 1, 1) = objFile.Name
            Set objTextStream = objFile.OpenAsTextStream(ForReading)
            Cells(i + 1, 2) = objTextStream.ReadAll
            i = i + 1
        End If
    Next
End Sub