VBA code to read and update text file

[punchinello]

Dear All,
Please help me to create VBA code the concept is :-
1. press Update button
2. browse a text file (.txt) to open
3. read text

4. update (copy-replace data) to text conditions :-
4.1 select line begin "003"
4.2 count "," 10th and paste data in Sheet1:B1 to after ","
4.3 count "," 11th and paste data in Sheet1:B2 to after ","
4.4 count "," 20th and paste data in Sheet1:B3 to after ","
4.5 count "," 37th and paste data in Sheet1:B4 to after ","
4.6 count "," 28th and paste data in Sheet1:B5 to after ","

5. write complete text file to a new or same text file

I am a acc-admin need to find way resolve my problem.
It is take time to manual update.

Thank you for advance,
Kim :bow:

[shrivallabha]

Welcome to VBAX.

Your text file seems to have more than one line of data which begins with 003. How do you want to handle that?

Is it possible to upload a sample txt file so that it can be used and therefore tested directly? In fact, if you could create a sample result file it'd be lot better.

[punchinello]

Welcome to VBAX.

Your text file seems to have more than one line of data which begins with 003. How do you want to handle that?

Is it possible to upload a sample txt file so that it can be used and therefore tested directly? In fact, if you could create a sample result file it'd be lot better.

Yes, line begin 003 have many lines in 1 file. I would like to update specically 003 line on condition above.

Thank you very much for your response. :)

[punchinello]

Yes, line begin 003 have many lines in 1 file. I would like to update specically 003 line on condition above.

Thank you very much for your response. :)

Pls find an attach file for sampleFile

Thank you very much,
Kim

[shrivallabha]

It is still not clear to me.

Apparently you want to insert following text
O, CB, 7, 2, 0
at some specific locations where you have this string that starts with 003 as you have stated in post#1. Is this correct understanding?

[punchinello]

It is still not clear to me.

Apparently you want to insert following text
O, CB, 7, 2, 0
at some specific locations where you have this string that starts with 003 as you have stated in post#1. Is this correct understanding?

yes, you correct. I want to insert string "O", "CB", "7", "002" and "0000" into text file at specific position with start 003 line on my message post#1.

PS. - 001 = header line, 003,005 = details line, 100 = footer line
- delimiter for each field shall be ","

Thank you so much,
Kim :bow:

[shrivallabha]

OK. Here is my attempt.
Note: I have referred your output_CHQ.txt file. The positions you mentioned in the post#1 and the output file supplied do not match. So at this point, I have considered the expected output file as reference.

Instructions for usage:
1. Paste the code in the Commandbutton_Click() sub for the button you have shown in the screenshot. I have commented the code for your information and future edits.
2. It will prompt for the file to be processed. Select a file to process e.g. source_CHQ.txt
3. It will prompt you for the output filename. Specify New filename as we do in Save file mode.
End of the code, the file will be made with the name specified in step 3.

Dim objFSO As Object 'FileSystemObject
Dim sFileToProcess As String, sFileProcessed As String
Dim objTxt As Object 'TextStream
Dim vData As Variant, vProcess As Variant

'******************************************************************
'First line prompts user to select the file to process.
'Then the rest of the line read text file and load an Array named
'vData which is used in processing
'******************************************************************
sFileToProcess = Application.GetOpenFilename
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTxt = objFSO.OpenTextFile(sFileToProcess, ForReading, False)
vData = Split(objTxt.ReadAll, vbCrLf)

'******************************************************************
'First line prompts user to provide the output filename.
'The linebelow then creates a new file or overwrites if existing is used
'******************************************************************
sFileProcessed = Application.GetSaveAsFilename
Set objTxt = objFSO.OpenTextFile(sFileProcessed, ForWriting, True)

