Hello All,
I am importing a text file, Comma Seperated, small sample below.
Code
7/3/2007,0.24,55,LT-W678,HAMMER,BDCUT,,H22,
7/3/2007,0.26,550,LT-W678,HAMMER,PRODUC,,H22,
7/3/2007,0.394224537037037,55,LT-W678,HAMMER,PRODUC,,H22,
7/3/2007,0.4,550,LT-W678,HAMMER,MAINT,,H22,
7/4/2007,0.5,550,LT-W678,HAMMER,PRODUC,,H22,
7/4/2007,0.75,55,LT-W678,,,600,H22,
7/4/2007,0.75,550,LT-W678,,,550,H11,
7/4/2007,0.453229166666667,55,LT-W678,,,700,H11,
7/6/2007,0.456018518518519,55,LT-W678,HAMMER,SETUP,,H5,
8/3/2007,0.24,55,LT-W678,HAMMER,BDCUT,,H22,
8/3/2007,0.26,550,LT-W678,HAMMER,PRODUC,,H22,
8/3/2007,0.394224537037037,55,LT-W678,HAMMER,PRODUC,,H22,
8/3/2007,0.4,550,LT-W678,HAMMER,MAINT,,H22,
8/3/2007,0.5,550,LT-W678,HAMMER,PRODUC,,H22,
8/3/2007,0.75,55,LT-W678,,,600,H22,
8/3/2007,0.75,550,LT-W678,,,550,H11,
8/3/2007,0.453229166666667,55,LT-W678,,,700,H11,
8/3/2007,0.456018518518519,55,LT-W678,HAMMER,SETUP,,H5,
Display More
Over time it may get quite large.
The code below will import the text file.
Code
Sub Macro1()
With ActiveSheet.QueryTables.Add(Connection:="TEXT;C:\hampunches3.txt", _
Destination:=Range("A2"))
.Name = "hampunches3"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub
Display More
Then I use an advanced filter to filter out only the data I want.
Code
Sub Macro2()
Application.CutCopyMode = False
Range("A1:I5000").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
"L1:N2"), CopyToRange:=Worksheets("Sheet2").Range("A1"), Unique:=False
End Sub
too keep my sheet smaller and simpiler, I would like to filter the data as the file is imported.
I see no need to import 5,000 records and filter it down to 20. I would like to just import the 20 records as I need.
Thanks,
Ross