need to help: mid function

[supergirl]

Hello, I have text file each field separate by ";". And I would like to
edit/move some field before upload to our system there are :-

1). move text after the first ";" to after the 2nd ";" but,
not over 50 chars.
2). if 1) length is over 50 chars move char#51 to xxx to 2nd ";"
3) move text after the 2nd ";" to after the 3rd; but, not over 50 chars.

for example
AAA company;123;abc;;10100;x;;;;1111111111;22222222;;
AAA company;;123;abc;10100;x;;;;1111111111;22222222;;

I try to develope code as below but, text some record is invalid such as
original text :-
JJJ Co., Ltd;2,4 Soi Lasal 1, Bangna,Bangna,;;;Bangkok 10260;R : Consumer Protection Sticker;;;;0105536051511;027445061;;

result text :-
JJJ Co., Ltd;;2,4 Soi Lasal 1, Bangna,Bangna,angna,2,4 Soi Lasal;angna,2,4 Soi Lasal 1, Bangna,Bangna,Bangkok 10260;2,4 Soi Lasal 1, Bangna,Bangna,Bangkok 10260;R : Consumer Protection Sticker;;;;0105536051511;027445061;;

*** seem like text after the 1st ";" is <> 50 then mid function get the first
character again til xxxx char to equal 50 chars.

For i = LBound(vData) To UBound(vData)
vChar = Split(vData(i), ";")
address = vChar(1) & vChar(2) & vChar(3)
vChar(2) = Left(address, 50) 'Address 2
vChar(3) = Left(Mid(address, 51), 50) 'Address 3
vChar(4) = vChar(4) ' Address4
vChar(1) = "" 'Address 1
objTxt.writeline (Join(vChar, "~"))


Please you suggest me how to solve it. Thank you in advances. :)

[p45cal]

try a variant of:Sub blah()
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.OpenTextFile("C:\Documents and Settings\Pascal\My Documents\ex_file\test_C.txt", 1, TristateFalse)
Set objtxt = fs.CreateTextFile("C:\Documents and Settings\Pascal\My Documents\ex_file\result_Cpd01.txt", True)
Dim vData(), vResult()
ReDim vData(1 To 1)
i = 1
Do While f.AtEndOfStream <> True
ReDim Preserve vData(1 To i)
vData(i) = f.readline
i = i + 1
Loop
For i = LBound(vData) To UBound(vData)
vChar = Split(vData(i), ";")
ReDim vResult(LBound(vChar) To UBound(vChar))
Address = vChar(0) & vChar(1) & vChar(2)
For j = 1 To Len(Address) \ 50 + 1
vResult(j) = Mid(Address, (j - 1) * 50 + 1, 50) 'Address 2, and 3 and 4 etc...
Next j
For k = j To UBound(vChar)
vResult(k) = vChar(k)
Next k
objtxt.writeline (Join(vResult, "~"))
Next i
f.Close
objtxt.Close
End Sub
Things are probably in the wrong place but it may give you ideas.

[supergirl]

Thank you very much. I can fix it! :thumb


try a variant of:Sub blah()
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.OpenTextFile("C:\Documents and Settings\Pascal\My Documents\ex_file\test_C.txt", 1, TristateFalse)
Set objtxt = fs.CreateTextFile("C:\Documents and Settings\Pascal\My Documents\ex_file\result_Cpd01.txt", True)
Dim vData(), vResult()
ReDim vData(1 To 1)
i = 1
Do While f.AtEndOfStream <> True
ReDim Preserve vData(1 To i)
vData(i) = f.readline
i = i + 1
Loop
For i = LBound(vData) To UBound(vData)
vChar = Split(vData(i), ";")
ReDim vResult(LBound(vChar) To UBound(vChar))
Address = vChar(0) & vChar(1) & vChar(2)
For j = 1 To Len(Address) \ 50 + 1
vResult(j) = Mid(Address, (j - 1) * 50 + 1, 50) 'Address 2, and 3 and 4 etc...
Next j
For k = j To UBound(vChar)
vResult(k) = vChar(k)
Next k
objtxt.writeline (Join(vResult, "~"))
Next i
f.Close
objtxt.Close
End Sub
Things are probably in the wrong place but it may give you ideas.