VBA: Copy files in excel, edit and save

[vbadumb]

Copy .txt files in worksheet with FILENAME (Column B) and FILE DATA (Column C) in the respective cell from a given location(Column A) from folder "Sales_2019"

Delete lines with given word Sky, Bird, Tree if found in cells

Then do reverse i.e save the file with FILENAME (Column B) and FILE DATA (Column C) in the respective cell in given location(Column A)

[DocAElstein]

Hello vbadumb.

I do not understand you.

_1)
…. Copy .txt files in worksheet with FILENAME (Column B) and FILE DATA (Column C) …. What does this mean?

[vbadumb]

_1)
…. Copy .txt files in worksheet with FILENAME (Column B) and FILE DATA (Column C) …. What does this mean? – Do you want to copy contents of a text file to a column in an Excel worksheet??

Ans: Yes

_2)
…. respective cell from a given location(Column A) from folder "Sales_2019" … This is very confusing to me. I cannot understand what does it mean?

Ans: same row.

[DocAElstein]

Hello vbadumb.


_1) To copy contents of a text file to a column in an Excel worksheet
There are many ways to do that. I am not familiar with them all. Some ways may be better different text files.
I can show one way that I am familiar with.
In Brief how it works:
It gets an entire text file as a single long text string. ( '2a)(i) )
This string is placed in the windows clipboard ( Rem 3 )
That string is pasted into a worksheet. ( Rem 4 )



See attached text file, " vbadumb.txt"

Inside that text file is 3 lines of data. There is one piece of data in each line, 1 in first line, 2 in second line, and 3 in the third and last line :

1
2
3
vbatext.JPG : https://imgur.com/WKNmChc
2571


Coding description
The macro here, http://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11810&viewfull=1#post11810 ,gets the entire text file as a single long text string. '2a)(i)
If we were to examine that string, strtxtFile , then we would see something like
"1" & vbCr & vbLf & "2" & vbCr & vbLf & "3"

Rem 3 We can put the string, strtxtFile , into the clipboard. It is somewhat a mystery to most people how all the Microsoft clipboards ( Office Clipboard, Excel Clipboard, Windows Clipboard ) work, but with some practice , we can usually find a way to put a string into the ( Windows ) clipboard, such that the Worksheets .Paste Method used within Excel will recognise that text as a text data range. In our simple example it is relatively easy, since the vbCr & vbLf is recognised by Excel as the row separator. Hence our data would be .Pasted across 3 lines.

Rem 4 Paste out to the worksheet
If we have done no other interactions with clipboards from Excel, then it appears that a default link will look for data in the Windows Clipboard.
If we paste the entire string in / at B1 , then the vbCr & vbLf in the string will be interpreted by Excel as the row separator, so the entire string will be split over 3 lines.
_____ Workbook: vbadumb.xls ( Using Excel 2007 32 bit )
Row\Col
A
B
C

1
1


2
2


3
3


4
Worksheet: FILENAME



The macro here http://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11810&viewfull=1#post11810 , is also in the attached Excel file, " vbadumb.xls "








To run a demo , Do this
Save both files, " vbadumb.txt" and " vbadumb.xls" , in the same Folder
Run macro,
Sub txtfilesinworksheetwithFILENAMEColumnB()

You should then see this in Column B of vbadumb.xls
vbadumb xls AFTER.JPG : https://imgur.com/6T951BP
2572





_2) I still do not understand what else you want.

Your explanations are too brief for me to understand. I need full and detailed explanation of all what you want. I am unable to help further without a more detailed explanation of what you want.
I am away for a few days. If you are able to give a more full explanation, possibly with examples then I will look in again in a few days , if you still then require help

Otherwise, good luck

Alan.