Add text to every text file in a folder

[HotLanta]

Hi,

I want to take a macro that I have "to the next level". We have some measurement equipment that exports data collected as a .txt file into a folder. When the next test is performed, a second text file is placed in the same folder.

The problem is, the text files just contain the test data. What I want to do is have information in my spreadsheet starting in cell C5 down to cell C13 copied and pasted into each text file in the folder.

Specifically I would like for it to work like this:

Open text file
Delete the very first line of the text file
Copy cell C5 in the very first line
Copy cell C6 in the next line
Continue to cell C13
Loop through all of the *txt files in the folder

The delete the very first line in the text file is important...

FYI The cells in the spreadsheet - I'm want to have the part name, type of test, name of the tester, etc.
The idea is when those parameters change I just update the spreadsheet, run the macro, and I have updated text files.

Thank you in advance for your help

 

[DanteAmor]

Open text file
Delete the very first line of the text file
Copy cell C5 in the very first line
Copy cell C6 in the next line
Continue to cell C13
Loop through all of the *txt files in the folder

In a folder with some test files, run the following macro. If it's what you need, then back up your files to another folder and run the macro.
The response time depends on the number of files and the number of lines each file has.

Try this:

Sub update_txt()
  Dim textline
  Dim myPath As String, myFile As String
  Dim i As Long, n As Long
  Dim c As Range
 
  With Application.FileDialog(msoFileDialogFolderPicker)
    .Title = "Select Folder"
    .AllowMultiSelect = False
    If .Show <> -1 Then Exit Sub
    myPath = .SelectedItems(1) & "\"
  End With
 
  myFile = Dir(myPath & "*.txt")
 
  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("C5:C13")
          Print #2, c.Value
        Next
      Else
        Print #2, textline
      End If
    Wend
    Close #1
    Close #2
    Kill myPath & myFile
    Name myPath & "tmp.txt" As myPath & myFile
    myFile = Dir()
  Loop
End Sub

 

[HotLanta]

DanteAmor - this is very nice. Thank you. I may come back with a few questions. Have a nice weekend.

 

[HotLanta]

DanteAmor - I have been working with this for a few days and it is working very nicely Thank you.

Another hurdle I have with this project is the time stamp in the text files. The software that receives the text files must have the time in HH:MM:SS format.

This is an example of what is currently in the text files:

TIME= 09:25 AM

Is it possible to search each text file for the line containing "TIME=" and replace that line with the excel function =NOW() in HH:MM:SS format?

The receiving software uses the time as a serial number to distinguish between datasets, so this is why I need SS.

Thank you.

 

[DanteAmor]

Is it possible to search each text file for the line containing "TIME=" and replace that line with the excel function =NOW() in HH:MM:SS format?

Try this:

Sub update_txt()
  Dim textline
  Dim myPath As String, myFile As String
  Dim i As Long, n As Long
  Dim c As Range
  
  With Application.FileDialog(msoFileDialogFolderPicker)
    .Title = "Select Folder"
    .AllowMultiSelect = False
    If .Show <> -1 Then Exit Sub
    myPath = .SelectedItems(1) & "\"
  End With
  
  myFile = Dir(myPath & "*.txt")
  
  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("C5:C13")
          Print #2, c.Value
        Next
      Else
        If InStr(1, textline, "Time=", vbTextCompare) > 0 Then
          Print #2, "Time=" & Format(Now(), "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

 

[HotLanta]

DanteAmor - I am very happy with the outcome of this. I wasn't sure how the hurdle about the time could be accomplished.
The only thing about it... I ran it on some sample files and noticed that I had multiple instances of Time= 15:51:39 (just for example (I had several Time= 15:51:40 and so forth)).

I hate to say that the macro runs too fast - of course we want faster but in this case I need each timestamp to be unique because the incoming software uses the timestamp as a serial number.

What would happen with a second file with Time= 15:51:39 - the software would over write the first file with the second.

Is it possible to slow the code down? Can it loop once per millisecond or by some other method?

Thank you

 

[DanteAmor]

I hate to say that the macro runs too fast - of course we want faster but in this case I need each timestamp to be unique because the incoming software uses the timestamp as a serial number.
Is it possible to slow the code down? Can it loop once per millisecond or by some other method?

Hahaha. It seems like a joke but it's real. I have had some cases where it is necessary to wait, because the database does not respond as fast as the process, so we must wait for a data to continue.

But in this case, let's leave the speed of the macro. I propose the following: create a unique timestamp for each file. The first file at 15:00:01, with a counter, the second file at 15:00:02, the third at 15:00:03, if you have 60 files, for example, then file 60 would be 15:01:00 and so on.

What do you think?

 

[HotLanta]

DanteAmor - That sounds fantastic. There are certain occasions where I would expect more than 60 files so yes - perfect.

 

[DanteAmor]

The first file at 15:00:01, with a counter, the second file at 15:00:02, the third at 15:00:03, if you have 60 files, for example, then file 60 would be 15:01:00 and so on.

Try this:

Sub update_txt()
  Dim textline
  Dim myPath As String, myFile As String
  Dim i As Long, n As Long
  Dim c As Range
  Dim ntime As Double
  
  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")
  
  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("C5:C13")
          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

 

[HotLanta]

DanteAmor - I have tried this on various output files and it is working great.

I did encounter a situation that I hadn't thought of.
Is it possible before the Excel information is copied into the text file for the code to look for a line in the text file that begins with "FILNAM/", if the line that begins with "FILNAM/" exists can it add "$$ " to the line. The result would look like:

$$ FILENAM/ (and then the rest of the line which is the name of the file)

The reason for this is originally I had asked for the first line to be deleted. That first line is 99% of the time always the FILENAM/ line. I discovered that sometimes technicians add comments in the text file before the FILENAM/ line. The macro is adding a second FILENAME/ line in the first row. The software that receives these text files can't handle two FILENAME/ lines...

The input software ignores lines that begin with $$