File Read/Write Replace 1

[deltaair]

I need to write a standalone program or a function/macro built in excel or word to do some string replacement in a very wierd text file.

This file looks something like this....
HRBATL22DEC0500-0659$EV¥L<enter>HRBATL23DEC0500-0659$EV¥L....(unicode text characters). It is a series of dates and times that are used to pull numbers in an internal program at out company. These numbers have to be retreived for 7 days in advance every week.

The problem is, this internal program, cannot automate the date part of this entry. Hence a user has to find and replace the dates every time for every week. This file is in the format abcdef.KY1 (made for our internal program). It reads in notepad as the correct characters. Any changes made in notepad still preserve the proper format, but any changes in word or other software doesn't work.

I need someway in Excel or a standalone program / function / macro -> to read in this file wherever a string is given, and then replace it with a string and write back to it, without actually read/rewrit-ing any of the wierd unicode text characters (like the ¥ sign). If any of those characters are rewritten the file loses its program format.

I am not too good with file handling. If anybody can provide sample / guide code and guidelines to build from - I'd appreciate it.

 

[PHV]

Have a look here:

http://msdn.microsoft.com/library/en-us/script56/html/af4423b2-4ee8-41d6-a704-49926cd4d2e8.asp

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[CautionMP]

Two questions:[ol][li]Is the user using the 'Replace...' functionality in Notepad?[/li]

a user has to find and replace the dates every time for every week

[li]How big is the text file?[/li][/ol]

Not sure if this works, I only skimmed the book that came with the software.

 

[deltaair]

yes we've been using notepad - find/replace functionality in notepad. anyway to automate the program or make macro.

 

[PHV]

When you'll be confident with the FileSystemObject, have a look at the Replace function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[CautionMP]

PHV beat me to the punch, hence the reason for the second question.

If the file is not too long/large you can load the file into a variable and do a quick [tt]Replace()[/tt] (without using the FSO), if not you can loop through the file and do [tt]Replace()[/tt] line by line (still without using the FSO).

Either way you then dump the updated data back to the file (did I mention that you won't need the FSO to do this?) without altering any of the other data/characters.

How big is the text file?

Not sure if this works, I only skimmed the book that came with the software.

 

[CautionMP]

deltaair,
Never mind on the file length, here is the routine that will update the file one line (record) at a time.

Const strFileName = "C:\thread707-1169101.txt"

'This is needed because the file is opened for Random Access
Type InternalFileRec
  '25 characters plus 2 (type data)
  Field1 As String * 27
End Type

Public Sub UpdateFile()
Dim intFile As Integer, intRecord As Integer
'Container needed for Random file access
Dim InternalRecord As InternalFileRec
Dim dteNew As Date
Dim strDate As String
intFile = FreeFile
Open strFileName For Random As intFile Len = Len(InternalRecord)
'The file is now open, loop through it
Do While Not EOF(intFile)
  'Pointer to keep track of Input/Output operations
  intRecord = intRecord + 1
  'Read the record
  Get #intFile, intRecord, InternalRecord
  'Normalize the date format so it can be advanced 7 days
  strDate = Mid(InternalRecord.Field1, 7, 2) & "-"
  strDate = strDate & Mid(InternalRecord.Field1, 9, 3) & "-"
  strDate = strDate & Mid(InternalRecord.Field1, 12, 2)
  'Convert the string to a date and add 7 days
  dteNew = CDate(strDate) + 7
  'Perform the text replacement
  InternalRecord.Field1 = Replace(InternalRecord.Field1, _
  Mid(InternalRecord.Field1, 7, 7), Format(dteNew, "ddmmmyy"))
  'Write the updated record back to the file
  Put #intFile, intRecord, InternalRecord
Loop
'Close the file
Close intFile
End Sub

Written/tested (sort of) in Excel 2K.

Hope this helps,
CMP

Not sure if this works, I only skimmed the book that came with the software.

 

[deltaair]

Wow,
Thank you so much!

Just to make sure - the file is around 3KB (in windows explorer atleast).

Also, I understand that this would replace the stuff line by line, however, the text i need to replace is not really linear, so I could potentially run into the problem that the date, say 25DEC could be split into two lines. If my concept of lines is not accurate please let me know.

Thanks again.

 

[CautionMP]

deltaair,
The routine cycles through the file 25 characters at a time (a record), so it should still work.

If you run into problems post a larger portion on the file as it appears in notepad. Please take the time to 'dummy' up the data to protect the innocent.

CMP

Not sure if this works, I only skimmed the book that came with the software.

 

[deltaair]

I tested the code given by Caution MP and I made the supporting functions and subs, however, I ran into the problem that I predicted: the string to be replaced, if found at the divide of two records, doesn't work. Anyway to make it cycle until all of the occurences of the string to be replaced have been replaced correctly ?

 

[CautionMP]

If you run into problems post a larger portion on the file as it appears in notepad. Please take the time to 'dummy' up the data to protect the innocent.

Not sure if this works, I only skimmed the book that came with the software.