Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
| Search | Today's Posts | Mark Forums Read
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old February 28th, 2004, 06:07 PM
Registered User
Join Date: Feb 2004
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default How to read multiple text files into excel?

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, _

 Next i

End Sub

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

  #2 (permalink)  
Old February 29th, 2004, 05:03 AM
Friend of Wrox
Join Date: Oct 2003
Location: , , Finland.
Posts: 168
Thanks: 0
Thanked 0 Times in 0 Posts


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


  #3 (permalink)  
Old February 29th, 2004, 03:20 PM
Registered User
Join Date: Feb 2004
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts

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.

  #4 (permalink)  
Old March 5th, 2004, 05:13 PM
Friend of Wrox
Join Date: Jun 2003
Location: , , Denmark.
Posts: 150
Thanks: 0
Thanked 0 Times in 0 Posts

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.
  #5 (permalink)  
Old March 5th, 2004, 06:26 PM
Friend of Wrox
Join Date: Jun 2003
Location: High Wycombe, UK, United Kingdom.
Posts: 344
Thanks: 0
Thanked 1 Time in 1 Post

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.

Similar Threads
Thread Thread Starter Forum Replies Last Post
Importing Multiple Files Into Excel Using VB bsac Excel VBA 4 January 27th, 2014 11:37 AM
Importing multiple text files to sql server db tammyk SQL Server DTS 1 April 25th, 2008 12:07 AM
Export to multiple Excel files using template ecapox Access VBA 0 February 5th, 2008 10:38 AM
code to read two text files, concatenate one...... dgr7 Beginning VB 6 2 April 18th, 2007 03:24 PM
How to read text files from CD? Alex1024 VB How-To 2 November 3rd, 2003 12:50 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.