Você pode tornar o código mais eficiente, sustentável e um pouco mais dinâmico
As duas versões abaixo determinam o tamanho dos dados com base na localização do próximo token ( "Phone" )
em relação ao token atual ( "Name" )
.
A versão 1 usa matrizes para mapear tokens para células diferentes em Sheet5
Option Explicit
Private Sub CommandButton1_Click()
Const FULL_PATH = "C:\Users\test1.txt"
Const TOKENS = "Name Phone Address1 Email Postcode SR MTM Serial Problem Action Dated"
Const LOCATIONS = "C11 H13 C15 C13 H16 C10 H14 H15 C17 C18 H10"
Dim fId As String, txt As String, txtLen As Long, idArr As Variant, locArr As Variant
fId = FreeFile
Open FULL_PATH For Input As fId
txt = Input(LOF(fId), fId) 'Read entire file (not line-by-line)
Close fId
txtLen = Len(txt)
idArr = Split(TOKENS)
locArr = Split(LOCATIONS)
Dim i As Long, k As String, sz As Long, found As Long, ub As Long
ub = UBound(idArr)
With ThisWorkbook.Worksheets("Sheet5") '<--- Update sheet name
For i = LBound(idArr) To ub
k = idArr(i) 'Name, Phone, etc
found = InStr(txt, k) + Len(k) + 1 'Find current key in file
If found > 0 Then 'Determine item length by finding the next key
If i < ub Then sz = InStr(txt, idArr(i + 1)) Else sz = txtLen + 2
.Range(locArr(i)).Value2 = Trim$(Mid$(txt, found, sz - found - 1))
End If
Next
End With
End Sub
.
Versão 2 usa um dicionário
Private Sub CommandButton1_Click()
Const FULL_PATH = "C:\Users\test2.txt"
Dim fId As String, txt As String, txtLen As Long, d As Object, dc As Long
fId = FreeFile
Open FULL_PATH For Input As fId
txt = Input(LOF(fId), fId) 'Read entire file (not line-by-line)
Close fId
txtLen = Len(txt)
Set d = CreateObject("Scripting.Dictionary")
d("Name") = "C11" 'Same as: d.Add Key:="Name", Item:="C11"
d("Phone") = "H13"
d("Address1") = "C15"
d("Email") = "C13"
d("Postcode") = "H16"
d("SR") = "C10"
d("MTM") = "H14"
d("Serial") = "H15"
d("Problem") = "C17"
d("Action") = "C18"
d("Dated") = "H10"
dc = d.Count
Dim i As Long, k As String, sz As Long, found As Long
With ThisWorkbook.Worksheets("Sheet5") '<--- Update sheet name
For i = 0 To dc - 1 'd.Keys()(i) is a 0-based array
k = d.Keys()(i) 'Name, Phone, etc
found = InStr(txt, k) + Len(k) + 1 'Find the (first) key in file
If found > 0 Then 'Determine item length by finding the next key
If i < dc - 1 Then sz = InStr(txt, d.Keys()(i + 1)) Else sz = txtLen + 2
.Range(d(k)).Value2 = Trim$(Mid$(txt, found, sz - found - 1))
End If
Next
End With
End Sub
.
test1.txt
Name Name1
Phone Phone1
Address1 Address11
Email Email1
Postcode Postcode1
SR SR1
MTM MTM1
Serial Serial1
Problem Problem1
Action Action1
Dated Dated1
Resultado 1 :
.
test2.txt
NameName2PhonePhone2Address1Address12EmailEmail2PostcodePostcode2SRSR2MTMMTM2SerialSerial2ProblemProblem2ActionAction2DatedDated2Resultado2: