Extracting Data for .Txt Files By Unique Field Data

[La]

Excel 2003 - I have approximately 150 coma delimited .txt files created from
Access over the past four years acting as an historical database of equipment
movement over time in ~ two week slices. The files are named i.e.
60713Inv.txt, 60628Inv.txt, 60612Inv.txt, 60410Inv.txt, 20830Inv.txt in a
YMMDD format and contain serial number, Bar Code number information for
Identification, and Site ID information of where the equipment was located
(among other data) at the time the file was created . I would like to find
the file (time slice) where a particular piece of equipment moved e.g. field
"Site ID" 55 before to field "Site ID" 70 after - where the change happened
between 60606Inv.txt and 60510Inv.txt. I would like to use the last six
characters (general format) of the field "Bar Code" or "S/N" to id the file.
The amount of equipment has grown over time as new equipment has been
added.There are about 2800 pieces of equipment in the lastest file and the
first file contains only 1700 pieces. Lastly, one piece could have moved more
than once over the years.

 

[PY & Associates]

Send us part of a typical file to check please

 

[PY & Associates]

We would try this, not tested

Dim datefrom&
Dim datetill&
Dim i&
Dim Fname$
Dim Newfname$
Dim rownr%
Dim j%

For i = datefrom To datetill Step 14
Fname = i & "Inv.txt"
Workbooks.OpenText Fname, etc
rownr = Match(eqmt, equipment_column).Row
Rows(rownr).Copy ThisWorkbook.ActiveSheet.Cells(j + 1, 1)
j = j + 1
ActiveWorkbook.Close
Next i

ActiveWorkbook.SaveAs Newfname

 

[La]

"SiteID","InventoryID","Serial.","ItemCategory","ItemDescription","Model.","BiosDate","VABarCode.","MSCertificate.","WinIPcfg","IPAddress","CmptrNme","OpSystem","SvcPack","McAfeeDat","UserName","Updated","UpdatedBy","Notes","NextPlacement","NextPlacementDate","NextPlacementOfficial","PurchaseDate","WarrantyExpiration","PurchaseOrderNumber","AssignedTo","AssignedToNote"
110,423,"23GNGK8",3,"IBM Personal Computer
300GL","6282-58U",,"190868","05597-OEM-0021693-10205","00-E0-29-03-02-CF","204.176.55.72","memremsw4",,,,"vhamembodfoj",1/31/2002
0:00:00,"vhacowillil",,,,0,,,,,
75,424,"23-M1752",6,"IBM 14""
Color","6540-00N",,"195407",,,"204.176.55.75",,,,,"vhamemtrallj",10/29/2004
0:00:00,"vhacowillil",,,,0,,,,,
54,430,"JPHK050046",5,"HP LaserJet
5N","C3952A",,"191659",,,"204.176.60.220",,,,,,6/27/2006
0:00:00,"vhacowillil",,,,0,,,,,
110,431,"SG63C2208X",9,"HP Color Scanner
4C","C2520B",,"190804",,,,,,,,,5/17/2002 0:00:00,"vhacowillil",,,,0,,,,,