Import several text files to separate worksheets of the same workbook

[premvinoth]

Hi,

First a big thanks to this forum for my learning in VBA here.

I have seven different text files named DVE, DVB, FAE, FAF, FAG, FAM, FAB.

I have a macro named D_Import(), when I run this macro it prompts me for a text file, and I select DVE.txt from it's location, then it imports the text file as per my requirement to the Excel and saves it as DVE.xls.

The same macro I repeat it for DVB.txt and it saves it as DVB.xls.

I have another macro named F_Import(), which I use for FAE, FAF, FAG, FAM, FAB text files and save it as respective excel files.

Now what I would like to do is, instead of running the macro 7 times, can I have a single one that imports 7 text files into 7 worksheets of a same workbook and name the sheets with the text file's name?

Note: D_Import() and F_Import() are different macros that handles text files with different formats and fields.

Thanks in advance,
Vinoth

 

[Rhodie72]

Hi,

First a big thanks to this forum for my learning in VBA here.

I have seven different text files named DVE, DVB, FAE, FAF, FAG, FAM, FAB.

I have a macro named D_Import(), when I run this macro it prompts me for a text file, and I select DVE.txt from it's location, then it imports the text file as per my requirement to the Excel and saves it as DVE.xls.

The same macro I repeat it for DVB.txt and it saves it as DVB.xls.

I have another macro named F_Import(), which I use for FAE, FAF, FAG, FAM, FAB text files and save it as respective excel files.

Now what I would like to do is, instead of running the macro 7 times, can I have a single one that imports 7 text files into 7 worksheets of a same workbook and name the sheets with the text file's name?

Note: D_Import() and F_Import() are different macros that handles text files with different formats and fields.

Thanks in advance,
Vinoth

It's almost the same code but instead of selecting a single file, you specify all of them and then save and close each individually. If they are always the same files it's a simple for each wb in workbooks.count ... next loop that does the hard work.

To code the exact code you need, just post the directory here and the full names of the files. Are they TXT, TAB or CSV files? We don;t know.

 

[premvinoth]

All seven are text files.

The path is D:\Dats\DVB.TXT, DVE.TXT, FAB.TXT, FAE.TXT, FAG.TXT, FAM.TXT, FAF.TXT.

Text files DVB and DVE should be imported using the codes in D_Import().

Text files FAB, FAE, FAG, FAM, FAN Should be imported using codes in F_Import().