Import and export txt files automatically with correct name/subject code

Hello!

I have 12 txt files per participant for 10-110 participants. I would like to import them to Excel so I can analyse them.
It takes quite a lot of time to import them manually one-by-one. Is there any routine/program I could use to import them all at once? (they all have different subject codes/file names)

I also have the problem the other way around, I need to save Excel sheets as txt files. I recorded a macro for it which works fine. However, I have to change the subject code in the makro for every single participant. Is there a kind of placeholder that I can use to fill in subject codes automatically to save the files with the correct name?

Thanks a lot!

Hello!

Thanks for the reply!

The logic for the text files is as follows:

There are 6 different tasks ("170905 VER2", "170905 VER4", "170905 VIS2", "170905 VIS4", "170905 SOC2", "170905 SOC4")
that come each in 2 different types ("170905 VER2_1", "170905 VER2_2", "170905 VIS2_1", etc),
thus 12 different file names per subject.
The subject Code is always appended in the beginning of the file name ("PM02170905 VER2_1", "PM02170905 VER2_2", etc).

It would also be okay to get prompted for the file names.

I'd like them to be opened and filled in the rows (text file is Tab delimited).

It would be great if I could have two Excel Maps:
One in which each file is imported in a seperate sheet. (12 sheets per participant, thus 120 sheets for 10 participants)
And the other one, in which all 12 files from one subject are imported below each other in a single sheet. (1 sheet per participant, thus 10 sheets for 10 participants)

Thank you!

Thank you! The two import makros work perfectly!!! Thanks a lot!

I have a question concerning the export code though from your first reply. I tried it out and it prompts me to fill in the file name for the Excel map when saving it.
However, I have one Excel map per participant with lots of different sheets. Among them, there are 18 sheets which are named according to the name logic discussed in my last post ("170905 VER2_1", "170905 VER2_2", "170905 VIS2_1", etc.....). I want to save these 18 single Excel sheets as signle text files tab delimited with the name of the corresponding sheet plus the participant code appended.
Thus, the result is 18 text files with names such as: "170905 VER2_1_PM01.txt".
Is this possible with only typing in the participant code once for all 18 files?

Thank you!

Thanks, works great!!

Is it possible to replace the

For Each Sh In Worksheets

in the code with something like

For ActiveWorkbook.Worksheets("VERBAL_4N", "VISUAL_4N", etc...)

cause I would only like to save some worksheets of the Excel file to text files. As they are always the same for every participant, it would be great to have them included in the code.

Thank you!

great, thanks!
I am afraid there is no logic in naming that could distinguish the files I need to export and the files I don't at the moment.
I will think of that next time! Thank you very much for your fast and really helpful code!!

Hello, I have a follow-up question to this makro. The exported txt files are saved in the following format:

FileFormat:=xlTextMSDOS

But characters like ö,ä, ü can not be displayed in the exported text files. I guess it is due to the chosen format. Can I save it in other formats?
If so which are my options that would display ö,ä,ü correctly? I would have to try th different formats out as I read the text files in another program which is pretty choosy for formats...
What would be the code for that to change in the makro?

Thank you very much! The makro is great help for me!

Thanks!
Export works perfectly with xlUnicodeText, all ä s are fine.
Will still try if the other program can handle xlUnicodeText.
Thank you!!!

Hello,

the xlUnicodeText did not work with the other program, but the FileFormat:=xlCurrentPlatformText worked very well- thank you!

One last question regarding this matter:
Using a Mac book, the export does not work, the line "ActiveWorkbook.SaveAs......FileFormat:=xlCurrentPlatformText gets highlighted and the error message appears " Laufzeitfehler "1004": Zugriff auf das schreibgeschützte Dokument nicht möglich."
Do you know how to get it running with a mac book?

Thank you!

Thank you!

Hello, I have another follow-up question to the makro 'Sub ImportTXTFilesToSeparateSheets()'.

I use this makro a lot and it saved me so much time, so I d like to adapt it a bit to also use it for another study but I dont understand enough of the code to do so. Two main issues came up:

1.) The columns of the txt file are seperated by "space" this time and so if I use the makro, all columns are written in the first one and not spread out in different columns. Is there a way to adapt that in your makro? Could you tell me how, so next time my data might be seperated by comma for example I could adapt that myself- that would be great!

2.) The worksheets are named automatically accoridng to the first letters of the txt. file in your makro. However, in my current study, the initial letters of the txt files are always the same, they only distinguish with the last letters. Is there a way to adapt that in your makro? Could you tell me again how, so next time I could also name my worksheets accordingng to the 3.-6. letter of teh txt file's name for example.

Thank you yo much!!

Thank you! It Works great!
Just one question, why does it use the 3rd through 6th letters to name the sheet when you write 3, 4?
And another one: If I write StartRow=3 e.g., the txt will be importet in A3 instead of A1?
Thank you!!

Great, thanks! Everything works perfectly!