dan_g8 Posted October 2, 2014 Share Posted October 2, 2014 Hi, I currently have a text file that contains a list of materials first part of the line is the quantity the next 7 characters is the material code. It looks like this: 1000No. Red Brk 10No. Oak Dor 30No. Flr Brd 2100No. Red Brk 15No. Oak Dor 45No. Flr Brd 900No. Red Brk 5No. Oak Dor 40No. Flr Brd I would like to be able to be able to consolidate the list down adding all of the quantities together so that the list would turn into. 4000No. Red Brk 30No. Oak Dor 115No. Flr Brd I originally tried looping through the file line by line checking a material code then when it gets to the end of the file it goes back to the start and checks for another material code , this worked but was very slow as it was looping through the file a couple of hundred times! anyone got any pointers for a more efficient process in VBA? Dan Quote Link to comment Share on other sites More sharing options...
Tyke Posted October 2, 2014 Share Posted October 2, 2014 Try creating a dynamic array and then read in your file line by line. Parse out the quantity and code and store in two variables, check if the code already exists in your array, if so add the quantity to that already in the array, if not add a new item to the array using "Redim Preserve" and insert the quantity. When you've done reading the file write the data from the array into a new file. This way you will only loop through your original file once. Quote Link to comment Share on other sites More sharing options...
Tyke Posted October 2, 2014 Share Posted October 2, 2014 (edited) Hi Dan, Have you had time to try my suggestion out yet? I had a little bit of time this afternoon and cobbled an example together for you. It's basic with no bells and whistles and no error trapping but it works. You will need to put in the path to your files or incorporate file opening and saving dialogs. Dim iQuant As Integer Dim sQuant As String Dim sCode As String Dim ParseLine As Variant Dim sTemp As String Dim iTemp As Integer Dim index As Integer Dim c As Integer Dim sLine As String Dim sOutLine As String Dim MyList() As Variant Dim sOrgFile As String Dim sNewFile As String Dim iFileNum As Integer Dim bFound As Boolean ' initialise variables index = 0 c = 0 sLine = "" sOutLine = "" sTemp = "" iTemp = 0 sOrgFile = "<PATH>\Consolidate file\original.txt" sNewFile = "<PATH>\Consolidate file\new.txt" iFileNum = FreeFile bFound = False ' open original file to read it in Open (sOrgFile) For Input As #iFileNum Do While Not EOF(iFileNum) Line Input #iFileNum, sLine sLine = Trim(sLine) ParseLine = Split(sLine, ".", -1, vbTextCompare) sQuant = CStr(ParseLine(0)) sQuant = Mid(sQuant, 1, Len(sQuant) - 2) iQuant = CStr(sQuant) sCode = CStr(ParseLine(1)) sCode = Trim(sCode) ' for first line read in If index = 0 Then ReDim Preserve MyList(1, index) ' add first entry to list MyList(0, index) = sCode MyList(1, index) = iQuant bFound = True index = index + 1 Else ' loop to see if code already exists For c = 0 To index - 1 sTemp = MyList(0, c) If sTemp = sCode Then ' add quantity iTemp = MyList(1, c) iQuant = iQuant + iTemp MyList(1, c) = iQuant bFound = True Exit For End If Next End If If bFound = False Then ReDim Preserve MyList(1, index) ' add a new entry to list MyList(0, index) = sCode MyList(1, index) = iQuant index = index + 1 End If bFound = False Loop Close #iFileNum ' open new file to output to it iFileNum = FreeFile Open (sNewFile) For Output As #iFileNum iQuant = 0: sQuant = "": sCode = "": sOutLine = "" ' loop through array and write to file For c = 0 To UBound(MyList) + 1 iQuant = MyList(1, c) sQuant = CStr(iQuant) sCode = MyList(0, c) sOutLine = sQuant & "No. " & sCode Print #iFileNum, sOutLine Next Close #iFileNum You don't need to implement this in AutoCAD VBA you could write a standalone app as it's pure data handling. You will need to change the integer variables to type long to work with larger quantities. I just ran it on a 400 item original list and it completed in less than one second. HTH Edited October 2, 2014 by Tyke updated variable declarations for large quantities Quote Link to comment Share on other sites More sharing options...
BIGAL Posted October 3, 2014 Share Posted October 3, 2014 Maybe an alternative swap the qty and name then just do a sort on the data this way it will still be line by line but you just read a line adding up as you go and once name changes its total for that group. FlrBrd 30No. FlrBrd 45No. FlrBrd 40No. OakDor 10No. OakDor 15No. OakDor 5No. RedBrk 1000No. RedBrk 2100No. RedBrk900No. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.