Hi,
Thanks for the macro.
What I meant was that code 1 and code 2 uniquely defines a row so if
there is data item in the .csv file than it the macro should insert
the row in from of the code1 (column A) and code 2 (column B). Im my
spreadsheet I have 12 monthly tabs which all look exactly the same
setup of montly_totals.xls is as below:
Code 1 Code 2 YR MM CoName Dept_name Quantity $amount
A5 B5 c5 d5 e5 f5
g5 g6
Will I insert this macro as a module on every monthly tab in
montly_totals.xls ? And run it for each tab?
Will this macro insert any new data that it does not find in the
montly_totals.xls but finds in the .csv file?
thanks for your help
newby Dave
On Feb 17, 11:15 am, Paul Schreiner <schreiner_p...@att.net> wrote:
> 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 athttp://www.excel-macros.blogspot.com
> 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below
> linkhttp://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
