Excel spreadsheet to manipulate x,y,z extent data for cut list?

-- 1 --

I am a novice at using spreadsheets, and was wondering if the following is possible with a macro or otherwise:

I can export a csv file of x,y,z 'extents' of acis objects from my CAD program. This is simply the distance, in each axis, that an object occupies. The x,y,z extents for an object obviously differ depending on how the object is oriented. For instance, an object that's 3 units by 2 units by 1 unit would generate x,y,z, extents of 1,2,3 or 2,3,1 or 2,1,3 or 3,1,2... etc depending on how it's oriented in 3d space. Now, is it possible in excel to import this information into length, width, and thickness columns (the information would be initially all mixed-up), and have excel comb the records, and place the largest number in length, the second largest in width, and the smallest in thickness?

Furthermore, is it possible for excel to find duplicate records, toss out the second and subsequent records, and keep track of the number of duplicate records, then adding that number into a 'quantity' column for the original part? I hope I'm explaining myself well enough. I realize this wouldn't work perfectly, as there are some parts that are larger in width than length, but for my purposes it would be a vast improvement.

Thanks

Splinter

could be done using VBA if you know your beans.....

Gavin

Okay thanks Splinter. If you think it's possible, I'll buy excel 2011 (I have excel 2008 mac, which doesn't have macros). I didn't realize the macros involved writing code. I took some evening classes in visual basic years ago, so I'll give it a shot (once in a while I also write handy little BASIC programs for my old-but-trusty Texas Instruments calculator - sort of like the original 'Apps'!)

I also got a private message suggestion to check out Smartlister. This would be a great idea, but it's my understanding it requires a full version of AutoCAD, plus I have an apple computer, so it would be an expensive solution.

Thanks again, and I'll post if I have any success.

Splinter

Hey Gavin
pm me and i will send you a link to some code i wrote last night that may help you get started.

Splinter

Create a form with the following components.

Common Dialogue name = Cdl1
Command button 1 name= cmdLoad
Command button 2 name= cmdProcess
RTF Text box Name = txtIn
Std Text box Name = txtOut
Set both text box's multiline value to True
Copy following code into form

Option Explicit

Private Sub cmdLoad_Click()
' uses the "Load file" method of a RTF text box, really should open the file directly

With cdl1 'using common dialogue cdl1

.Filter = "CSV (*.csv)|*.csv"

.DialogTitle = "Load CSV file"

.ShowOpen

If .FileName "" Then

txtIn.LoadFile .FileName, 1 'load file into rtf text box

End If

End With
End Sub

Function SortList(strList As String) As String

Dim i As Long, strLine As String, strNewLine As String

Dim strText() As String, strArray() As String

Dim strNewString As String

strText = Split(txtIn.Text, vbCrLf) 'put each line into array

txtOut.Text = "" ' clear output txt box

For i = 0 To GetLineCount(strList) - 1

strLine = strText(i)

strArray = Split(strLine, ",") 'create array using fields from current line

SortArray strArray 'sort values in line

strNewString = Join(strArray, ",")

txtOut.Text = txtOut.Text & strNewString & vbCrLf

Next i

' destroy arrays

Erase strText

Erase strArray
End Function

' basic bubble sort - shuffle sort might be quicker
Public Sub SortArray(ByRef vArray As Variant)

Dim i As Long

Dim iMin As Long 'lower boundary of array

Dim iMax As Long 'upper boundary of array

Dim varSwap As Variant

Dim bSwapped As Boolean

iMin = LBound(vArray)

iMax = UBound(vArray) - 1

Do

bSwapped = False

For i = iMin To iMax

If Val(vArray(i)) > Val(vArray(i + 1)) Then

varSwap = vArray(i)

vArray(i) = vArray(i + 1)

vArray(i + 1) = varSwap

bSwapped = True

End If

Next

iMax = iMax - 1

Loop Until Not bSwapped
End Sub

'Returns the number of lines in a text string by counting the number of CRLF in string
Function GetLineCount(strText As String) As Long

Dim i As Long, iLineCount As Long, iPos As Long

For i = 1 To Len(strText)

If Mid(strText, i, 2) = vbCrLf Then

GetLineCount = GetLineCount + 1

End If

Next i
End Function

'This function not used
Function GetCSV(strCSV As String, iField As Integer, Optional sep As String = ",")
Dim iFieldNum As Integer, char As String, iPos As Integer

iFieldNum = 0

For iPos = 1 To Len(strCSV)

If InStr(iPos, strCSV, sep, vbTextCompare) = iPos Then

iFieldNum = iFieldNum + 1

If iField = iFieldNum Then

GetCSV = Mid(strCSV, InStrRev(strCSV, sep, iPos - 1, vbTextCompare) + 1, iPos - InStrRev(strCSV, sep, iPos - 1, vbTextCompare) - 1)

Exit Function

End If

End If

Next iPos
End Function

' Gets the number of fields from a csv line
Function GetNumFields(strLine As String) As Integer
Dim iFieldCount As Integer, iLinePos As Integer

If Len(Trim(strLine)) > 0 Then

GetNumFields = 1

Else

GetNumFields = 0

Exit Function

End If

For iLinePos = 1 To Len(Trim(strLine))

If Mid(strLine, iLinePos, 1) = "," Then

GetNumFields = GetNumFields + 1

End If

Next iLinePos
End Function

Private Sub cmdProcess_Click()

SortList txtIn.Text
End Sub

Gavin

Hey thanks! I'll try to purchase Office 2011 tomorrow, try it out, and let you know how it goes.

Splinter

Hey Gavin
it aint VBA - its VB6
but i thought you might be able to get some ideas from what i posted
If you like, i can pursue this a little further and make you a stand alone program to process your CSV files

David Wishengrad

xyz only means something if we are sure x, y,z always represent length width or thickness.

This means that parts need to be properly orientated first before issuing the getboundingbox method in vba/vb6/c# or c++, etc., unless the method generates the values needed and the current boundingbox values and then to be adjusted based on the current part's xyz transformation(rotation). In theory it seems possible and faster, but in practical application transforming the part (or a copy if the part) itself first, and then calling the method for boundingbox is the clear solution.

The issue here is that, unlike Sketchup, built on a gaming engine that utilizes 3D matrices upfront and readily available for a more cpu efficient method of transformation of entities for faster graphical display (making everything move around the operator's viewpoint as opposed to making the view update based on moving through an environment) either of with is indistinguishable in the operator's view, AutoCAD does not have this up front and ready to be taken advantage of. AutoCAD is robust enough with API support to add such a feature in many ways. It's already there in obscure and rarely used DXF codes, but the code needs to be written to call the data and process it. Quite literally, you must create the 3D gaming engine code to create, call, and modify 3D matrices and add it to AutoCAD.

Creating entities in AutoCAD from a spreadsheet is very straight forward in AutoCAD and you can find many pieces of sample code on many sites to work from.

VBA/VB6 is pretty old too. Not to mention are Microsoft product made for Windows. Yet, people don't own their Windows OS, they own a license to use it, even though it says Buy Windows, they are not really selling Windows, nor does it appear they have for sale through the click of that link on this page http://windows.microsoft.com/en-us/windows/buy Windows for actual sale. It's only opportunities to buy "licenses to use and own", not actually Windows. http://legal-dictionary.thefreedictionary.com/fraud

As for Apple...
http://www.bloomberg.com/slideshow/2012-03-30/inside-apple-s-foxconn-factory.html#
slide9

Driving a drawing from a spreadsheet presents issues of a lack of visibility of the 3D model while your eyes are buried under cells of data filled with formulas with many of them outside the screens view or on other worksheets, and or in other files. A person has a hard time remembering what they wrote 2 weeks later and it's not all there in clear view to re-digest. Technically, it works, but can very easily get very cumbersome. OpenOffice is free and has spreadsheets that can drive AutoCAD too. You can do it from a txt(csv) file like Splinter showed. Create your formula to create data, then export the data to txt/csv file and read it in in AutoCAD. The API is filled with examples of how to generate objects. It's a lot of code to write to only have Autodesk tell you that it's no longer supported in their next version and have to update it based on their API changes.

The importance of a cut list outweighs most other reasons to use CAD in the first place, but sales.
Some stuff for thinking about...

Gavin

David - I'm not trying to create a program to interact directly with a CAD program, but simply to move information around in database fields or spreadsheet columns that the CAD program has already spit out. I agree that the concept is flawed in the assumption that the largest dimension of x,y,z represents length, the second largest width, and the smallest thickness - but for my needs, I can manually adjust the odd part that's wider than it is long. If you think about it, this situation almost never occurs with solid wood, and in sheet goods, only occurs once in a while with sheets having grain.

Also, I'm not trying to create objects from a spreadsheet. I'm sure this can be done, but I would be happy to just model things, and then present the objects dimensions in a usable manner.

I'm a little confused by the rest of your post, but I don't use Sketchup, and only use AutoCAD because I'm taking courses in it right now and have an educational version.

Splinter - thanks again. A standalone program is what I was originally thinking of, but thought that the spreadsheet route might be simpler. I have no clue how to approach a standalone program for Mac, as it's all in C or Java. Maybe I'll show my father, a retired programmer, your code and see if he can reproduce it in Java - maybe it would motivate him to give me a hand!

Splinter

Hey Gavin
Finished a utility to sort csv line and sum the qtys of identical ones.
If you want to pm me your email address I will send it all to you.

1 --

To receive email notification of additions to this forum thread, enter your name and email address, and then click the "Keep Me Posted" button below. Please Note: If you have posted a message or response, do not submit this request ... you are already signed up to receive notification!
Your Name:
E-Mail Address:
Enter the correct numbers into the field below:
Date of your Birth:	Return to top of page