Hi all,

Thanks for your replies.I have to develop an application which will
read a .DAT file.There are several hundred records in this file.Also I
get another excel file with adds and deletes.
There are records in this Excel file with matches in the DAT
file.Whereever the records are marked "deleted" in the excel file,I
have to find the similar records on the DAT file and delete those
records.The records marked "add" in the excel file have to be added or
appended to the DAT file.

The DAT file is as follows:

ImpMonth00100001234567X2000.000123400.0000
ImpMonth00100002334567A7000.000133400.0000
ImpMonth00100003234567B9000.000153400.0000
ImpMonth00100004234567F5000.000128400.0000


The Excel file is :

1284567X6 add 5640
2334567A7 del 1334
3234567B9 del 1534.

At present,I am doing it manually by importing the DAT file to a excel
file.I then import this file and the excel file with add/deletes to an
access database.I then have a query which compares both the tables and
deletes the matching records.Later I have a append query,which inserts
the records marked add.

But this is so tedious and i want to automate it to give it to the
users.

Any solutions?

Thanks

Roy

Roy - to get from the values in the Excel file to the values in the dat file
do you do the following to the Excel file data?
Add a prefix "ImpMonth0010000" to the entry in column A
To this add "000.000"
Then add column C (less any final dot)
Then add "00.0000"

ChkVal = "ImpMonth0010000" & Range("A1") & "000.000" & Range("C1") _
& "00.0000"

"Roy" wrote:

Hi all,

Thanks for your replies.I have to develop an application which will
read a .DAT file.There are several hundred records in this file.Also I
get another excel file with adds and deletes.
There are records in this Excel file with matches in the DAT
file.Whereever the records are marked "deleted" in the excel file,I
have to find the similar records on the DAT file and delete those
records.The records marked "add" in the excel file have to be added or
appended to the DAT file.

The DAT file is as follows:

ImpMonth00100001234567X2000.000123400.0000
ImpMonth00100002334567A7000.000133400.0000
ImpMonth00100003234567B9000.000153400.0000
ImpMonth00100004234567F5000.000128400.0000


The Excel file is :

1284567X6 add 5640
2334567A7 del 1334
3234567B9 del 1534.

At present,I am doing it manually by importing the DAT file to a excel
file.I then import this file and the excel file with add/deletes to an
access database.I then have a query which compares both the tables and
deletes the matching records.Later I have a append query,which inserts
the records marked add.

But this is so tedious and i want to automate it to give it to the
users.

Any solutions?

Thanks

Roy

this code will work. It may need some minor modifications. I used to
worksheets DAT and query. Change the names as necessary. The code uses 3
columns on the query worksheet A - C and uses column A on the data sheet.


Sub add_delete()
Dim FieldA As String
Dim FieldC As String

'do deletes then do adds
With Sheets("query")
QueryRowCount = 1
Do While .Range("A" & QueryRowCount) < ""
If .Range("B" & QueryRowCount) = "del" Then
FieldA = .Range("A" & QueryRowCount)
FieldC = .Range("C" & QueryRowCount)
'search for records
With Sheets("DAT")
DatRowCount = 1
Do While .Range("A" & DatRowCount) < ""
data = .Range("A" & DatRowCount)
Field1 = Left(data, 15)
Field2 = Mid(data, 16, 9)
Field3 = Mid(data, 25, 7)
Field4 = Mid(data, 32, 4)
Field5 = Mid(data, 36, 7)
If (FieldA = Field2) And _
(FieldC = Field4) Then

.Rows(DatRowCount).Delete
Else
DatRowCount = DatRowCount + 1
End If
Loop
End With
End If
QueryRowCount = QueryRowCount + 1
Loop
End With

'do the adds
With Sheets("DAT")
'get fields to use for filling in data
'base added data on last line in data file
DatLastRow = .Cells(Rows.Count, "A").End(xlUp).Row
data = .Range("A" & DatLastRow)
Field1 = Left(data, 15)
Field3 = Mid(data, 25, 7)
Field5 = Mid(data, 36, 7)
End With
DatRowCount = DatLastRow + 1
With Sheets("query")
QueryRowCount = 1
Do While .Range("A" & QueryRowCount) < ""
If .Range("B" & QueryRowCount) = "del" Then
FieldA = .Range("A" & QueryRowCount)
FieldC = .Range("C" & QueryRowCount)

data = Field1 & FieldA & Field3 & _
FieldC & Field5
With Sheets("DAT")
.Range("A" & DatRowCount) = data
End With
DatRowCount = DatRowCount + 1
End If
QueryRowCount = QueryRowCount + 1
Loop
End With

End Sub


"Gleam" wrote:

Roy - to get from the values in the Excel file to the values in the dat file
do you do the following to the Excel file data?
Add a prefix "ImpMonth0010000" to the entry in column A
To this add "000.000"
Then add column C (less any final dot)
Then add "00.0000"

ChkVal = "ImpMonth0010000" & Range("A1") & "000.000" & Range("C1") _
& "00.0000"

"Roy" wrote:

Hi all,

Thanks for your replies.I have to develop an application which will
read a .DAT file.There are several hundred records in this file.Also I
get another excel file with adds and deletes.
There are records in this Excel file with matches in the DAT
file.Whereever the records are marked "deleted" in the excel file,I
have to find the similar records on the DAT file and delete those
records.The records marked "add" in the excel file have to be added or
appended to the DAT file.

The DAT file is as follows:

ImpMonth00100001234567X2000.000123400.0000
ImpMonth00100002334567A7000.000133400.0000
ImpMonth00100003234567B9000.000153400.0000
ImpMonth00100004234567F5000.000128400.0000


The Excel file is :

1284567X6 add 5640
2334567A7 del 1334
3234567B9 del 1534.

At present,I am doing it manually by importing the DAT file to a excel
file.I then import this file and the excel file with add/deletes to an
access database.I then have a query which compares both the tables and
deletes the matching records.Later I have a append query,which inserts
the records marked add.

But this is so tedious and i want to automate it to give it to the
users.

Any solutions?

Thanks

Roy