Import a comma delimited file & match records

[Dave]

I am on Excel 2003,. I have a spreadsheet with 12 monthly tabs one for
every month of the year January, February … December.. This file is
called montly_totals.xls and is located in P:\DAM\WI_FTP. There are a
total of 200 accounts set up in each of these monthly tabs. Each of
these accounts starts with a code in A1 & B1 . These are 4 character
codes like below:

Code1 Code2

0845 MTRA
1016 BBBB
1017 LEGL
1605 1605
1605 OSLA
7777 0001
7777 0002


Every month I download a comma delimited file named as WI_JAN_2011,
WI_FEB_2011 and so on till WI_DEC_2011. This file is located in P:\DAM
\WI_FTP This file may contain 10 to 150 accounts with updated
information for that specific month. They also have the same codes as
above. The format of the comma delimited file is :

Code1,Code2,YR,MM,CoName,Dept_name,Quantity,$amount
0010,BBBB,11,01,Iron works ,Grills ,
000000130,0000003448635,

Currently every month I have to open each monthly tab and manually
copy and paste the correct Information from the comma delimited file
into the exact columns in my montly_totals.xls spreadsheet. The
columns I copy are : YR,MM,CoName,Dept_name,Quantity,$amount

I want a macro that would read this comma delimited file and insert
these new monthly updated values to the correct account numbers.
I was thinking that it could key on Code1 & Code2 fields. Once there
is a match between the codes i.e Code1+Code 2 in the montly_totals
spreadsheet and the comma delimited file than the macro could insert
the complete record (YR,MM,CoName,Dept_name,Quantity,$amount ) into
seperate cells in the montly_totals.xls.

Thanks in advance,
Dave

 

[Javed]

If possible pls send me one sample monthly file and one consolidated
file.

 

[GS]

It happens that Dave formulated :

Code1,Code2,YR,MM,CoName,Dept_name,Quantity,$amount

Is this the first line in the file. If so then I suggest using ADO to
grab the data into recordset and write the appropriate values to
your worksheet. Each recordset could be unique for each Code# pair, OR
you could process each record of a recordset containing all data in the
file.

If the field names are not the first line in the file then this can be
prepended (via VB file I/O functions) so ADO knows what these field
names are.

Just curious.., why do you not copy the 2 Code# when copying the other
data? How do you handle a new month entering the first data onto its
sheet?

 

[Dave]

Javed, I am sorry I can not load the file to big and contains info I
can not load.

Gary,

No that is not the first line of data I was trying to show what the
data lebels were.
the .csv file just contains row after row of data like
0010,BBBB,11,01,Iron works ,Grills ,
000000130,0000003448635
Each code pair is unique.
So far I have been manuall copying and pasting the data. I entered the
initial 200 codes by hand & now I want to be able to compare and
insert the updated values in a macro,

thanks in advance again.

It happens that Dave formulated :

Code1,Code2,YR,MM,CoName,Dept_name,Quantity,$amount

Is this the first line in the file. If so then I suggest using ADO to
grab the data into recordset and write the appropriate values to
your worksheet. Each recordset could be unique for each Code# pair, OR
you could process each record of a recordset containing all data in the
file.

If the field names are not the first line in the file then this can be
prepended (via VB file I/O functions) so ADO knows what these field
names are.

Just curious.., why do you not copy the 2 Code# when copying the other
data? How do you handle a new month entering the first data onto its
sheet?

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc