Solved: Solved:How to get several .txt file data in a workbook?

[fanjy]

Hi,everyone

Usually,I can open a .txt file and get its data in a workbook.Now,I have two or more .txt files in a folder or in a directory.How can I get these .txt file data in the same workbook? Can I make these .txt files as a Collection Object?

Thanks for any assistance!

[lucas]

This might help with your task.....might need tweeking to suit your needs:
[VBA]
Option Explicit
Sub ImportTextUsingXlDialogOpen()

'\ use this approach to prompt user for text wizard values

Application.ScreenUpdating = False
Application.DisplayAlerts = False
ActiveSheet.UsedRange.Clear
Range("A1").Select

'\ display open file dialog and copy to new (temporary workbook)
If Application.Dialogs(xlDialogOpen).Show("*.txt") Then
ActiveSheet.UsedRange.Select '\ select imported text in temporary workbook
Selection.Copy '\ copy to clipboard
ActiveWorkbook.Close '\ close temporary workbook
ActiveSheet.Paste '\ paste text into your workbook
End If
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
[/VBA]

[fanjy]

Steve,Thank you very much.
But I don't want to I open then Text Guide dialog.
use VBA,only one time ,total these .txt files in a workbook.

The following is Excel file and .txt files.

[jindon]

Hi
try
note: if you have more than 65536 line to input, need modification
[vba]
Sub Sample()
Dim n As Long, a(), ff As Integer, txt As String, myDir As String, x
Dim myF As String, i As Long
myDir = "C:\test\" ' change here to suite
myF = Dir(myDir & "*.txt")
Do While myF <> ""
ff = FreeFile
Open myF For Input As #ff
Do While Not EOF(ff)
Line Input #ff, txt
x = Split(txt)
Redim Preserve a(1 To n)
a(n) = x
Loop
Close #ff
myF = Dir()
Loop
With ThisWorkbook.Sheet1.Range("a1")
For i = 1 To UBound(a)
.Offset(i).Resize(,UBound(a(i))) = a(i)
Next
End With
End Sub[/vba]

[fanjy]

Hi,jindon
Thank you for your Code!Sorry,I am a newer.When I run the Macro,Excel display "File Not Found!".Why?see:

[jindon]

fanjy,

Sorry, I'm not able to see your file at the moment.

You need to change myDir to your actual directory.

[jindon]

Or place the text files in the same folder that workbook is in
[vba]
Sub Sample()
Dim n As Long, a(), ff As Integer, txt As String, myDir As String, x
Dim myF As String, i As Long
myDir = ThisWorkbook.Path & Application.PathSeparator
myF = Dir(myDir & "*.txt")
Do While myF <> ""
ff = FreeFile
Open myF For Input As #ff
Do While Not EOF(ff)
Line Input #ff, txt
x = Split(txt)
Redim Preserve a(1 To n)
a(n) = x
Loop
Close #ff
myF = Dir()
Loop
With ThisWorkbook.Sheet1.Range("a1")
For i = 1 To UBound(a)
.Offset(i).Resize(,UBound(a(i))) = a(i)
Next
End With
End Sub[/vba]

[fanjy]

jindon:
I have tried,but failed.
Statement:ThisWorkbook.Sheet1.Range("a1") ,Should change:ThisWorkbook.Worksheets("Sheet1").Range("a1")
When run The Statement:
Open myF For Input As #ff
display:
"File Not Find!"
You can see my file in #5.

[jindon]

OK
Can you change like

Open myDir & myF For Input As #ff

[fanjy]

again
the statement:
ReDim Preserve a(1 To n)
brings up a "subscript" error.

[jindon]

Good sign!
[vba]
Sub Sample()
Dim n As Long, a(), ff As Integer, txt As String, myDir As String, x
Dim myF As String, i As Long
myDir = ThisWorkbook.Path & Application.PathSeparator
myF = Dir(myDir & "*.txt")
Do While myF <> ""
ff = FreeFile
Open myDir & myF For Input As #ff
Do While Not EOF(ff)
Line Input #ff, txt
x = Split(txt, "|")
n = n + 1
Redim Preserve a(1 To n)
a(n) = x
Loop
Close #ff
myF = Dir()
Loop
With ThisWorkbook.Sheets("Sheet1").Range("a1")
For i = 1 To UBound(a)
.Offset(i - 1).Resize(,UBound(a(i))+1) = a(i)
Next
End With
End Sub[/vba]

[fanjy]

Also see:
.Offset(i).Resize(,UBound(a(i))) = a(i)
display:"error 1004"

[jindon]

Yep

I should consider for blank line..

.Offset(i).Resize(,UBound(a(i))+1) = a(i)

Code above has been modified already...

[fanjy]

Ok,succeed.Thank you.
But these data is filled in column A.
Can you fill these data in different column?For example,
SaleData|Sales amount|ID|POSID|BankerID|
column A B C D E
and ,not display "|" sign.
see:

[jindon]

OK, so the file is delimited by |, I thought it would be a space....

Can you just change the line of

x = Split(txt)

to

x = Split(txt, "|")

will change the code later....

[fanjy]

jindon,
Perfectly!Thanks.
But in my sheet, First row is blank.Can you make data begin from the first row?

[jindon]

Ok

Change

.Offset(i).Resize(,....

To

.Offset(i-1).Resize....

code has been modified....

[fanjy]

jindon,Thank you very much.
The question has solved.
vbaexpress is a good place for my study.

[lucas]

Nice job Jindon, thanks for following up on this.
fanjy, if you have your answer please mark your thread solved using thread tools at the top of the page.

[jindon]

fanjy,
Glad I could help

Steve
Thanks and I've just modified the code to coop with more than 65536 lines..
[vba]
Sub Sample()
Dim n As Long, a(), ff As Integer, txt As String, myDir As String, x
Dim myF As String, i As Long, t As Integer
t = 1
myDir = ThisWorkbook.Path & Application.PathSeparator
myF = Dir(myDir & "*.txt")
Do While myF <> ""
ff = FreeFile
Open myDir & myF For Input As #ff
Do While Not EOF(ff)
Line Input #ff, txt
x = Split(txt, "|")
n = n + 1
Redim Preserve a(1 To n)
a(n) = x
If n = 65536 Then
With ThisWorkbook.Sheets(t).Range("a1")
For i = 1 To UBound(a)
.Offset(i- 1).Resize(,UBound(a(i))+1) = a(i)
Next
End With
n = 0 : Erase a : t = t + 1
Loop
Close #ff
myF = Dir()
Loop
If n > 0 Then
With ThisWorkbook.Sheets(t).Range("a1")
For i = 1 To UBound(a)
.Offset(i - 1).Resize(,UBound(a(i))+1) = a(i)
Next
End With
End If
End Sub[/vba]