'******************************************************************
'Here, actual processing is done using the Array we created at step 1.
'We check the string if it starts with 003
'******************************************************************
For i = LBound(vData) To UBound(vData)
If Left(vData(i), 3) = "003" Then 'Here we test, if true
vProcess = Split(vData(i), ",") 'Then we process the line
vProcess(37) = Range("B4").Value
vProcess(38) = Range("B5").Value
vProcess(21) = Range("B3").Value
vProcess(11) = Range("B2").Value
vProcess(10) = Range("B1").Value
objTxt.WriteLine (Join(vProcess, ","))
Else 'If false then
objTxt.WriteLine (vData(i)) 'Write Data as it is
End If
Next i

[punchinello]

OK. Here is my attempt.
Note: I have referred your output_CHQ.txt file. The positions you mentioned in the post#1 and the output file supplied do not match. So at this point, I have considered the expected output file as reference.

Instructions for usage:
1. Paste the code in the Commandbutton_Click() sub for the button you have shown in the screenshot. I have commented the code for your information and future edits.
2. It will prompt for the file to be processed. Select a file to process e.g. source_CHQ.txt
3. It will prompt you for the output filename. Specify New filename as we do in Save file mode.
End of the code, the file will be made with the name specified in step 3.

Dim objFSO As Object 'FileSystemObject
Dim sFileToProcess As String, sFileProcessed As String
Dim objTxt As Object 'TextStream
Dim vData As Variant, vProcess As Variant

'******************************************************************
'First line prompts user to select the file to process.
'Then the rest of the line read text file and load an Array named
'vData which is used in processing
'******************************************************************
sFileToProcess = Application.GetOpenFilename
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTxt = objFSO.OpenTextFile(sFileToProcess, ForReading, False)
vData = Split(objTxt.ReadAll, vbCrLf)

'******************************************************************
'First line prompts user to provide the output filename.
'The linebelow then creates a new file or overwrites if existing is used
'******************************************************************
sFileProcessed = Application.GetSaveAsFilename
Set objTxt = objFSO.OpenTextFile(sFileProcessed, ForWriting, True)

'******************************************************************
'Here, actual processing is done using the Array we created at step 1.
'We check the string if it starts with 003
'******************************************************************
For i = LBound(vData) To UBound(vData)
If Left(vData(i), 3) = "003" Then 'Here we test, if true
vProcess = Split(vData(i), ",") 'Then we process the line
vProcess(37) = Range("B4").Value
vProcess(38) = Range("B5").Value
vProcess(21) = Range("B3").Value
vProcess(11) = Range("B2").Value
vProcess(10) = Range("B1").Value
objTxt.WriteLine (Join(vProcess, ","))
Else 'If false then
objTxt.WriteLine (vData(i)) 'Write Data as it is
End If
Next i


Many thanks shrivallabha!

I try to run your VBA code and found :-

Run-time error '5': Invalid procedure call or argument

How to fix it?

[Teeroy]

Try adding the following line after the Dim statements:


Const ForReading = 1, ForWriting = 2, ForAppending = 8

I'm not sure when these constants were added to excel but I've had issues before with them using excel 2003 so I usually include it.

[shrivallabha]

Oops:
1. In Visual Basic Editor,
Tools | References | Add "Microsoft Scripting Runtime"

2. Then run the code again.

[punchinello]

Try adding the following line after the Dim statements:


Const ForReading = 1, ForWriting = 2, ForAppending = 8

I'm not sure when these constants were added to excel but I've had issues before with them using excel 2003 so I usually include it.

Yeah!!! It is work! :thumb

Thanks Shrivallabha and Teeroy so much. :bow:

Cheers,:beerchug:
Kim

[shrivallabha]

Oops:
1. In Visual Basic Editor,
Tools | References | Add "Microsoft Scripting Runtime"

2. Then run the code again. You can use following code without referencing the MSR as above.
Dim objFSO As Object
Dim sFileToProcess As String, sFileProcessed As String
Dim objTxt As Object
Dim vData As Variant, vProcess As Variant

