Reading Multiple Text File into Single Worksheet

[thunderkid]

I would like to open multiple text files into a single Excel file (each file would be extracted onto a sheet). I would like to read the text files, up to 20 files, from a text file. I know that I can use the following subroutine for single text file into one worksheet:

Sub OpenTextFile()
Workbooks.OpenText Filename: "C:\directory\file_1, _
DataType:=xlDelimited, tab:=True, comma:=True
End Sub

I can use the above as many times as I need to by replacing the filename (file_1). Any ideas?

Thanks
thunderkid

 

[SkipVought]

Hi,

You asked, "I would like to read the text files, up to 20 files, from a text file."

What data is in the text file that indicates the files to open?

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[thunderkid]

Would it help to restrict the list of files to a specific number, say 15? Input files look like the following:

Input files:
Seed, a, b
123456, 5, 6
234567, 56, 6
345678, 4, 6
456789, 4, 7
456789, 6, 16
434567, 5, 20
.
.
30 records

 

[camy123]

Hi why dont u loop through the folder and open all the text files in the XL sheet.
let me know if need the code.

 

[MakeItSo]

How about using the Shell copy command first?
put all files into a folder. In Shell:

C:\yourfolder> copy *.txt allmerged.txt

You could also paste this code into a text file and save it as e.g. "merge.bat", as a batch file.

You can then import the merged file instead of all single text files.
Precondition: The files are all formatted identically (e.g. same column names, same format, same delimiter)

Greetings,
MakeItSo


Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://www.andygalambos.de

 

[thunderkid]

camy123
pls provide code for the looping routine.
thunderkid

 

[johnwm]

I posted a solution for camy123 in thread222-798640. It's often worth reading faq222-2244 before posting, and also doing a forum search, as many answers are already out there.

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'