If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.

Send both
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"StackemEvs" wrote in message
...

Sorry for not getting this back quicker, been away from my machine.
Trying to describe it better now.

I want to take information from two files and put them together,
outputting the data in one file. They have one common field by which to
correlate the data. All items in file one are numbers, File two has the
MainID code and a description which I want to add to the data in the
first file. The MainID is the only unique number, but is not always
sequential, and misses a lot of numbers (is this making sense?) .

I'm not sure how much of this code is actually relevant, or if what I
haven't put in is, so sorry if too much/too little.

'***General Descriptions etc

Const FSO_ForReading As Integer = 1
Const InputFile2 = TEMPDIR & "FileDesc.tmp"

Dim fso As Object
Dim ts_IN As Object
Dim ts_IN2 As Object
Dim sLine As String
Dim sLine2 As String
Dim lngFileOut As Long
Dim UsrDataRecord As DataRecord
Dim nRow As Long
Dim nRow2 As Long
Dim strcode As String
Dim strDesc As String

Set fso = CreateObject("Scripting.FileSystemObject")
Set ts_IN = fso.OpenTextFile(InputFile, FSO_ForReading, True)
Set ts_IN2 = fso.OpenTextFile(InputFile2, FSO_ForReading, True)
lngFileOut = FreeFile
Open OutputFile For Append As lngFileOut

nRow = 0
Row2 = 0
strcode = ""
strDesc = ""


'***File One, so inputting most data in from this file***
'***Reading one line at a time

Do While Not ts_IN.AtEndOfStream
sLine = ts_IN.ReadLine

With UsrDataRecord
MainID = Left(sLine, 4) 'unique file for matching
item1 = Mid(sLine, 5, 8)
item2 = Mid(sLine, 13, 13)
item3 = Mid(sLine, 26, 7)
item4 = Mid(sLine, 33, 9)

'*** Now looking for description by matching .MainID
Do While .MainID = strcode
If .itemcode = strcode Then
Item5 = strDesc
Exit Do
ElseIf ts_IN2.AtEndOfStream Then
Exit Do
ElseIf .itemcode < strcode Then
Item5 = "Not recorded"
Exit Do
Else
'***So moving onto the next code and description
Do While Not ts_IN2.AtEndOfStream
sLine2 = ts_IN2.ReadLine
strcode = Left(sLine2, 4)
strDesc = Mid(sLine2, 5, 20)
nRow2 = nRow2 + 1
Exit Do
Loop
End If
Loop

WriteOutput UsrDataRecord, lngFileOut
End With
nRow = nRow + 1

Loop
closeAllFiles

This currently works as long as the next MainID in the second file is a
higher number than the previous one it looked for. If it is lower then
the previous description is being added, and continues to do so until a
higher mainID is used by file1.

I have tried various ways to return the second stream back to its
begining, so will check the lower numbers (they are sequential in it
[although some will be missing]).

Any help will be greatly appreciated, I'm ready to get the beers in,
I've spent a lot of time banging my head on the desk, going round in
circles.

If someone has a simple answer to this, and say what is so blatently
obvious (that I can't see it), then maybe I'll explain what I'm after as
a final program (it can wait for now).

All help and suggestions really are greatly appreciated.
Neil


--
StackemEvs
------------------------------------------------------------------------
StackemEvs's Profile: 1402
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=175267

Microsoft Office Help