Results 1 to 2 of 2

Skipping rows while importing a text file if a field contains 0

Threaded View

  1. #1
    Registered User
    Join Date
    02-21-2011
    Location
    Sliema, Malta
    MS-Off Ver
    Excel 2003
    Posts
    1

    Skipping rows while importing a text file if a field contains 0

    I am importing a tab delimited text file and need to skip rows, that is not to import row, whose 4th field (column) is equal to zero. My current code is:
    Sub aged()
    '
    ' aged Macro
    
        Dim strinp As String
        strinp = Environ("userprofile") & "\My Documents\Dimensions Reports\Customer List.txt"
    
        Sheets("ImpCust").Select
        ActiveSheet.Unprotect
        Range("A1").Select
        With ActiveSheet.QueryTables.Add(Connection:= _
            "TEXT;" & strinp _
            , Destination:=Range("A1"))
            .Name = "Customer List"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = 437
            .TextFileStartRow = 1
            .TextFileParseType = xlDelimited
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            .TextFileConsecutiveDelimiter = False
            .TextFileTabDelimiter = True
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = False
            .TextFileSpaceDelimiter = False
            .TextFileColumnDataTypes = Array(1, 1, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, _
            9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9 _
            , 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, _
            9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9 _
            , 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 1, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 1, 9, 9, 9, 9, 9, 9, 1, _
            9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False
            ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    ' I need coding here to test the 4th field of each row and if equal to 0 will skip
    
        End With
    
    End Sub

    The following is a sample of the imported text, but I do not want to import the 3rd and 6th rows as their value is 0.00
    34000001 INV 28-Dec-06 796.3
    34000002 INV 24-Jan-07 23.29
    34000002 INV 29-Jan-07 0.00
    34000005 INV 31-Jan-07 139.76
    34000005 PAY 31-Jan-07 796.3
    34000005 INV 15-Mar-07 0.00
    34000007 PAY 24-Mar-07 186.35
    34000007 PAY 25-Apr-07 273.7

    Would appreciate a solution. Thank you in anticipation.
    34000008 INV 22-Jun-07 19.8
    Last edited by Leith Ross; 02-21-2011 at 03:39 PM. Reason: Added Code Tags

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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