Hi,
I have a big problem in my master thesis.
I want to read many text file into excel file for data analyses. The text files are so numerous that I can not read the files one by one. So I plan to read it into excel by VBA code. But I just learn to how to read one file into excel, and do not know how to deal with numerous. I can modify to name the files with number. My code:

Sub opentext()

Dim i As Integer


For i = 1 To 10000

 Workbooks.opentext Filename:="D\:VB\10000+i.out", _ /The text file can be name as 10001, 10002,....
 Origin:=xlMSDOS, _
 StartRow:=1, _
 DataType:=xlDelimited, _
 ConsecutiveDelimiter:=True, _
 Space:=True

 Next i

End Sub

Obviously, the code can not work. Could somebody give me a help. Thanks in advance.

Hi

You must change following line:
Filename:="D\:VB\10000+i.out" to
FileName:="D:\VB\10000" & i & ".out"

-vemaju

Hi, vemaju, thank you for your nice help. The code runs greatly.
But now I have two other problems.
1. there is no name for the opened excel file exception the system name, such as book1, book2,.....how can I make the name of the opened excel files the same as that of the text file?
2. Is it possible that we let the text files opened in different sheets in the same workbook with different sheet name?
Thank you very much.

Instead of using the Workbooks.OpenText method, try the Import External Data option from the Data menu.

In your loop, use a string variable to hold the filename excl. extension.

Add a new sheet, rename it using the variable.
select the new sheet.
Import the text file to this sheet (add the i value as you did before and the extension)
..and start all over again within your loop.

I think that should do it.

You can get all the code by using the Record Macro.

Have you tried QueryTables, it worked for a solution I was working on allowing me to load different content into different worksheets in the same workbook.