Open Multiple Workbooks In Folder And Run Text To Column Using VBA

[Excel Fox]

Or if you want to insert 10 columns also after the Text to Column operation, try this...

Sub LoopFolder()
     
    Dim strFile As String
    Dim strFileType As String
    Dim strPath As String
    Dim lngLoop As Long
    Dim wbk As Workbook
    
    strPath = "C:\ExcelFox"
    strFileType = "Book*.xlsx" 'Split with semi-colon if you want to specify the file types. Example ->> "*.xls;*.doc"
     
    For lngLoop = LBound(Split(strFileType, ";")) To UBound(Split(strFileType, ";"))
        strFile = Dir(strPath & "\" & Split(strFileType, ";")(lngLoop))
        Do While strFile <> ""
            Set wbk = Workbooks.Open(strPath & "\" & strFile, False, True)
            With wbk.Sheets(1)
                .Range("A:A").TextToColumns Destination:=.Range("A1")
                .Range("A:J").Insert
                .Parent.Close 1
            End With
        Loop
    Next lngLoop
     
    strFile = vbNullString
    strFileType = vbNullString
    strPath = vbNullString
    lngLoop = Empty
     
End Sub