Any Function to pick text file name and copy it in last column of the same file

[Salman Ahmad]

I want to know is there any Macro to edit big large number of text files in the way that it pick the file name and paste it in the same file last column in repetition till last row.

 

[AlanSidman]

Pictures
Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

 

[Marc L]

I want to know is there any Macro to edit big large number of text files in the way that it pick the file name and paste it in the same file last column in repetition till last row.

• As VBA has all the necessary just reading its inner help or on Microsoft Docs website …
  
  In case you need further help :
  
  
• As there is no column in your text file Notepad++ picture (it seems to be a fixed length text file)
  post the file columns structure or at least just from which position # until last position # in order to paste the file name …
  
  
• As per forum rules attach a before text file and an expected result text file with a complete explanation of the need.

 

[Salman Ahmad]

Hi Alan,
I have attached the data file with adding fifth column which is the name of the file (ABCDEF-1994).
I HAVE 400 files I need to make all in this format.
if there is any way in excel to do this quickly then guide me little about that.

Thanks for your time,

 

[Marc L]

We need first the before text file and a complete technical explanation for all we can't guess as we are not mind readers …​

​

 

[Salman Ahmad]

Hi,
original file attached here and target was in last message.
Actually I have big files like 10000 rows.
If there is any way to make target file by using macro then it would be great otherwise any possible way to do that in excel that this file copy in excel and add one extra column at end.

 

[AlanSidman]

a complete technical explanation for all we can't guess as we are not mind readers

this request seems to be missing from your responses. As Marc has indicated, we are not mind readers. Please explain your logic to get the information required into the "After" file.

 

[Marc L]

If there is any way to make target file by using macro

As I yet wrote VBA has already all the necessary and with several ways, it's nothing but rewritting a text file,​

very not difficult, at beginner level. I will post soon a demonstration - maybe advanced - to amend this file …​

​

 

[Salman Ahmad]

Let me try to explain it, first shared file was the target and second was the source file.
I have 400 files I have to edit each file to make like target file.
like my source file has name ABCDEF-1994, I copy this, open file and paste in the last column till last row.
For few files it can be done manually in excel or Notpad++, but I have 400 files so searching any way like script or method in excel to do all files without spending much time manually.
I guess it would be easy on Linux which I don't have right now.
I am new user here, I really appreciate your effort, might be I cant get solution of it but I can learn many new things from this forum.

 

[Marc L]

If the next demonstration workbook is not saved within the text files source folder​

so amend the variable P within the code for the Path of this source folder …​

​

As all is yet explained in the VBA inner help, place the text cursor on a statement and hit F1 key then read, that's it ‼​

​

According to the attachment, on my side this Roman times demonstration well modifies it as expected :​

(Edit : code revamped for huge files, may rewrite a bit faster …)​

Sub Demo1r()
    Const E = ".txt"
      Dim F%, P$, N$, SPQ$(), T$, C&, R&
          F = FreeFile
          P = ThisWorkbook.Path & Application.PathSeparator
          N = Dir(P & "*" & E)
    While N > ""
        Open P & N For Input As #F
        SPQ = Split(Input(LOF(F), #F), vbCrLf)
        Close #F
    If UBound(SPQ) > 5 Then
            T = Replace(N, E, "")
        If Right(SPQ(6), Len(T)) <> T Then
            C = C + 1
            Open P & N For Output As #F
            For R = 0 To 5:  Print #F, SPQ(R):  Next
            For R = 6 To UBound(SPQ) - 1:  Print #F, SPQ(R); Spc(4); T:  Next
            Print #F, SPQ(R); Spc(4); T;
            Close #F
        End If
    End If
          N = Dir
    Wend
          MsgBox "Mod #  :  " & C, vbInformation, " Demo1r"
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​