Zamenjajte besedilo v besedilni datoteki z uporabo VBA v programu Microsoft Excel

Anonim

Spodnje makre lahko uporabite za zamenjavo besedila v besedilni datoteki,
npr. ko želite spremeniti ločevalnik stolpcev v besedilni datoteki
preden ga uvozite v Excelov delovni list ali potem, ko ga izvozite v besedilno datoteko.

Sub ReplaceTextInFile (SourceFile As String, _ sText As String, rText As String) Dim TargetFile As String, tLine As String, tString As String Dim p As Integer, i As Long, F1 As Integer, F2 As Integer TargetFile = "RESULT.TMP "If Dir (SourceFile) =" "then Exit Sub If Dir (TargetFile)" "Then On Error Resume Next Kill TargetFile On Error GoTo 0 If Dir (TargetFile)" "Potem se MsgBox TargetFile & _" že odpre, zapre in izbriše / preimenujte datoteko in poskusite znova. ", _ vbCritical Exit Sub End If End Če je F1 = FreeFile Open SourceFile Za vnos kot F1 F2 = FreeFile Open TargetFile Za izhod kot F2 i = 1 'števec vrstic Application.StatusBar =" Branje podatkov iz " & _ TargetFile & "…" Medtem ko ni EOF (F1) Če je i Mod 100 = 0, potem Application.StatusBar = _ "Bralna vrstica #" & i & "v" & _ TargetFile & "…" Vrstni vnos #F1, tLine Če sText "" Nato ReplaceTextInString tLine, sText, rText End Če natisnete #F2, tLine i = i + 1 Wend Application.StatusBar = "Zapiranje datotek …" Zapri F1 Zapri F2 Ubij izvorno datoteko "izbriši ali iginalno ime datoteke TargetFile As SourceFile 'preimenuj začasno datoteko Application.StatusBar = False End Sub Private Sub ReplaceTextInString (SourceString As String, _ SearchString As String, ReplaceString As String) Dim p As Integer, NewString As String Do p = InStr (p + 1 , UCase (SourceString), UCase (SearchString)) Če je p> 0, potem 'SearchString zamenjajte z ReplaceString NewString = "" Če je p> 1 Potem je NewString = Mid (SourceString, 1, p - 1) NewString = NewString + ReplaceString NewString = NewString + Sredina (SourceString, _ p + Len (SearchString), Len (SourceString)) p = p + Len (ReplaceString) - 1 SourceString = NewString End Če je Če je p> = Len (NewString), potem je p = 0 zanka do p = 0 End Sub Sub TestReplaceTextInFile () ReplaceTextInFile ThisWorkbook.Path & _ "\ ReplaceInTextFile.txt", "|", ";" 'nadomesti vse znake pipe (|) s podpičjem (;) End Sub