How can I change the texts in text file?

[Dreamer]

Hi guys,

Please kindly help me to solve this problem, many thanks!!!

I wrote a simple macro to export excel texts into text files, and it works okay..

The outline of the text file is very simple, e.g.
CM100009 \ Age 15-24 \ 1
CM10000A \ Age 25-34 \ 2
CM10000B \ Age 35-44 \ 3
CM10000C \ Age 45-54 \ 4
CM10000D \ Age 55-64 \ 5

CM10000D-- 1st Part
\ Age 55-64 \ 5-- 2nd Part

Here comes my problem... I have no idea how to edit the texts in text files by VBA, such as I find the statement "CM10000D \ Age 55-64 \ 5" is wrong, I should correct it to "CM10000D \ Age 55-64 \ 6". How can I use a keyword, such as 1st Part "CM10000D", to correct the 2nd part "\ Age 55-64 \ 5" ?

Regards,
Dreamer

[stanl]

I would think you would want to fix errors in Excel prior to exporting to text file, or am I missing something?
Stan

[OBP]

If you use a "Save as" text or CSV file instead of exporting the data, you can still open the file in Excel and manipulate it with VBA.

[Dreamer]

Hi, my current procedure is fix the errors in excel first, then use the macro to export the whole piece (around 50000 lines) into a new text file. But actually I may need to correct one or two wordings only.. so I am not sure whether I can use VBA to edit the text file directly instead of generating a new text file. Please advise

[OBP]

In actual fact if you use the Excel Save as method you can just make the corrections using Excel.

[stanl]

Hi, my current procedure is fix the errors in excel first, then use the macro to export the whole piece (around 50000 lines) into a new text file. But actually I may need to correct one or two wordings only..

I'm really confused. You fix the errors, then you export as text, but then there are more errors to be fixed Probably better if you indicate (1) at what point and (2) under what conditions the data needs to be changed. Or better yet, post an example with say 50 rows and 1-2 errors. Also, are you using delimiters other than , in your text file. Stan

[Norie]

Like Stan I'm confused as to what you are actually doing/trying to do.

You could use VBA to read each line of the text file using it's file I/O functions.

Then you could use various text functions to manipulate it.

Then you could write to a new file.

But with 50000 records/rows that could take some time, and the manipulation could be complicated.

[Dreamer]

Hi, sorry for my poor expression... As title, actually I want to know some hints to edit text file by VBA. e.g If I search the keyword "CM10000D" in text file, how can I change the whole string from CM10000D \ Age 55-64 \ 5 to CM10000D \ Age 55-64 \ 6 ? I know I can do it if I open the text file and correct it directly... but I wonder if I can do it by VBA?

Thanks a lot!

[geekgirlau]

Hi Dreamer,

The best approach would be for your macro to fix the errors before you export to a text file - will this work for you? If so, please attach a sample of the Excel file before the export, and containing your macro code.

[Norie]

Dreamer

Have you read any of the responses to your post?

[stanl]

I know I can do it if I open the text file and correct it directly... but I wonder if I can do it by VBA?

O.K. based on things I actually had to do with telecom EMI files [usually involving inserting terminating CIC's for 800 calls, or converting 'ticks' to clock time for older switches]. (1) Copy and Paste your range into Word as text, use find/replace VBA code [there are umpteen samples around], then export to file; (2) If your data in Excel can be handled as a Recordset, use Jet 4.0, open it, perform n number of SQL Updates then use getstring() to write to text file.

But to see how either of these might apply, or obtain additional solutions from other contributors, you should review this thread and oblige those who have asked you to post some code.

[Off-Topic Postscript] - it is an interesting historical note in Telecom, how earlier long-distance billing was in 6-second increments - as most switch AMA layouts had clock time (as ticks) as an Integer Value. The solution to 1-second increments and my introduction into real binary file processing was to set the high-bit of the previous byte. Stan