Importing multiple text files to spreadsheet

  • Thread starter Thread starter lanwu
  • Start date Start date
L

lanwu

Just wondering if anyone might know if there's a way to automate this
problem:

I have 200 text files that need to be imported to 1 worksheet on Excel.

- each file should be imported to 1 column in the Excel worksheet
- the 'next' file imported to the next column
- thus after the import, there should be 200 filled columns
- starting with zero columns filled

??? Any ideas?

==========================

FYI: essentially, i'm trying to get information from the text files -
which represent 1 record - to a database, & i'm using a spreadsheet to
do this coz after the import, I will still have to adjust the size of
each column to align the fields (each line in a text file = 1 field,
but some lines are missing from some text files). So if anyone can
suggest another method to do this, by all means......

ps. I actually want each file to fill a row, not a column, but this
seems easy enough w/ a "Paste Special" & "transpose" function.....
 
Ianwu,

The following VBA sub will open each .txt file in folder C:\MyFolder in
turn, and import each line in each file horizontally, so no need to
transpose afterwards. Open a new workbook, right-click on a sheet tab and
select View Code; when taken to the VBA window, select the file you want to
save the code in in the upper left panel (the new workbook, or, Personal.xls
so it is globally available), and go to menu item Insert > Module. Pste the
code below in the right hand side panel, and change the folder from
C:\MyFolder to your actual folder name. Run the code (F5 in the VBA window
or Tools > Macro > Macros while in the spreadsheet) and it's done.

Sub Import_Text_Files()
Dim fs, f, f1, vLine
Dim i As Long, j As Long
i = 0
Range("A1").Select
fldr = "C:\MyFolder\"
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.getfolder(fldr)
Set f1 = f.Files
For Each f2 In f1
filenam = fldr & f2.Name
If Right(filenam, 4) = ".txt" Then
j = 0
Open filenam For Input As #1
Do Until EOF(1)
Line Input #1, vLine
ActiveCell.Offset(i, j).Value = vLine
j = j + 1
Loop
Close #1
i = i + 1
End If
Next
End Sub

HTH,
Nikos
 
Back
Top