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
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
