@Dan
Classic! lol : )
Yes I believe I can manage search and replace once the text file is open. However, I'm trying to run my macro in Excel, have it export the text, and have it perform the search and replacements while my code in Excel proceeds forth with the rest of my code(without stopping to open the text file and manually search and replace the items).
@Hui
Here is the function and sub that search and replaces text in text file.
Any ideas on how I can use this to search and replace tabs?
[pre]<br />
Sub FindAndReplace(filePath As String, findWhat As String, _<br />
replaceWith As String)<br />
' from Excel Help:<br />
' A variable-length string can contain up to approximately 2 billion<br />
' (2^31) characters.<br />
Dim nextFileNum As Long<br />
Dim oldFileContents As String<br />
Dim newFileContents As String<br />
Dim textFileTypes() As String<br />
Dim fileExtension As String</p>
<p> If Len(Dir(filePath)) = 0 Then<br />
Exit Sub<br />
End If</p>
<p> ' only act on "text" files<br />
textFileTypes = QuoteString("txt csv html xml", ","
</p>
<p> fileExtension = LCase$(Right$(filePath, 3))</p>
<p> If UBound(Filter(textFileTypes, fileExtension)) = -1 Then<br />
Exit Sub<br />
End If</p>
<p> ' open file and read contents<br />
nextFileNum = FreeFile</p>
<p> Open filePath For Input As #nextFileNum<br />
oldFileContents = Input$(LOF(nextFileNum), #nextFileNum)<br />
Close #nextFileNum</p>
<p> ' replace old char with new char<br />
newFileContents = Replace(oldFileContents, findWhat, replaceWith)</p>
<p> ' reopen file and write new contents<br />
nextFileNum = FreeFile</p>
<p> Open filePath For Output As #nextFileNum<br />
Print #nextFileNum, newFileContents<br />
Close #nextFileNum</p>
<p>End Sub</p>
<p>Function QuoteString(str As String, delimiter As String) As String()</p>
<p>Dim tempString() As String<br />
Dim newString As String</p>
<p> newString = Replace(str, " ", delimiter)</p>
<p> ' split the string into an array, using delimiter<br />
tempString = Split(newString, delimiter)</p>
<p> QuoteString = tempString</p>
<p>End Function<br />
''''''''''''''''''''''''''''''''''''''''''<br />
''''''''''''''''''''''''''''''''''''''''''<br />
''''''''''''''''''''''''''''''''''''''''''</p>
<p>Sub TestFindAndReplace()</p>
<p> On Error GoTo ErrorHandler<br />
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' find and replace underneath right here<br />
Call FindAndReplace("C:UsersAnonymousDesktopTab Delimited File.txt", "text to find", "text to replace"
</p>
<p>ProgramExit:<br />
Exit Sub<br />
ErrorHandler:<br />
MsgBox Err.Number & " - " & Err.Description<br />
Resume ProgramExit<br />
End Sub<br />
''''''''''''''''''''''''''''''''''''''''''</p>
<p>[/pre]