Importing specific text

[aa2000]

Hi all

I'm using slightly modified code to import text files to excel. The imported files are basically a list experiment results, however only certain results are important for me.
Some experiments begin with the word "Single test", and these should not be imported. How can I modify my current code to do so?

Heres the code so far:

Public Sub ImportTextFile(FName As String, Sep As String)

Dim RowNdx As Long
Dim ColNdx As Integer
Dim TempVal As Variant
Dim WholeLine As String
Dim Pos As Integer
Dim NextPos As Integer
Dim SaveColNdx As Integer
Dim StartFlag As Boolean

Application.ScreenUpdating = False
SaveColNdx = ActiveCell.Column
RowNdx = ActiveCell.Row
StartFlag = False

Open FName For Input Access Read As #1
While Not EOF(1)
Line Input #1, WholeLine

    If WholeLine = "Function" Then
    StartFlag = True
    End If
        If StartFlag = True Then
            If Right(WholeLine, 1) <> Sep Then
            WholeLine = WholeLine & Sep
            End If
        ColNdx = SaveColNdx
        Pos = 1
        NextPos = InStr(Pos, WholeLine, Sep)
        While NextPos >= 1
        TempVal = Mid(WholeLine, Pos, NextPos - Pos)
        Cells(RowNdx, ColNdx).Value = TempVal
        Pos = NextPos + 1
        ColNdx = ColNdx + 1
        NextPos = InStr(Pos, WholeLine, Sep)
        Wend
        RowNdx = RowNdx + 1
        End If
        Wend
        
            Set Borderrange = Range("A3:AZ2000")
            For Each c In Borderrange.Cells
            If c.Value = "END" Then c.EntireRow.Borders(xlEdgeBottom).LineStyle = XlLineStyle.xlContinuous
            Next c
            
                Dim LastCol As Long
                LastCol = Cells.Find("*", Cells(Rows.Count, Columns.Count), SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
                Columns(LastCol).Borders(xlEdgeRight).LineStyle = XlLineStyle.xlContinuous
                Cells(1, LastCol).Offset(0, 2).Select
            


EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #1
End Sub

The problem is I cannot use " If wholeline = "Single test" then .... " because Single test is followed by the experiment name. Additionally eac single test is only 5 lines long, so I was thinking of using something like this:

If WholeLine = "Single Test" + "*" Then ...

However Im not sure how to get the macro to ignore this line and the next 4, and move on. Any suggestions on this?

Thanks!

 

[doofusboy]

You could try something like this:

    If WholeLine = "Single Test" + "*" Then
        Line Input #1, WholeLine
        Line Input #1, WholeLine
        Line Input #1, WholeLine
        Line Input #1, WholeLine
    End If

 

[aa2000]

Thanks for the suggestion, but this code only seems to find lines starting with "single test", rather than remove them.
How should I alter it to ignore those lines?
Also where should I place these lines in the importtextfile sub?

Thanks

 

[doofusboy]

That code finds lines starting with "single test", then "ignores" them by reading the next 4 lines of your input file into the variable WholeLine and does nothing else.

 

[aa2000]

Sorry if I am not using it correctly, but the code does not prevent any lines from being imported.
I am not sure whether this would affect it but I am putting it just after the line that says: "if startflag = true then"

Should it go elsewhere, or should the code be changed?

Cheers

 

[doofusboy]

You should really post your newly modified code so it can be evaluated.

 

[aa2000]

Ok, I've added your suggestions into the full code as shown below. Is this the correct place for them?

Public Sub ImportTextFile(FName As String, Sep As String)

Dim RowNdx As Long
Dim ColNdx As Integer
Dim TempVal As Variant
Dim WholeLine As String
Dim Pos As Integer
Dim NextPos As Integer
Dim SaveColNdx As Integer
Dim StartFlag As Boolean

Application.ScreenUpdating = False
SaveColNdx = ActiveCell.Column
RowNdx = ActiveCell.Row
StartFlag = False

Open FName For Input Access Read As #1
While Not EOF(1)
Line Input #1, WholeLine

    If WholeLine = "Function" Then
    StartFlag = True
    End If
        If StartFlag = True Then
        
        If WholeLine = "Single Test" + "*" Then
        Line Input #1, WholeLine
        Line Input #1, WholeLine
        Line Input #1, WholeLine
        Line Input #1, WholeLine
        End If
        
            If Right(WholeLine, 1) <> Sep Then
            WholeLine = WholeLine & Sep
            End If
        ColNdx = SaveColNdx
        Pos = 1
        NextPos = InStr(Pos, WholeLine, Sep)
        While NextPos >= 1
        TempVal = Mid(WholeLine, Pos, NextPos - Pos)
        Cells(RowNdx, ColNdx).Value = TempVal
        Pos = NextPos + 1
        ColNdx = ColNdx + 1
        NextPos = InStr(Pos, WholeLine, Sep)
        Wend
        RowNdx = RowNdx + 1
        End If
        Wend
        
            Set Borderrange = Range("A3:AZ2000")
            For Each c In Borderrange.Cells
            If c.Value = "END" Then c.EntireRow.Borders(xlEdgeBottom).LineStyle = XlLineStyle.xlContinuous
            Next c
            
                Dim LastCol As Long
                LastCol = Cells.Find("*", Cells(Rows.Count, Columns.Count), SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
                Columns(LastCol).Borders(xlEdgeRight).LineStyle = XlLineStyle.xlContinuous
                Cells(1, LastCol).Offset(0, 2).Select
            


EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #1
End Sub

Cheers

 

[doofusboy]

Try changing this:

        If WholeLine = "Single Test" + "*" Then

To this:

        If Mid(WholeLine, 1, 11) = "Single Test" Then