Quote Originally Posted by tigeravatar View Post
rapidasia,

Welcome to the forum!
Here is some commented code you can use to import text files:
Sub ImportTextFilesMacro_rapidasia()
    
    'Change the folder path as needed
    Const strFolderPath As String = "C:\Test"
    
    'Declare variables
    Dim FSO As Object                   'Used to access the Windows FileSystem
    Dim strFileName As String           'Used to store the filename
    Dim arrText(1 To 65000) As String   'Used to store the .txt file's text
    Dim TextIndex As Long               'Used by arrText for storing the text in the correct location
    
    'Access the Windows FileSystem
    Set FSO = CreateObject("Scripting.FileSystemObject")
    
    'Use the folderpath to find all the first .txt file in that folder
    strFileName = Dir(strFolderPath & "\*.txt")
    
    'Loop through all the text files
    Do While Len(strFileName) > 0
        'Found a text file, increase the TextIndex
        TextIndex = TextIndex + 1
        
        'Use the Windows FileSystem to read the text file and store that information in arrText
        arrText(TextIndex) = FSO.OpenTextFile(strFolderPath & "\" & strFileName).ReadAll
        
        strFileName = Dir   'Advance the loop
    Loop
    
    'If at least 1 text file was found, output the results in column C
    If TextIndex > 0 Then Range("C1").Resize(TextIndex).Value = Application.Transpose(arrText)
    
    'Cleanup objects and arrays
    Set FSO = Nothing
    Erase arrText
    
End Sub


How to use a macro:
  1. Make a copy of the workbook the macro will be run on
    • Always run new code on a workbook copy, just in case the code doesn't run smoothly
    • This is especially true of any code that deletes anything
  2. In the copied workbook, press ALT+F11 to open the Visual Basic Editor
  3. Insert | Module
  4. Copy the provided code and paste into the module
  5. Close the Visual Basic Editor
  6. In Excel, press ALT+F8 to bring up the list of available macros to run
  7. Double-click the desired macro (I named this one ImportTextFilesMacro_rapidasia)
Hi i'm trying to import multiple text files, however my files are of multiple lines, varying from 1 line to 200 lines. I need a macro to bring these in for me. Any help appriciated.