Batch creating multiple .txt files and with search and replace

kevenson,

I use this code to export all sheets in a workbook as individual text files. Hopefully that gives you a starting point. It sounds like you'll need to do your find/replaces within the loop. If this isn't what you're looking, can you provide more information so we can give you more accurate help?

Sub ExportSheets_as_TxtFiles()
    
    'Change to the correct folder path, be sure to include the ending \
    Const strSavePath As String = "C:\Test\Exported Text Files\"
    
    Dim ws As Worksheet
    
    Application.DisplayAlerts = False
    For Each ws In ActiveWorkbook.Sheets
        ws.SaveAs strSavePath & ws.Name & ".txt", xlUnicodeText
    Next ws
    Application.DisplayAlerts = True
    
End Sub

Tiger, that code will be extremely valuable to me! What I now need to do is find a way to open each .txt file into an individual sheet, the sheet name must be the same as the .txt file that gets imported.

Here is what I have so far for batch find and replace

findArray = Array("av1121", "bv1121 ", "ac1121")
replArray = Array("va1121", "vb1121 ", "gq1121")

For i = 0 To UBound(findArray)
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = findArray(i)
.Replacement.Text = replArray(i)
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
Next i

kevenson,

So you're importing text files into a single workbook, with each sheet being the name of a different text file? And after the import, run the find/replace on the data in each sheet? Is that right?

(And thanks Cutter for adding the code tags )

kevenson,

Are all of the text files in the same folder? Are they tab delimited or comma delimited, etc? Do you want to save the sheets as excel files or as text files?
Assuming yes, tab delimited, and excel files:

Sub tgr()
    
    'Change to the correct folder paths, be sure to include the ending \
    Const strFldrPath As String = "C:\Test\"
    Const strSavePath As String = "C:\Test\Converted Files\"
    
    'Declare variables
    Dim lCalc As XlCalculation
    Dim FSO As Object
    Dim oFile As Object
    Dim findArray() As Variant
    Dim replArray() As Variant
    Dim arrText() As String
    Dim strName As String
    Dim i As Long
    
    'Turn off various settings to enahnce performance
    With Application
        lCalc = .Calculation
        .Calculation = xlCalculationManual
        .EnableEvents = False
        .DisplayAlerts = False
        .ScreenUpdating = False
    End With
    
    'Assume code will fail so that settings will always get turned back on
    On Error GoTo CleanExit
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    findArray = Array("av1121", "bv1121 ", "ac1121")
    replArray = Array("va1121", "vb1121 ", "gq1121")
    
    'Check each file in the Folder Path
    For Each oFile In FSO.GetFolder(strFldrPath).Files
        
        'Check if the file is a txt file
        If LCase(FSO.GetExtensionName(oFile.Path)) = "txt" Then
            
            'Found to be a text file, get its contents
            arrText = Split(FSO.OpenTextFile(oFile.Path).ReadAll, vbNewLine)
            
            'Create a new sheet for the contents and perform various operations
            With Sheets.Add
                
                'Guarantee valid sheet name
                strName = Replace(oFile.Name, ".txt", vbNullString, Compare:=vbTextCompare)
                For i = 1 To 7
                    strName = Replace(strName, Mid(":\/?*[]", i, 1), vbNullString)
                Next i
                strName = Left(WorksheetFunction.Trim(strName), 31)
                
                'Name the sheet
                .Name = strName
                
                'Populate the sheet with the text file's data
                .Range("A1").Resize(UBound(arrText) - LBound(arrText) + 1).Value = Application.Transpose(arrText)
                .UsedRange.TextToColumns .UsedRange, xlDelimited, xlTextQualifierDoubleQuote, True, Tab:=True
                
                'Perform find and replace
                For i = LBound(findArray) To UBound(findArray)
                    .UsedRange.Replace findArray(i), replArray(i)
                Next i
                
                'Make the sheet its own workbook then save and close the new workbook
                .Move
                ActiveWorkbook.SaveAs strSavePath & strName & ".xls", xlExcel8
                ActiveWorkbook.Close False
            End With
        End If
    Next oFile
    
CleanExit:
    'Turn application settings back on
    With Application
        .Calculation = lCalc
        .EnableEvents = True
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With
    
    'Display error message, if any
    If Err.Number <> 0 Then
        MsgBox Err.Description, , "Error: " & Err.Number
        Err.Clear
    End If
    
    'Cleanup
    Set FSO = Nothing
    Set oFile = Nothing
    Erase arrText
    Erase findArray
    Erase replArray
    
End Sub

kevenson,

Because the output is a text file, the VBA code doesn't need to import anything into Excel, which simplifies the code. Because of that strange "ÿþ" (I have no idea where that is coming from), I changed the method of reading the text file. This seems to have taken care of the issue. Give it a try and let me know

Sub tgr()
    
    'Change to the correct folder paths, be sure to include the ending \
    Const strFldrPath As String = "C:\Test\"
    Const strSavePath As String = "C:\Test\Converted Files\"
    
    'Declare variables
    Dim FSO As Object
    Dim oFile As Object
    Dim findArray() As Variant
    Dim replArray() As Variant
    Dim strText As String
    Dim strTemp As String
    Dim i As Long
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    findArray = Array("av1121", "bv1121 ", "ac1121")
    replArray = Array("va1121", "vb1121 ", "gq1121")
    Close #1
    
    'Check each file in the Folder Path
    For Each oFile In FSO.GetFolder(strFldrPath).Files
        
        'Check if the file is a txt file
        If LCase(FSO.GetExtensionName(oFile.Path)) = "txt" Then
            
            'Found to be a text file, get its contents
            Open oFile.Path For Input As #1
            Do While Not EOF(1)
                Line Input #1, strTemp
                If strTemp <> "ÿþ" Then strText = strText & "||" & strTemp
            Loop
            Close #1
            
            'Perform find and replace
            For i = LBound(findArray) To UBound(findArray)
                strText = Replace(strText, findArray(i), replArray(i), Compare:=vbTextCompare)
            Next i
            
            'Create converted file and output converted text to it
            Open strSavePath & "\" & Replace(oFile.Name, ".txt", " (Converted).txt", Compare:=vbTextCompare) For Output As #1
            Print #1, Replace(Mid(strText, 3), "||", vbCrLf)
            Close #1
            
            strText = vbNullString
            
        End If
    Next oFile
    
    'Cleanup
    Set FSO = Nothing
    Set oFile = Nothing
    Erase findArray
    Erase replArray
    
End Sub