Open a Text File that is named in another cell, copy and paste all in another tab

[Bobbbo]

I need some VBA code that will open a text file in a know location (path) and find that file based on a variable number found in the wordbook Tab1. Once the text file is open I want the contents copied and then pasted into A1 of another tab.

The attached samples will help you understand what I am doing. The file named: CopyPasteTextFileData.xlsx is where the VBA code will reside. I want the VBA code to use the number found in the specific cell B4 on Tab1 of the workbook. Use that number in cell B4 to open the second attached file: Output"number".txt , where the "number" is the value in Cell B4. Once the text file is found, open it, select the entire contents of the file, copy it and paste it starting at cell A1 of Tab2 of the Workbook. If the code cannot find the exact text file it just returns and error message "Cannot find Output"number".txt.

The text file is in this path: C:\Users\Bob\Documents\TextFiles

The sample text file is Output123456.txt where the "123456" is the variable found in cell B4 of the Tab1 tab.

Thanks in advance!!

[LeoTaxi]

Try this

Please Login or Register  to view this content.

Kind regards
Leo

[Bobbbo]

Try this

Please Login or Register  to view this content.

Kind regards
Leo

Thanks Leo, I will give it a try.

[Bobbbo]

Try this

Please Login or Register  to view this content.

Kind regards
Leo

Hi Leo, I am getting a question from excel when the .txt file is closed "There is a large amount of information on the clipboard. Do you want to be able to paste this information into another program later?" I need to answer the question "Yes" so I can paste the data into Tab2. Is there a way of automatically answering the question with "Yes"

[LeoTaxi]

With this change, and no emty rows or columns in txt file

Old

Please Login or Register  to view this content.

New

Please Login or Register  to view this content.

Kind regards
Leo

[Tinbendr]

Here's my effort.

Please Login or Register  to view this content.

[Bobbbo]

With this change, and no emty rows or columns in txt file

Old

Please Login or Register  to view this content.

New

Please Login or Register  to view this content.

Kind regards
Leo

Hi Leo, I got it working. What I did to get rid of the "Large Clip Board" message is since the data is limited I just select 100 lines of the data and the message goes away. Thanks again for your help.

[LeoTaxi]

Is good as long if there are now more rows then 100
if CurentRegion dont work cause of emty rows or columns you can count the rows

Please Login or Register  to view this content.

this way you can replace the 100 by lastrow

kind regards
Leo