'******************************************************************
'First line prompts user to select the file to process.
'Then the rest of the line read text file and load an Array named
'vData which is used in processing
'******************************************************************
sFileToProcess = Application.GetOpenFilename
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTxt = objFSO.OpenTextFile(sFileToProcess, 1, False)
vData = Split(objTxt.ReadAll, vbCrLf)

'******************************************************************
'First line prompts user to provide the output filename.
'The linebelow then creates a new file or overwrites if existing is used
'******************************************************************
sFileProcessed = Application.GetSaveAsFilename
Set objTxt = objFSO.OpenTextFile(sFileProcessed, 2, True)

'******************************************************************
'Here, actual processing is done using the Array we created at step 1.
'We check the string if it starts with 003
'******************************************************************
For i = LBound(vData) To UBound(vData)
If Left(vData(i), 3) = "003" Then 'Here we test, if true
vProcess = Split(vData(i), ",") 'Then we process the line
vProcess(37) = Range("B4").Value
vProcess(38) = Range("B5").Value
vProcess(21) = Range("B3").Value
vProcess(11) = Range("B2").Value
vProcess(10) = Range("B1").Value
objTxt.WriteLine (Join(vProcess, ","))
Else 'If false then
objTxt.WriteLine (vData(i)) 'Write Data as it is
End If
Next i
The problem was that:
1. While working on the code, I had used early binding as it makes coding easier.

2. Then I forgot to remove the references. I changed the FSO and TextStream to objects and ran it again. The code ran fine as I had references set. So there was no runtime error in my case.

3. I did not know of passing arguments as numeric constant would be critical when I changed style from Early binding to Late binding.

[Teeroy]

Oops:
1. In Visual Basic Editor,
Tools | References | Add "Microsoft Scripting Runtime"

2. Then run the code again.
Thanks Shrivallabha :thumb. This will add the constants (among other things). It also explains why the errors I got previously were not consistent across machines.

When something works I'll use it but it bugs me if I don't know why I'm using it.:think:

[punchinello]

You can use following code without referencing the MSR as above.
Dim objFSO As Object
Dim sFileToProcess As String, sFileProcessed As String
Dim objTxt As Object
Dim vData As Variant, vProcess As Variant

'******************************************************************
'First line prompts user to select the file to process.
'Then the rest of the line read text file and load an Array named
'vData which is used in processing
'******************************************************************
sFileToProcess = Application.GetOpenFilename
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTxt = objFSO.OpenTextFile(sFileToProcess, 1, False)
vData = Split(objTxt.ReadAll, vbCrLf)

'******************************************************************
'First line prompts user to provide the output filename.
'The linebelow then creates a new file or overwrites if existing is used
'******************************************************************
sFileProcessed = Application.GetSaveAsFilename
Set objTxt = objFSO.OpenTextFile(sFileProcessed, 2, True)

'******************************************************************
'Here, actual processing is done using the Array we created at step 1.
'We check the string if it starts with 003
'******************************************************************
For i = LBound(vData) To UBound(vData)
If Left(vData(i), 3) = "003" Then 'Here we test, if true
vProcess = Split(vData(i), ",") 'Then we process the line
vProcess(37) = Range("B4").Value
vProcess(38) = Range("B5").Value
vProcess(21) = Range("B3").Value
vProcess(11) = Range("B2").Value
vProcess(10) = Range("B1").Value
objTxt.WriteLine (Join(vProcess, ","))
Else 'If false then
objTxt.WriteLine (vData(i)) 'Write Data as it is
End If
Next i
The problem was that:
1. While working on the code, I had used early binding as it makes coding easier.

2. Then I forgot to remove the references. I changed the FSO and TextStream to objects and ran it again. The code ran fine as I had references set. So there was no runtime error in my case.

3. I did not know of passing arguments as numeric constant would be critical when I changed style from Early binding to Late binding.

