+ Reply to Thread
Results 1 to 2 of 2

Extracting Text Data to Excel

Hybrid View

Guest Extracting Text Data to Excel 02-02-2006, 07:35 AM
Guest Re: Extracting Text Data to... 02-02-2006, 01:35 PM
  1. #1
    Ozzie via OfficeKB.com
    Guest

    Extracting Text Data to Excel

    I have been struggling now for a few days, so time to ask the experts. My
    problem is this;

    I have 46 txt files each in exactly the same format and layout each with
    different file names,
    I need to be able to open all the files in th efolder Without specifying file
    names,
    Then to be able to extract the 3rd and 46th line from each txt file,
    I then want to paste these 2 lines into a workbook leave a space and then
    paste the next 2 lines and so on,
    and finally the two lines will need formatted to "text to columns\fixed width\


    can anyone help?

    many thanks

    David

    --
    Message posted via http://www.officekb.com

  2. #2
    Steve Yandl
    Guest

    Re: Extracting Text Data to Excel

    I'm assuming that the 46 text files are all in the same folder (in my
    example, C:\Test), that they all have a file extension of "txt" and that no
    other files in that folder have a txt extension. In the TextToColumns
    routine, you will need to provide more Array information to get a decent
    result.

    Sub FetchSomeLines()

    Dim R As Integer
    Dim rngA As Range
    Dim rngB As Range

    Const ForReading = 1

    strFldPath = "C:\Test"
    R = 1

    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set objFldr = FSO.GetFolder(strFldPath)

    For Each objFile In objFldr.Files
    If FSO.GetExtensionName(objFile) = "txt" Then
    Set objTextFile = FSO.OpenTextFile(objFile, ForReading)
    objTextFile.ReadLine
    objTextFile.ReadLine
    Cells(R, 1).Value = objTextFile.ReadLine
    R = R + 1

    For I = 1 To 42
    objTextFile.ReadLine
    Next I

    Cells(R, 1).Value = objTextFile.ReadLine
    R = R + 2

    objTextFile.Close

    End If
    Next objFile

    Set rngA = Range("A1").EntireColumn
    Set rngB = Range("B1")

    rngA.TextToColumns Destination:=Range("B1"), DataType:=xlFixedWidth, _
    FieldInfo:=Array(0, 1), TrailingMinusNumbers:=True

    rngA.Delete

    End Sub





    Steve


    "Ozzie via OfficeKB.com" <u18021@uwe> wrote in message
    news:5b4669a0bbeff@uwe...
    >I have been struggling now for a few days, so time to ask the experts. My
    > problem is this;
    >
    > I have 46 txt files each in exactly the same format and layout each with
    > different file names,
    > I need to be able to open all the files in th efolder Without specifying
    > file
    > names,
    > Then to be able to extract the 3rd and 46th line from each txt file,
    > I then want to paste these 2 lines into a workbook leave a space and then
    > paste the next 2 lines and so on,
    > and finally the two lines will need formatted to "text to columns\fixed
    > width\
    >
    >
    > can anyone help?
    >
    > many thanks
    >
    > David
    >
    > --
    > Message posted via http://www.officekb.com




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1