do have two sets of files
1. abc.csv,xyz.csv,pqr.csv....till n (Master files); format as below:
Date Price
02-11-07 168
01-11-07 165

2. Trans.xls; format as below:
File Date Price
abc.csv 02-11-07 169
xyz.csv 02-11-07 251

Now i want as follows:
abc/xyz/pqr.csv is updated from trans.xls everytime i run the code. it must
look whether data relevant to "Date" exist or not? If Exist write over it and
save & close the .csv files; if not exist insert a line after first row and
put the data there.

Both file have different directory. i can put both files in same directory.

Any help would be greatly appreciated...

--
Nikesh

this code will search for all CSV files in the directory CSVPath and update
the price based on the data in trans.xls. this code should be place in the
trans.xls file. I can modify the code to run from another workbook if
necessary.

I assumed the sheet name in Trans.xls is sheet1. change if it is different
in two places. the sheet name of the csv files are not necessary to change
because they are the filename with out the extension.

Sub update_csv()

Const CSVPath = "c:\temp\test"

With ThisWorkbook.Sheets("Sheet1")
TransLastRow = .Cells(Rows.Count, "A").End(xlUp).Row
End With

First = True
Do
If First = True Then
Filename = Dir(CSVPath & "\*.csv")
First = False
Else
Filename = Dir()
End If

If Filename < "" Then
Workbooks.Open Filename:=CSVPath & "\" & Filename
Set CSVbook = ActiveWorkbook
sheetname = Left(Filename, InStr(Filename, ".") - 1)

With ThisWorkbook.Sheets("Sheet1")
For TranRowCount = 2 To TransLastRow
If .Range("A" & TranRowCount) = Filename Then
SearchDate = .Range("B" & TranRowCount)
Price = .Range("C" & TranRowCount)
With CSVbook.Sheets(sheetname)
CSVLastRow = _
.Cells(Rows.Count, "A").End(xlUp).Row
Set CSVSearch = .Range("A2:A" & CSVLastRow)
Set c = CSVSearch.Find(what:=SearchDate, _
LookIn:=xlValues)
If Not c Is Nothing Then
.Range("B" & c.Row) = Price
Else
.Rows(2).Insert
.Range("A2") = Date
.Range("B2") = Price
End If
End With
End If
Next TranRowCount

End With

CSVbook.Close SaveChanges:=True
End If
Loop While Filename < ""

End Sub


"Nikesh Goyal" wrote:

do have two sets of files
1. abc.csv,xyz.csv,pqr.csv....till n (Master files); format as below:
Date Price
02-11-07 168
01-11-07 165

2. Trans.xls; format as below:
File Date Price
abc.csv 02-11-07 169
xyz.csv 02-11-07 251

Now i want as follows:
abc/xyz/pqr.csv is updated from trans.xls everytime i run the code. it must
look whether data relevant to "Date" exist or not? If Exist write over it and
save & close the .csv files; if not exist insert a line after first row and
put the data there.

Both file have different directory. i can put both files in same directory.

Any help would be greatly appreciated...

--
Nikesh

I made a small error. I put todays date in the CSV file instead of the date
in the trans.xls. change one line of code

from
..Range("A2") = Date
to
..Range("A2") = SearchDate

"Nikesh Goyal" wrote:

do have two sets of files
1. abc.csv,xyz.csv,pqr.csv....till n (Master files); format as below:
Date Price
02-11-07 168
01-11-07 165

2. Trans.xls; format as below:
File Date Price
abc.csv 02-11-07 169
xyz.csv 02-11-07 251

Now i want as follows:
abc/xyz/pqr.csv is updated from trans.xls everytime i run the code. it must
look whether data relevant to "Date" exist or not? If Exist write over it and
save & close the .csv files; if not exist insert a line after first row and
put the data there.

Both file have different directory. i can put both files in same directory.

Any help would be greatly appreciated...

--
Nikesh