PDA

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



fanjy
07-28-2006, 07:46 AM
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!:friends:

lucas
07-28-2006, 09:05 AM
This might help with your task.....might need tweeking to suit your needs:

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

fanjy
07-28-2006, 07:30 PM
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.:beerchug:

The following is Excel file and .txt files.

jindon
07-28-2006, 08:06 PM
Hi
try
note: if you have more than 65536 line to input, need modification

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

fanjy
07-28-2006, 10:15 PM
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
07-28-2006, 10:52 PM
fanjy,

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

You need to change myDir to your actual directory.

jindon
07-28-2006, 10:57 PM
Or place the text files in the same folder that workbook is in

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

fanjy
07-28-2006, 11:25 PM
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
07-28-2006, 11:34 PM
OK
Can you change like

Open myDir & myF For Input As #ff

fanjy
07-28-2006, 11:48 PM
again
the statement:
ReDim Preserve a(1 To n)
brings up a "subscript" error.

jindon
07-28-2006, 11:50 PM
Good sign!

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

fanjy
07-29-2006, 12:00 AM
Also see:
.Offset(i).Resize(,UBound(a(i))) = a(i)
display:"error 1004"

jindon
07-29-2006, 12:12 AM
Yep

I should consider for blank line..

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

Code above has been modified already...

fanjy
07-29-2006, 12:29 AM
Ok,succeed.Thank you.:friends:
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
07-29-2006, 12:34 AM
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
07-29-2006, 12:47 AM
jindon,
Perfectly!Thanks.
But in my sheet, First row is blank.Can you make data begin from the first row?

jindon
07-29-2006, 12:56 AM
Ok

Change

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

To

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

code has been modified....

fanjy
07-29-2006, 01:06 AM
jindon,Thank you very much.
The question has solved.
vbaexpress is a good place for my study.

lucas
07-29-2006, 06:55 AM
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
07-29-2006, 05:27 PM
fanjy,
Glad I could help

Steve
Thanks and I've just modified the code to coop with more than 65536 lines..

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