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

"La" <[email protected]> wrote in message
news:[email protected]...
> 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]

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" <[email protected]> wrote in message
news:[email protected]...
> 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.

[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,,,,,

"PY & Associates" wrote:

> Send us part of a typical file to check please
>
> "La" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
>
>
>