Use a Macro to pull all information from a txt file into a single column

**ArmyExcelProgrammer** · 03-20-2013, 01:37 PM

Hello Everyone and thank you in advance to those offering assistance.

I have a process I can’t seem to get figured out and I’m hoping someone can assist. I need to pull information from a txt file into a single column using a macro.

Setup: I have 61 units that send a report every month that I want to extract information from. I have created a folder for each month inside a file named for the report. The report from each unit for each month will be named identically: For instance, Alpha Company’s report will be named WRCRAA_AWAME130 for every month; it will just be saved in the respective month’s folder. I want to be able to create the list of units on a tab by their code (the WRCRAA characters in the example) and have the macro look to that list and then open the respective file. In the end Tab 1 will be running VLOOKUP to find information about specific items based on the serial number that is extracted from the txt files.

I’ve been able to write the macro so that it copies the entire txt file but when it pastes it into Excel it pastes into multiple columns and I need it all in the same column. If I import the text using the Data>>”From Text” process and eliminating the break lines it works but I do not know how to use this process using the list of units created on the separate tab.

I tried to attach the practice spreadsheet I am working on but I keep getting errors so I will have to forgo this. Again, thanks to anyone willing to lend a hand.