Help with reformatting string in text files

[HotLanta]

Hi,

I have a macro that loops through a folder containing text files. The macro adds information in the Excel worksheet to each text file in the folder.

I want to add something to the macro that does the following:
In each text file there is a string of information which is the date. The machine that will read this information cannot read it as formatted by the machine that outputs the information.
I would like to have a function that searches the text file for the string that looks like this:

$$ Date: 3/17/2022

and then reformats it somehow to:

Date= 2022/03/17

See the difference? In the second string I have removed the $$, changed the colon to and equals sign and then rearranged the date from m/dd/yyyy to yyyy/mm/dd

Thank you for any help you can give.

 

[Micron]

left(mid("$$ Date: 3/17/2022",4),4) & "= " & Format(#3/17/2022#,"yyyy/mm/dd")

If str was a date, then perhaps like

left(mid(str,4),4) & "= " & Format(str,"yyyy/mm/dd")

 

[HotLanta]

Hi Micron,

Would you help me with a little more information to get this code into my macro? I'm not savvy enough to just work with this line.

Thank you

 

[Micron]

Maybe can do - if you post your code, otherwise I have no clue what you've got. Please use code tags ( see vba button on posting toolbar) and maintain proper indentation to make it easier to read/follow.

 

[HotLanta]

Sub txt_Header()

  Dim textline
  Dim myPath As String, myFile As String
  Dim i As Long, n As Long
  Dim c As Range
  Dim ntime As Double
  Dim Str As String

  
  With Application.FileDialog(msoFileDialogFolderPicker)
    .Title = "Select Folder"
    .AllowMultiSelect = False
    If .Show <> -1 Then Exit Sub
    myPath = .SelectedItems(1) & "\"
  End With
  
  myFile = Dir(myPath & "*.txt")
  ntime = TimeValue(Hour(Now) & ":00:00")
  
  Str = Replace("FILNAM/          ", "F", "$$ F")
  
  Do While myFile <> ""
    On Error Resume Next: Kill myPath & "tmp.txt": On Error GoTo 0
    Open myPath & myFile For Input As #1
    Open myPath & "tmp.txt" For Output As #2
    n = 0
    While Not EOF(1)
      n = n + 1
      Line Input #1, textline
      If n = 1 Then
        For Each c In Range("H2:H13")
          Print #2, c.Value
        Next
      Else
        If InStr(1, textline, "Time=", vbTextCompare) > 0 Then
          ntime = ntime + TimeValue("00:00:01")
          Print #2, "Time=" & Format(ntime, " HH:MM:SS")
        Else
          Print #2, textline
        End If
      End If
    Wend
    Close #1
    Close #2
    Kill myPath & myFile
    Name myPath & "tmp.txt" As myPath & myFile
    myFile = Dir()
  Loop
  
End Sub

 

[Micron]

AFAIK you cannot simply open, edit, save and close a text file using these scripting methods. The only approach I know is to open a file, get its contents into a string variable, edit as desired, close file, reopen and paste, thus overwriting the original contents. WARNING - you should back up folders/files when you attempt to use any new or edited code that does things like this. Here is the portion of the code that should do the above.

 Do While myFile <> ""
    On Error Resume Next: Kill myPath & "tmp.txt": On Error GoTo 0
    Open myPath & myFile For Input As #1 'open file to get its contents
    strInput = Input(LOF(1), 1) 'entire file contents into string variable
    strOutput = Left(Mid("$$ Date: 3/17/2022", 4), 4) & "= " & Format(#3/17/2022#, "yyyy/mm/dd") 'remove$$ and reformat date
    Close #1 'close so that we can edit
    Open myPath & myFile For Output As #1 'open for writing to file
    Print #1, strOutput 'over-write file with new string
    Close #1 'close to save
    Open myPath & myFile For Input As #1 'reopen file and process as before
    Open myPath & "tmp.txt" For Output As #2
    n = 0

FWIW, I don't believe the best practice is to assign the file numbers like that, especially when opening/closing/reopening with 2 or more files at the same time. I'd probably use FreeFile and let that function assign the file numbers to variables.