Thanks to the contributions of others in this group I have a macro
which will open multiple text files (rtf to be precise) and copy them
into a single spreadsheet. However, the text files range in length and
when they are pasted into the sheet, they may range from 1 row to 50.

Since my ultimate goal is to use this file to import into an Oracle
database, I need to have the entire contents of each text file in a
single cell and then have the name of the file in the next column.

The macro I am using is the following:

Sub ImportText()
Dim fileRow As Integer
Dim pathname As String
Dim j As Integer, i As Integer, filenameLen As Integer


Application.DisplayAlerts = False
Application.ScreenUpdating = False


filetoOpen = Application.GetOpenFilename _
("Select Letter Files (*.rtf),*.txt", , , , True)


If IsEmpty(Range("A1")) Then
fileRow = 1
Else
fileRow = ActiveSheet.UsedRange.Rows( _
ActiveSheet.UsedRange.Rows.Count).Row + 1
End If


For i = 1 To UBound(filetoOpen, 1)
Workbooks.OpenText Filename:=filetoOpen(i), _
Origin:=xlWindows, StartRow:=1, _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, _
Space:=False, Other:=False


Debug.Print ActiveWorkbook.Name


Set newBook = ActiveWorkbook 'reference to textfile


ActiveSheet.UsedRange.Copy
With Workbooks("Import Letter
Templates.xls").Worksheets("Sheet1")
ActiveSheet.Paste Destination:=.Cells(fileRow, 1)
fileRow = .UsedRange.Rows( _
.UsedRange.Rows.Count).Row + 1
End With
Debug.Print fileRow
newBook.Close


Next i
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub