qitjch
05-24-2016, 09:00 AM
Hello,
I am trying to create a macro that will loop through an entire directory of text files. For each text file, I want to open it and then find any special characters and replace them with "". The specific character I want to replace looks like a cross 16239
I have a macro currently that will replace the character but I have to specify the file:
Sub looptexttest()
'remove extra characters from text file
Dim sBuf As String
Dim sTemp As String
Dim iFileNum As Integer
Dim sFileName As Variant
' Edit as needed
sFileName = Application.GetOpenFilename()
If sFileName = False Then
MsgBox "No File Selected", vbExlamation
Worksheets("Summary").Select
Exit Sub
End If
iFileNum = FreeFile
Open sFileName For Input As iFileNum
Do Until EOF(iFileNum)
Line Input #iFileNum, sBuf
sTemp = sTemp & sBuf & vbCrLf
Loop
Close iFileNum
sTemp = Replace(sTemp, "", "")
'Save txt file as (if possible)
iFileNum = FreeFile
Open sFileName For Output As iFileNum
Print #iFileNum, sTemp
Close iFileNum
End Sub
I also have a macro that loops through all text files in a directory and is supposed to find/replace; however it does not seem to be working and I cannot figure out why.
Sub FindAndReplaceText()
Dim FileName As String
Dim FolderPath As String
Dim FSO As Object
Dim I As Integer
Dim SearchForWords As Variant
Dim SubstituteWords As Variant
Dim Text As String
Dim TextFile As Object
'Change these arrays to word you want to find and replace
SearchForWords = Array("")
SubstituteWords = Array("")
'Change the folder path to where your text files are.
FolderPath = "\\SVUSINDFILE1\IndyManufacturing\Finance\Daily PPV Reporting\GL Files"
Set FSO = CreateObject("Scripting.FileSystemObject")
FolderPath = IIf(Right(FolderPath, 1) <> "\", FolderPath & "\", FolderPath)
FileName = Dir(FolderPath & "\*.txt")
Do While FileName <> ""
FileSpec = FolderPath & FileName
'Read all the file's text into a string variable.
Set TextFile = FSO.OpenTextFile(FileSpec, 1, False)
Text = TextFile.ReadAll
TextFile.Close
'Scan the string for words to replace and write the string back to the file.
Set TextFile = FSO.OpenTextFile(FileSpec, 2, False)
For I = 0 To UBound(SearchForWords)
Replace Text, SearchForWords(I), SubstituteWords(I)
Next I
TextFile.Write Text
TextFile.Close
FileName = Dir()
Loop
End Sub
Any help combining these two macros would be greatly appreciated. I am just trying to be able to replace all the special characters in a directory of text files.
note: the special character doesn't want to show on this post, but it is showing in my vba code as a square.
Thanks!
I am trying to create a macro that will loop through an entire directory of text files. For each text file, I want to open it and then find any special characters and replace them with "". The specific character I want to replace looks like a cross 16239
I have a macro currently that will replace the character but I have to specify the file:
Sub looptexttest()
'remove extra characters from text file
Dim sBuf As String
Dim sTemp As String
Dim iFileNum As Integer
Dim sFileName As Variant
' Edit as needed
sFileName = Application.GetOpenFilename()
If sFileName = False Then
MsgBox "No File Selected", vbExlamation
Worksheets("Summary").Select
Exit Sub
End If
iFileNum = FreeFile
Open sFileName For Input As iFileNum
Do Until EOF(iFileNum)
Line Input #iFileNum, sBuf
sTemp = sTemp & sBuf & vbCrLf
Loop
Close iFileNum
sTemp = Replace(sTemp, "", "")
'Save txt file as (if possible)
iFileNum = FreeFile
Open sFileName For Output As iFileNum
Print #iFileNum, sTemp
Close iFileNum
End Sub
I also have a macro that loops through all text files in a directory and is supposed to find/replace; however it does not seem to be working and I cannot figure out why.
Sub FindAndReplaceText()
Dim FileName As String
Dim FolderPath As String
Dim FSO As Object
Dim I As Integer
Dim SearchForWords As Variant
Dim SubstituteWords As Variant
Dim Text As String
Dim TextFile As Object
'Change these arrays to word you want to find and replace
SearchForWords = Array("")
SubstituteWords = Array("")
'Change the folder path to where your text files are.
FolderPath = "\\SVUSINDFILE1\IndyManufacturing\Finance\Daily PPV Reporting\GL Files"
Set FSO = CreateObject("Scripting.FileSystemObject")
FolderPath = IIf(Right(FolderPath, 1) <> "\", FolderPath & "\", FolderPath)
FileName = Dir(FolderPath & "\*.txt")
Do While FileName <> ""
FileSpec = FolderPath & FileName
'Read all the file's text into a string variable.
Set TextFile = FSO.OpenTextFile(FileSpec, 1, False)
Text = TextFile.ReadAll
TextFile.Close
'Scan the string for words to replace and write the string back to the file.
Set TextFile = FSO.OpenTextFile(FileSpec, 2, False)
For I = 0 To UBound(SearchForWords)
Replace Text, SearchForWords(I), SubstituteWords(I)
Next I
TextFile.Write Text
TextFile.Close
FileName = Dir()
Loop
End Sub
Any help combining these two macros would be greatly appreciated. I am just trying to be able to replace all the special characters in a directory of text files.
note: the special character doesn't want to show on this post, but it is showing in my vba code as a square.
Thanks!