I already run your code without referencing MSR. It's work too! :thumb
Thanks again. :clap2:

[shrivallabha]

Thanks Shrivallabha :thumb. This will add the constants (among other things). It also explains why the errors I got previously were not consistent across machines.

When something works I'll use it but it bugs me if I don't know why I'm using it.:think:
Hi Teeroy,

Actually, had it not been this error which Kim let us know about, I wouldn't have checked. I tried to recreate the error and that is when I understood about this nuance.

Your solution is also good as the code becomes good for reading. Reading and understanding numeric constants if it is not your code is probably suitable for a programmer but not for us mortals.

------------------------------------------------------------------------

Hi Kim,
If the code works as intended then please mark it as solved.

[punchinello]

Hi Teeroy,

Actually, had it not been this error which Kim let us know about, I wouldn't have checked. I tried to recreate the error and that is when I understood about this nuance.

Your solution is also good as the code becomes good for reading. Reading and understanding numeric constants if it is not your code is probably suitable for a programmer but not for us mortals.

------------------------------------------------------------------------

Hi Kim,
If the code works as intended then please mark it as solved.

Hi shrivallabha,

After I have try to modify your code to add more delete output file (sFileProcessed) statments. I found that cannot delete because permission denied. I guess, I does not close output file. (right?) How to close and delete output file in variable: sFileProcessed.

Thank you very much,
Kim :content:

[Teeroy]

To close the object just use:

objFSO.Close

To delete the file use:
objFSO.DeleteFile(sFileProccessed),False

The file must be closed before you try to delete it.

[punchinello]

To close the object just use:

objFSO.Close

To delete the file use:
objFSO.DeleteFile(sFileProccessed),False

The file must be closed before you try to delete it.

Thanks Teeroy,

I try your statments but found "Run-time error '438' Object doesn't support this property or method" :think: How to fix it?

[shrivallabha]

Hi shrivallabha,

After I have try to modify your code to add more delete output file (sFileProcessed) statments. I found that cannot delete because permission denied. I guess, I does not close output file. (right?) How to close and delete output file in variable: sFileProcessed.

Thank you very much,
Kim :content:
Hi Kim,

Why do you want to delete file which will have your desired output. To delete the file you can try:
Kill sFileProcessed

The code is revised to close Textstream and releasing the objects.
Dim objFSO As Object
Dim sFileToProcess As String, sFileProcessed As String
Dim objTxt As Object
Dim vData As Variant, vProcess As Variant

'******************************************************************
'First line prompts user to select the file to process.
'Then the rest of the line read text file and load an Array named
'vData which is used in processing
'******************************************************************
sFileToProcess = Application.GetOpenFilename
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTxt = objFSO.OpenTextFile(sFileToProcess, 1, False)
vData = Split(objTxt.ReadAll, vbCrLf)
objTxt.Close 'TextStream Closed Here

'******************************************************************
'First line prompts user to provide the output filename.
'The linebelow then creates a new file or overwrites if existing is used
'******************************************************************
sFileProcessed = Application.GetSaveAsFilename
Set objTxt = objFSO.OpenTextFile(sFileProcessed, 2, True)

'******************************************************************
'Here, actual processing is done using the Array we created at step 1.
'We check the string if it starts with 003
'******************************************************************
For i = LBound(vData) To UBound(vData)
If Left(vData(i), 3) = "003" Then 'Here we test, if true
vProcess = Split(vData(i), ",") 'Then we process the line
vProcess(37) = Range("B4").Value
vProcess(38) = Range("B5").Value
vProcess(21) = Range("B3").Value
vProcess(11) = Range("B2").Value
vProcess(10) = Range("B1").Value
objTxt.WriteLine (Join(vProcess, ","))
Else 'If false then
objTxt.WriteLine (vData(i)) 'Write Data as it is
End If
Next i
objTxt.Close 'TextStream Closed Here

Set objFSO = Nothing
Set objTxt = Nothing