我需要在excel的特定列中修改一个带有字符串的文本文件。当我执行代码时，我发现一些值是重复的。它似乎与以某种方式被解释为副本的旧值结束数字有关。然而，这并不是每次都会发生，我很难过。有 10,000 个条目需要更改，但我创建的小测试文件揭示了一个更大的问题。测试文件希望这个链接可以正常工作，如果可以，您会发现 Test.txt 我的测试文件，TestBook.xlsm 和 Test - L5K.txt，其中包含要修改的实际文件的一部分。
提前致谢。
Private Sub CommandButton21_Click()
Dim sBuffer As String, sSubstitute As String, sFileName
As String, sNewTagRef As Variant
Dim sOldFileRef As Variant
Dim Lastrow As Long
Dim i As Integer, iFileNum As Integer
'Path to target file
sFileName = Application.GetSaveAsFilename()
iFileNum = FreeFile
Open sFileName For Input As iFileNum
' Find the last row of specified column
Lastrow = Worksheets("Sheet1").Cells(Rows.Count, 4).End(xlUp).Row
For i = 2 To Lastrow
'sOldFileRef is the search value and sNewTagRef is replacement value
Sheets("Sheet1").Activate
sOldFileRef = ActiveSheet.Cells(i, 4).Value
sNewTagRef = ActiveSheet.Cells(i, 5).Value
'Prepare file text for replacement text
Do While Not EOF(iFileNum)
Line Input #iFileNum, sBuffer
sSubstitute = sSubstitute & sBuffer & vbNewLine
Loop
Close iFileNum
'Search and Replace text
sSubstitute = Replace(sSubstitute, sOldFileRef, sNewTagRef)
'Modify text file
iFileNum = FreeFile
Open sFileName For Output As iFileNum
Print #iFileNum, sSubstitute
Next i
Close iFileNum
End Sub
编辑：在查看你的源文件后重新设计 - 我对那些做了一些错误的假设......
注意：您的某些搜索词是其他搜索词的子字符串，因此您需要按词长度降序对映射表进行排序，否则您最终将替换较长词的较短部分。
例如：
N21:370/08 Prep_Peel_Peeler1DisconnectSwitchAlarm
N21:370/02 Prep_Peel_Peeler2DisconnectSwitchAlarm
N21:370/0 Prep_Peel_HalvingUnitMotorDisconnectAlarm
如果您在其他两个之前替换 N21:370/0，则不会给您想要的结果。
Private Sub CommandButton21_Click()
Dim sBuffer As String, sFileName As String
Dim sFileNameOut As String
Dim rngLookup As Range, m
Dim iFileNumIn As Integer, iFileNumOut As Integer
Dim arrMap, i As Long, numReplacements As Long
'sFileName = Application.GetSaveAsFilename() 'Path to target file
sFileName = "C:\_Stuff\test\Test - L5K.txt"
iFileNumIn = FreeFile
Open sFileName For Input As iFileNumIn
sFileNameOut = Replace(sFileName, ".txt", "_mod.txt")
iFileNumOut = FreeFile
Open sFileNameOut For Output As iFileNumOut
With Sheets("Sheet1")
Set rngLookup = .Range(.Range("D2"), .Cells(.Rows.Count, "E").End(xlUp))
arrMap = rngLookup.Value
End With
'Perform replacements
Do While Not EOF(iFileNumIn)
Line Input #iFileNumIn, sBuffer
If Len(sBuffer) > 0 Then
For i = 1 To UBound(arrMap)
If InStr(sBuffer, arrMap(i, 1)) > 0 Then
sBuffer = Replace(sBuffer, arrMap(i, 1), arrMap(i, 2))
If numReplacements <= 10 Then Debug.Print arrMap(i, 1), ">>", arrMap(i, 2)
numReplacements = numReplacements + 1
End If
Next i
End If
Print #iFileNumOut, sBuffer
Loop
Close iFileNumIn
Close iFileNumOut
Debug.Print "Made " & numReplacements & " replacements"
End Sub
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句