How to read all lines from a text file into cells C10:Cxxx ?

[Claudia d'Amato]

Assume I have a text file with a couple of lines (e.g. 234 lines).

Now I want to read the content of this text file into the currently opened worksheet
where the value of the first line is assigned to cell C10.
The value of the second line should be assigned to the net cell in the C column and so far....

How can I tell Excel to do this?

Claudia

 

[JLatham]

You need a macro to do that. Below is code that will do it for you. To put
the code in your workbook, open it up and press [Alt]+[F11] to enter the VB
Editor. In the VBE use Insert --> Module to create a new code module. Copy
the code below and paste it into the new code module and close the VB Editor.

To use it, go to the sheet where you want the contents of the text file to
be placed and use Tools --> Macro --> Macros and select the name of the macro
and click the [Run] button. Then you can browse to the file and read it into
your workbook. Code assumes the sheet is not protected.


Sub ReadTextFiles()
Dim textFile As String
Dim fileBufNum As Integer
Dim rawData As String
Dim baseCell As Range
Dim rowOffset As Long

textFile = Application.GetOpenFilename
If UCase(textFile) = "FALSE" Then
'user x'd or [Cancel]ed out
Exit Sub
End If
Set baseCell = ActiveSheet.Range("C10")
fileBufNum = FreeFile()
Open textFile For Input As #fileBufNum
While Not EOF(fileBufNum)
Line Input #fileBufNum, rawData
baseCell.Offset(rowOffset, 0) = rawData
rowOffset = rowOffset + 1
Wend
Close #fileBufNum
Set baseCell = Nothing
MsgBox "File Read Completed"
End Sub