I need to regularly move all text files from a directory into a database
(Excel97). All text files are exactly the same number of rows but not in
length of rows and the data within needs to be appended to a single table
(every row is a field in the record). Is there a way to loop through all
..txt files from a directory and run a TransferText that appends the data
from each into the target table?

This should work:-

Sub Macro1()
Workbooks.Open Filename:= "filename.txt"
Selection.Copy
Windows("Book1").Activate
Range("A1").Select
ActiveSheet.Paste
MsgBox ("Complete")
End Sub

Just put the path and filename of "filename.txt" and the current
workbooks name in "Book1"


---
Message posted from http://www.ExcelForum.com/

Not completely,

I have 10 .txt files in a directory. Each file has 10 lines. I want to put
each line in a cells where every row is the contect of one txt file.



"ianripping " schreef in bericht
...
This should work:-

Sub Macro1()
Workbooks.Open Filename:= "filename.txt"
Selection.Copy
Windows("Book1").Activate
Range("A1").Select
ActiveSheet.Paste
MsgBox ("Complete")
End Sub

Just put the path and filename of "filename.txt" and the current
workbooks name in "Book1"


---
Message posted from http://www.ExcelForum.com/

I'm not sure I understood :(, but following can work...Each .txt file is
pasted in single row...I had similar problem with 1500 .xls files. I
didn't test following code, but I hope it can work...

Sub Macro1()

For i = 1 to 10
Workbooks.Open Filename:= "File" & i & ".txt"
Selection.Copy
Windows("Book1").Activate
Range("A1").Select
ActiveSheet.Pastespecial := xlvalues, transpose:= true
Activecell.offset(1,0).select
Workbooks("File" & i & ".txt").close
Next i
MsgBox ("Complete")

End Sub


---
Message posted from http://www.ExcelForum.com/

-this example is what I have been looking for.
1) The code below will not run & do not understand the line tha
fails.
2) Will this load all text files into one sheet, in one row after th
next?

Sub Macro1()

For i = 1 To 10
Workbooks.Open Filename:="File" & i & ".txt"
Selection.Copy
Windows("Book1").Activate
Range("A1").Select

--ActiveSheet.PasteSpecial := xlvalues, transpose:= true

ActiveCell.Offset(1, 0).Select
Workbooks("File" & i & ".txt").Close
Next i
MsgBox ("Complete")

End Sub

Thanks

--
Message posted from http://www.ExcelForum.com