how to convert multiple .txt files to .xlsx files using a macro?

**Aaron_Tram** · 02-21-2013, 04:36 PM

I have multiple data files with tab delimited data that imports nicely into excel. The problem is that I have MANY of them and need to convert all of them to Excel files for other uses.

I started by importing one .txt file into excel using tab-delimited conversion which worked perfectly and then saved it as a .xlsx file. I recorded all this by a Macro. Is there a way to use the same macro to convert all the .txt files in a folder or subfolder?

Below is my VBA macro code. Thanks for the help!

Please Login or Register  to view this content.

**p24leclerc** · 02-21-2013, 05:14 PM

Are all of your text files the EXACT same format?
Will the excel workbook be in the same directory as those files?

**Aaron_Tram** · 02-21-2013, 05:19 PM

All of the files within a folder are in the same format, meaning all tab-delimited .txt files. They have varying numbers of rows.
The excel workbook can be within the same directory if needed.
- Thanks!

**p24leclerc** · 02-21-2013, 05:30 PM

try the following macro:

Please Login or Register  to view this content.

You place it in a module of any workbook. It could be an empty one. It will not be modified by the macro. Or you can place it in your Personnal.xlsb if you want.

**Aaron_Tram** · 02-21-2013, 05:38 PM

Worked like a charm! Thanks!

**Aaron_Tram** · 02-21-2013, 05:48 PM

One more question... Can the same code be modified to change a series of .csv files to .xlsx by simply changing the "txt" portion to "csv"? Thanks.

**p24leclerc** · 02-21-2013, 06:30 PM

CSV files are easier to open in Excel than TXT file.
Here would be the new macro:

Please Login or Register  to view this content.

Regards

P.S. Do not forget to mark your thread as solved if it is so and please click on the start (on the left side of the grey line) to mark your satisfaction. Thanks