How to split text file into separate Excel sheets... 1

[Waxaholic]

I have a multi-line text file i am parsing into single-line. I want to be able to separate specific ranges of data within the file and save them to Excel sheets bearing the data groups name. An example of the data is:

SS001 KEY DLA DLT
1 1 22 356
2 2 38 59

SS002 KEY SLM MLT
1 1 500 38
2 2 44 300

Thus, i want to assign "SS001" and all data thereafter up until "SS002" to an Excel sheet labeled SS001. Is this possible and could someone point me in the right direction to either examples or code.

Thank you,

Brian

 

[SkipVought]

Hi, Waxaholic,

I can get you an answer, but I am curious...

1. Why are you breaking a file into separate sheets?

2. What do you mean by, "I have a multi-line text file i am parsing into single-line." Parsing is a breaking down of something into compnent parts. I am having a hard time understanding.

Enquiring minds need to know. Skip,
metzgsk@voughtaircraft.com

 

[Waxaholic]

Disregard the multi-line statement. I know that might have been confusing. Perhaps my term of parsing was used incorrectly. The data example above was the result of the existing code formatting it into single line data. I want to include code in my existing code to break each group (SS001, SS002) out into a separate sheet. The reason for breaking the file up into separate sheets is to perform calculations on it within excel via a "lookup" function. I have a prebuilt workbook that would reference these sheets for specific info and then chart it for me. I hope that helps with your questions. I am trying to improve upon an old solution here. Perhaps one day automating the whole process.

 

[Waxaholic]

One more thing. A typical non-formatted file (raw text) is 42000 lines in length. A formatted file (parsed) line is 6200 lines in length. Broken out into separate sheets would make it even more manageable. This would allow me to even append future data to each table. Maybe.

Thanks,

Waxaholic

 

[SkipVought]

The reason I ask is that often it is a mistake to take similar data from one table and transfer it to multiple tables. It goes against the database rules of data organization and normalization. Subsets of data can be selected in a process.

Now some questions regarding your data. Are you saying that in Col A, you might have...

SS001 KEY DLA DLT
1      1  22  356
2      2  38  59
SS002 KEY SLM MLT
1      1  500 38
2      2  44  300

???

That would mean that heading values and data values are burried in the sheet, true? Or am I reading this incorrectly? If so, then here is some code to do just that...

Sub CopyToSheets()
    Dim rngSheet As Range, shtThis As Worksheet, shtTo As Worksheet
    Dim sThis, sPrev, iCol As Integer, lRow As Long
    Set shtThis = ActiveSheet
    Set rngSheet = shtThis.Range(Cells(1, 1), Cells(1, 1).End(xlDown))
    sPrev = ""
    For Each rngSource In rngSheet
        With rngSource
            iColCount = shtThis.Range(shtThis.Cells(.Row, 1), shtThis.Cells(.Row, 1).End(xlToRight)).Columns.Count
            If IsNumeric(.Value) Then
                lRow = shtTo.Cells(1, 1).CurrentRegion.Rows.Count + 1
            Else
                Set shtTo = Worksheets.Add(After:=Worksheets(Worksheets.Count))
                shtTo.Name = .Value
                lRow = 1
            End If
            For iCol = 1 To iColCount
                shtTo.Cells(lRow, iCol).Value = .Offset(0, iCol - 1).Value
            Next
        End With
    Next
End Sub

Hope this helps ;-)
Skip,
metzgsk@voughtaircraft.com

 

[Waxaholic]

Thanks Skip. I will put it to good use. Thank you very much.

Waxaholic

 

[ninash]

Waxaholic,

If you find a post useful it is the done thing to give the person who supplied the help a star.
I have issued Skip with a star this time but in the future try to remember this.

Ninash