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