Opening text files sequentially in Excel macro

[Hchristian_314]

I have multiple text files that I want to open in Excel, but only one at a time. I can do this with a macro successfully for the first file in the directory, but how do I get the macro to open the next file each time I run It? Alternatively I could loop the macro to do them all in one execution sequence, however I would rather not do it that way, as there are a number of things I need to do to each file's contents after opening.

Thanks,
Harry

 

[gallen]

Hi Harry

This code may help. It loops through all text files in a given directory and prints the name in the immediate window. You should be able to alter it to suit your needs

 Sub LoopFiles()

    Dim sFileName As String
    Dim sPath As String
    Dim sFilter As String
        
    'Path of Files
    sPath = "C:\Test\"
    
    'Filter to only get txt files
    sFilter = "*.txt"
    
    'Get first filename
    sFileName = Dir(sPath & sFilter)
    
    'If filename isn't blank then send print to immediate iwndow and get next filename then repeat
    Do While sFileName <> ""
        Debug.Print sFileName
        'Get Next File
        sFileName = Dir
    Loop
    
 End Sub

Naturally you will need to change the sPath variable to suit

 

[Fluff]

Similar to gallen

Sub GetFileNames()

    Dim FileNme As String
    Dim FilePth As String
        
    FilePth = "C:\Users\Fluff\Documents\Excel files\"
    
    FileNme = Dir(FilePth & "*.xls")
    
    Do While FileNme <> ""
        With Sheets("Files").Range("A" & Rows.Count).End(xlUp)
            .Offset(1).Value = FilePth
            .Offset(1, 1) = FileNme
        End With
        FileNme = Dir
    Loop
    
 End Sub

This will list the file path & file name in a sheets called Files.
Then you can do

Sub DoSomething()

    Dim FileNme As String
    Dim FilePth As String

    FilePth = ActiveCell
    FileNme = ActiveCell.Offset(, 1)
    
    Workbooks.Open FilePth & FileNme

End Sub

Select cell in col A for the file you want to open & then run the above