CODEWITHSUNDEEP
filevbaexcel
Andrei Ion
Andrei Ion

Reputation: 1827

split text to rows excel

I have a VBA code with which I import a txt file into one cell. Here's the code (it's not that important):

Sub ReadFile()
     ' Requires a reference to Microsoft Scripting Runtime (Tools > References)
    Dim FSO As FileSystemObject
    Dim FSOFile As File
    Dim FSOStream As TextStream

    Dim Rand
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")

   Set FSO = New FileSystemObject
   Set FSOFile = FSO.GetFile("C:\Users\sdagfsgedg\Desktop\20121122.log")
   Set FSOStream = FSOFile.OpenAsTextStream(ForReading, TristateUseDefault)
   Rand = 1
   Do While Not FSOStream.AtEndOfStream
        ws.Cells(Rand, 1).Value = FSOStream.ReadAll
   Loop
End Sub

The text file 20121122.log has about 20.000 lines which are all imported in one cell. How can I split that cell into 20.000 cells (if the log has 20.000 lines). I don't want to read the text file line by line... I want to read it all (it's way more faster) then split every line on a separate row.

LATER EDIT: Or if there is another solution to read the log file and paste the text as line to row (not everything in one cell as I do right now)

Upvotes: 0

Views: 1502

Answers (1)

bhuang3
bhuang3

Reputation: 3633

// Code is not tested

Sub ReadFile()
  Dim FSO As FileSystemObject
  Dim FSOFile As File
  Dim FSOStream As TextStream

  Dim Rand
  Dim row
  Dim ws As Worksheet
  Set ws = Worksheets("Sheet1")

  Set FSO = New FileSystemObject
  Set FSOFile = FSO.GetFile("C:\Users\sdagfsgedg\Desktop\20121122.log")
  Set FSOStream = FSOFile.OpenAsTextStream(ForReading, TristateUseDefault)
  Rand = 1
  Dim content As String
  Dim lines As Variant
  Dim intIndex As Integer

  content = FSOStream.ReadAll
  lines = split(content, Chr(10))

  For intIndex = LBound(lines) To UBound(lines)
    ws.Cells(Rand, 1).Value = lines(intIndex)
    Rand = Rand + 1
  Next

End Sub

Upvotes: 2

Related Questions