Open text files as Worksheets in a workbook
 

On approximately Fri, 25 Apr 2003 22:20:06 +0100, "Ong, Seng"
said something to the effect of:

Hi!

SOS! Would someone please enlighten me on how to do that?

I desperately need a routine to open files from a folder and import them
into separate worksheets instead of separate workbooks.

And, would someone please tell me which site has got the most comprehensive
collection of Macro source code that I can copy and adapt to use?

Thanks so much!

Seng



Sub TextFileToSheet(ByVal FName As String, ByVal Sh As String)
Dim Row As Long
Dim TextLine As String
Row = 1
Application.ScreenUpdating = False
On Error GoTo Bottom:
Open FName For Input As #1
While Not EOF(1)
Line Input #1, TextLine
If Not EOF(1) Then
Sheets(Sh).Cells(Row, 1).Value = TextLine
Row = Row + 1
End If
Wend
Bottom:
Application.ScreenUpdating = True
Close #1
End Sub

You can get much fancier than this, like looking for delimiting
characters, stripping out characters other than alphanumerics and
punctuation, checking to ensure the sheet and file actually exist,
etc., but this is one basic way to do what you wanted. Another way
(cheater's method) is to use Workbooks.OpenText and let it create a
new workbook, but then copy the only sheet from that workbook into
your own workbook, rename the sheet, and close the unneeded new
workbook. But there's something to be said for controlling the
process yourself. :)

-Binarian