Hi,
Please add my email id to the group.
Thanks!
Regards,
Kirpal
________________________________
From: excel-macros@googlegroups.com
[mailto:excel-macros@googlegroups.com] On Behalf Of Dave Bonallack
Sent: Friday, February 18, 2011 7:51 AM
To: excel-macros@googlegroups.com
Subject: RE: $$Excel-Macros$$ Import .csv & match records
I think someone is using my name in vain - unless there are two
Dave's in the world - and I don't know how that could be...
Dave.
________________________________
Date: Thu, 17 Feb 2011 09:15:24 -0800
From: schreiner_p...@att.net
Subject: Re: $$Excel-Macros$$ Import .csv & match records
To: excel-macros@googlegroups.com
Piece of cake...
I can probably write it in 20 minutes.
========================================================================
=
Option Explicit
Dim Col_Array_Code1, Col_Array_Code2, Col_Array_YR, Col_Array_MM
Dim Col_Array_CoName, Col_Array_Dept, Col_Array_Qty,
Col_Array_Amt
Dim Code1, Code2, YR, MM
Dim CoName, Dept, Qty, Amt
Dim fso
Public Const ForReading = 1, ForWriting = 2, ForAppending = 3
Sub ReadData()
Dim CSVFile, R, f, str, StrArray
CSVFile = "P:\DAM\WI_JAN_2011.csv"
Set_Defaults
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.OpenTextFile(CSVFile, ForReading)
Do While Not f.atendofstream
RecCnt = RecCnt + 1
If (RecCnt Mod 100 = 0) Then Application.StatusBar =
"Searching Commercial Archives for: " & MatNo & " : " & RecCnt
str = f.readline
StrArray = Split(str, ",")
Code1 = Trim(StrArray(Col_Array_Code1))
Code2 = Trim(StrArray(Col_Array_Code2))
YR = Trim(StrArray(Col_Array_YR))
MM = Trim(StrArray(Col_Array_MM))
CoName = Trim(StrArray(Col_Array_CoName))
Dept = Trim(StrArray(Col_Array_Dept))
Qty = Trim(StrArray(Col_Array_Qty))
Amt = Trim(StrArray(Col_Array_Amt))
Loop
End Sub
Sub Set_Defaults()
Col_Array_Code1 = 0
Col_Array_Code2 = 1
Col_Array_YR = 2
Col_Array_MM = 3
Col_Array_CoName = 4
Col_Array_Dept = 5
Col_Array_Qty = 6
Col_Array_Amt = 7
End Sub
========================================================================
===============
However, something you said makes me think it might not be as
easy to PLACE the data.
In the sheet for each month,
you have 200 "accounts"
and the combination of Code1 & Code2 defines a "record".
You want to read the .csv file and copy the records to the
appropriate "accounts"...
That implies that the sheets in the monthly_totals workbook
have a specific "layout" that you want to maintain.
I need to know what that layout is so that I can "find" the
appropriate "account".
I think I'm going to need a copy of the Monthly_totals.xls
workbook.
and, it would help if I had one or more of the .csv files.
Otherwise, I have to spend more time making up fake data than
actually writing code!
Paul
________________________________
From: Dave <davidstev...@gmail.com>
To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
Sent: Thu, February 17, 2011 9:32:15 AM
Subject: $$Excel-Macros$$ Import .csv & match records
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: They are sorted by Code 1.
Code 1 + Code 2 uniquely identify a record.
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,
(they are all on one line)
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 )
for
that match in the columns C, D,E,F,G, & going dow the rows.
If there is no match than a new record should be inserted
maintaing
the sort order.
Thanks in advance,
Dave
--
------------------------------------------------------------------------
----------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @
http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to
excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
--
------------------------------------------------------------------------
----------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @
http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
<http://www.excel-macros.blogspot.com/>
4. Learn VBA Macros at http://www.quickvba.blogspot.com
<http://www.quickvba.blogspot.com/>
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
<http://exceldailytip.blogspot.com/>
To post to this group, send email to
excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
--
------------------------------------------------------------------------
----------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @
http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to
excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
