Importing unknown number of .txt files in excel from location written in specific cell

[Anjamen]

Hie,

I need to write a macro that should look to a certain cell where would be a location of a folder with an unknown numbers of .txt files. Macro should import and separate files on columns and then paste them one under another. In the end, it should delete all the files imported from folder. Any suggestion how to write it?

 

[Chihiro]

Depends on version of Excel. If you have access to PowerQuery. I'd recommend using it, instead of VBA.

However, it is very difficult to help you without sample text files and example desired output. We are not mind reader after all.

 

[Anjamen]

Hi, thank you for your answer.

Regarding Excel, I am using version 2013. In the attachment I am sending you: one pic (screenshot 6) - the potential look of a base which should be made by macro, the second one (screenshot 4) - the look of the imported file. The last attachment is a file that should be imported by macro. I hope this is clear enough.

Thanks in advance!

 

[Chihiro]

If you have 2013... then you can download and add PowerQuery add-in (if you haven't already).
https://www.microsoft.com/en-ca/download/details.aspx?id=39379

You can then follow instruction given in link below to query folder and grab all csv files.
https://support.office.com/en-us/ar...er-query-94b8023c-2e66-4f6b-8c78-6a00041c90e4

However, since your file does not have proper header, make sure to remove both "Promoted Hearders" and "Changed" Type steps in the sample file query. This will ensure consistent data structure across all query.

There are other methods, using VBA such as FreeFile()/Open file for Input...; ADODB etc.