Hi,

I am in a slight pickle here. I am fairly unexperienced to VBA so bear with me.
Scenario : I have a 2 text files with specific lines of data / same format, call them "LINO767627" (for example, different combinations of numbers). The first txt file is my source of data (run data against) and the second is my weekly updated txt file of data. So there is a possibility that my weekly txt file may have identical lines - "LINO.........." that appear in my source text file.

If these lines do infact reappear or are duplicated, I need these lines to be sent to an output file - output.txt. The code I have attached is used for a previous module I have used in the past. Can anybody shed any light. I think the main thing is understanding how to look / analyze 2 text files at the same time ?? If that makes sense. Any help or pointers would be greatly appreciated.

Regards, Steve


Option Compare Database
Option Explicit

Public Function Removezero(infile As String, outfile As String)

Dim InputVariable As Integer
Dim CompX As Integer
Dim OutputVariable As Integer
Dim Inputstring As String
Dim Inputcomp As String
Dim Outputstring As String


InputVariable = FreeFile()
Open infile For Input As InputVariable
CompX = FreeFile()
Open infile For Input As CompX
OutputVariable = FreeFile()
Open outfile For Output As OutputVariable

Do Until EOF(InputVariable)

Line Input #InputVariable, Inputstring

Do Until EOF(CompX)

Line Input #CompX, Inputcomp

If Left(Inputcomp, 4) = "LIN0" And Mid(Inputcomp, 17, 32) = "01020304050809102526272829303132" And Left(Inputstring, 4) = "LIN0" And Mid(Inputstring, 17, 32) = "01020304050809102526272829303132" Then
Outputstring = Inputstring
Else
Outputstring = "Do nothing"
End If

Print #OutputVariable, Outputstring

Loop

Loop

Close #OutputVariable
Close #InputVariable

End Function

Never mind VBA. Use a series of queries to get where you want to go.

Step 1) open the Query Designer and add the static text file. Close the dialog box. Double-click on the star (*) in the top of the field list to select all fields, and then add a new field to the QBE grid, called perhaps TestStr, as follows:
Save the query as perhaps qryTSSub.

2) now do the exact same thing with the input text file, in a new query. Select * and then add a new field with the same info as before, but replacing the file name as appropriate. Save the query as perhaps qryITSub.

3) make a 3rd query. In the "Queries" tab of the input dialog box, select first the qryTSSub and then the qryITSub. Join the two new fields with an inner join.

Before you do anything else, run the query to make sure it gives you the correct results. Assuming it does,

4) make that 3rd query into a make-table (or append, as appropriate) query. If the select query yields incorrect results, you'll need to do some troubleshooting before changing the query type.

Good luck,

Sam

Hi Sam,

Thanks for your quick reply, I must say I have tried your technique and IT works. Just a few quick questions.....

I have set up the 3 queries (upto and including point 3) and all the data that is duped in both files is outputted to screen. I should have mentioned that the files both have other lines of data, see below as an example :

CPNTCS…………..
LIN012N 123445464748
LIN013N 01050617
CPNLPL……..
LIN012N 01020304050607
LIN023N 1213141516
LIN034N 121314
LIN044N 1213

So the output displays all the data where I am just after the LIN duped data. The code just looks at, for example left(4) and mid(17,32) but does not mention exactly what you are looking for. Can you specify the code to look at e.g. 4 = "LIN0" etc? Sorry I should have mentioned this earlier.

Cheers, Steve

You did mention it earlier. I deliberately ignored it to avoid biting off more than can be chewed.

Going back to the first query, qryTSSub, open it in design mode. Add a new field (at the end of the QBE grid is fine), as follows: In the "Criteria" line of that field add the following: Unclick the check box (it doesn't need to be displayed), and save the query again.

Is it OK now?

Sam