|Home » Forums » CAD » Message||Login|
You are not logged in. Consider these WOODWEB Member advantages:
Excel spreadsheet to manipulate x,y,z extent data for cut list?11/5
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.
could be done using VBA if you know your beans.....
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.
Create a form with the following components.
Common Dialogue name = Cdl1
Private Sub cmdLoad_Click()
With cdl1 'using common dialogue cdl1
.Filter = "CSV (*.csv)|*.csv"
.DialogTitle = "Load CSV file"
If .FileName "" Then
txtIn.LoadFile .FileName, 1 'load file into rtf text box
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
' destroy arrays
' basic bubble sort - shuffle sort might be quicker
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
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
iMax = iMax - 1
Loop Until Not bSwapped
'Returns the number of lines in a text string by counting the number of CRLF in string
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
'This function not used
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)
' Gets the number of fields from a csv line
If Len(Trim(strLine)) > 0 Then
GetNumFields = 1
GetNumFields = 0
For iLinePos = 1 To Len(Trim(strLine))
If Mid(strLine, iLinePos, 1) = "," Then
GetNumFields = GetNumFields + 1
Private Sub cmdProcess_Click()
Hey thanks! I'll try to purchase Office 2011 tomorrow, try it out, and let you know how it goes.
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...
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.
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!