Computer Aided Design

You are not logged in. [ Login ] Why log in
(NOTE: Login is not required to post)

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

11/5/13       
Gavin

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

11/6/13       #2: Excel spreadsheet to manipulate x,y ...
Splinter

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

11/6/13       #3: Excel spreadsheet to manipulate x,y ...
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.

11/6/13       #4: Excel spreadsheet to manipulate x,y ...
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.

11/6/13       #5: Excel spreadsheet to manipulate x,y ...
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

11/7/13       #6: Excel spreadsheet to manipulate x,y ...
Gavin

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

11/7/13       #7: Excel spreadsheet to manipulate x,y ...
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

11/7/13       #8: Excel spreadsheet to manipulate x,y ...
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...

11/7/13       #9: Excel spreadsheet to manipulate x,y ...
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!

11/8/13       #10: Excel spreadsheet to manipulate x,y ...
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.


Post a Response
  • Notify me of responses to this thread
  • Subscribe to email updates on this Forum
  • 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

    Buy & Sell Exchanges | Forums | Galleries | Site Map

    FORUM GUIDELINES: Please review the guidelines below before posting at WOODWEB's Interactive Message Boards (return to top)

  • WOODWEB is a professional industrial woodworking site. Hobbyist and homeowner woodworking questions are inappropriate.
  • Messages should be kept reasonably short and on topic, relating to the focus of the forum. Responses should relate to the original question.
  • A valid email return address must be included with each message.
  • Advertising is inappropriate. The only exceptions are the Classified Ads Exchange, Machinery Exchange, Lumber Exchange, and Job Opportunities and Services Exchange. When posting listings in these areas, review the posting instructions carefully.
  • Subject lines may be edited for length and clarity.
  • "Cross posting" is not permitted. Choose the best forum for your question, and post your question at one forum only.
  • Messages requesting private responses will be removed - Forums are designed to provide information and assistance for all of our visitors. Private response requests are appropriate at WOODWEB's Exchanges and Job Opportunities and Services.
  • Messages that accuse businesses or individuals of alleged negative actions or behavior are inappropriate since WOODWEB is unable to verify or substantiate the claims.
  • Posts with the intent of soliciting answers to surveys are not appropriate. Contact WOODWEB for more information on initiating a survey.
  • Excessive forum participation by an individual upsets the balance of a healthy forum atmosphere. Individuals who excessively post responses containing marginal content will be considered repeat forum abusers.
  • Responses that initiate or support inappropriate and off-topic discussion of general politics detract from the professional woodworking focus of WOODWEB, and will be removed.
  • Participants are encouraged to use their real name when posting. Intentionally using another persons name is prohibited, and posts of this nature will be removed at WOODWEB's discretion.
  • Comments, questions, or criticisms regarding Forum policies should be directed to WOODWEB's Systems Administrator
    (return to top).

    Carefully review your message before clicking on the "Send Message" button - you will not be able to revise the message once it has been sent.

    You will be notified of responses to the message(s) you posted via email. Be sure to enter your email address correctly.

    WOODWEB's forums are a highly regarded resource for professional woodworkers. Messages and responses that are crafted in a professional and civil manner strengthen this resource. Messages that do not reflect a professional tone reduce the value of our forums.

    Messages are inappropriate when their content: is deemed libelous in nature or is based on rumor, fails to meet basic standards of decorum, contains blatant advertising or inappropriate emphasis on self promotion (return to top).

    Libel:   Posts which defame an individual or organization, or employ a tone which can be viewed as malicious in nature. Words, pictures, or cartoons which expose a person or organization to public hatred, shame, disgrace, or ridicule, or induce an ill opinion of a person or organization, are libelous.

    Improper Decorum:   Posts which are profane, inciting, disrespectful or uncivil in tone, or maliciously worded. This also includes the venting of unsubstantiated opinions. Such messages do little to illuminate a given topic, and often have the opposite effect. Constructive criticism is acceptable (return to top).

    Advertising:   The purpose of WOODWEB Forums is to provide answers, not an advertising venue. Companies participating in a Forum discussion should provide specific answers to posted questions. WOODWEB suggests that businesses include an appropriately crafted signature in order to identify their company. A well meaning post that seems to be on-topic but contains a product reference may do your business more harm than good in the Forum environment. Forum users may perceive your references to specific products as unsolicited advertising (spam) and consciously avoid your web site or services. A well-crafted signature is an appropriate way to advertise your services that will not offend potential customers. Signatures should be limited to 4-6 lines, and may contain information that identifies the type of business you're in, your URL and email address (return to top).

    Repeated Forum Abuse: Forum participants who repeatedly fail to follow WOODWEB's Forum Guidelines may encounter difficulty when attempting to post messages.

    There are often situations when the original message asks for opinions: "What is the best widget for my type of shop?". To a certain extent, the person posting the message is responsible for including specific questions within the message. An open ended question (like the one above) invites responses that may read as sales pitches. WOODWEB suggests that companies responding to such a question provide detailed and substantive replies rather than responses that read as a one-sided product promotion. It has been WOODWEB's experience that substantive responses are held in higher regard by our readers (return to top).

    The staff of WOODWEB assume no responsibility for the accuracy, content, or outcome of any posting transmitted at WOODWEB's Message Boards. Participants should undertake the use of machinery, materials and methods discussed at WOODWEB's Message Boards after considerate evaluation, and at their own risk. WOODWEB reserves the right to delete any messages it deems inappropriate. (return to top)


  • Forum Posting Help
    Your Name The name you enter in this field will be the name that appears with your post or response (return to form).
    Your Website Personal or business website links must point to the author's website. Inappropriate links will be removed without notice, and at WOODWEB's sole discretion. WOODWEB reserves the right to delete any messages with links it deems inappropriate. (return to form)
    E-Mail Address Your e-mail address will not be publicly viewable. Forum participants will be able to contact you using a contact link (included with your post) that is substituted for your actual address. You must include a valid email address in this field. (return to form)
    Subject Subject may be edited for length and clarity. Subject lines should provide an indication of the content of your post. (return to form)
    Thread Related Link and Image Guidelines Thread Related Links posted at WOODWEB's Forums and Exchanges should point to locations that provide supporting information for the topic being discussed in the current message thread. The purpose of WOODWEB Forums is to provide answers, not to serve as an advertising venue. A Thread Related Link that directs visitors to an area with inappropriate content will be removed. WOODWEB reserves the right to delete any messages with links or images it deems inappropriate. (return to form)
    Thread Related File Uploads Thread Related Files posted at WOODWEB's Forums and Exchanges should provide supporting information for the topic being discussed in the current message thread. Video Files: acceptable video formats are: .MOV .AVI .WMV .MPEG .MPG .MP4 (Image Upload Tips)   If you encounter any difficulty when uploading video files, E-mail WOODWEB for assistance. The purpose of WOODWEB Forums is to provide answers, not to serve as an advertising venue. A Thread Related File that contains inappropriate content will be removed, and uploaded files that are not directly related to the message thread will be removed. WOODWEB reserves the right to delete any messages with links, files, or images it deems inappropriate. (return to form)