Export Part #s to Excel from Solidworks

[bguenther]

Hi I am new to this forum.
There seem to be alot of nice smart people here so i was hoping that someone could help me with task i have been having problems with.
I have an assembly and i need a macro to export the part numbers and descriptions of all the parts to an excel sheet, I don't want to do it with a BOM because i have other code that i need to put in this macro as well. Could someone help me with this?

 

[handleman]

Try AssemblyBOM from

http://www.lennyworks.com/solidworks/default.asp?ID=20

-handleman, CSWP (The new, easy test)

 

[bguenther]

Thanks that is a great tool and i think i can get the code I need from it.

 

[bguenther]

I looked through it and Lenny is a very good programmer, I can't seem to get the right code out of there becuase it export from a list and I would just be exporting by travesing through the assemby. Any other example that you know of ? Thanks in advance.

 

[handleman]

I think SW 2009 is supposed to be able to generate a BOM directly in an assembly without having to make a drawing.

-handleman, CSWP (The new, easy test)

 

[bguenther]

That is interesting.
But what i actually want to do is have an assembly open traverse each part look for sheetmetal features and if it finds one get the perimeter and the number of holes and do some calculations based on that then export the Part number, the description, the size of the part and configuration name. I have figured out how to get most of that stuff, I just don't know how to send it to excel. So it won't really help me with the bill of materials.

 

[handleman]

faq559-1040 has a pretty simple interface to Excel.

-handleman, CSWP (The new, easy test)

 

[bguenther]

Thanks I hope I can get it to work, that is much easier to read for me. Do you have any idea how I assign each part to an index or to save its name to a variable so I can pass that to the export to excel function. I can go debug.print and it will print out all the part number to the immeadiate window but to hold the value is what i am troubling with. Any help is much appreciated.

 

[handleman]

You don't need any "export to Excel function". Just do it in the body of your code. Let me 'splain, using bits of code from the example I linked:

Dim exApp As Excel.Application
Dim sheet As Excel.Worksheet

These two lines tell your SW macro that it's going to be working with Excel.

Set exApp = New Excel.Application

This line starts up Excel. Now every time you refer to the exApp variable, it's connected to the Excel application.

There are a few "If Not ___ is Nothing Then" statements. They are just in there to make sure that the stuff that the macro is telling Excel to do is actually getting done.

exApp.Visible = True

This line makes sure that Excel is visible to the user. Otherwise, Excel will open in the background and you won't be able to interact with it - i.e. save the worksheet once you're done putting values in it.

exApp.Workbooks.Add

This line creates a new Excel Workbook inside the Excel Application.

Set sheet = exApp.ActiveSheet

This line sets the object variable "sheet" equal to the new workbook that was just created.

sheet.Cells(1, 2).Value = "X"

This line puts the text "X" into cell B2 (row 1, column 2) of the new worksheet. This is how you actually put your values in the worksheet - one cell at a time.

-handleman, CSWP (The new, easy test)

 

[bguenther]

Thanks that worked very well. The only thing is now it makes a new sheet for each part. I hate to have to make you lay it all out for me but you think you know of a way to make it put all the part nubmer in book ?
I like to put things like this in functions because then it is neater it you are searching for something it is alot easier to find. This is what i have and it make sence that it would make a new sheet each time.

Function ExportToExcel(ByVal compName As String)
Dim exApp As Excel.Application
Dim sheet As Excel.Worksheet
Set exApp = Excel.Application
exApp.Visible = True
exApp.Workbooks.Add
Set sheet = exApp.ActiveSheet
sheet.Cells(1, 2).Value = compName

End Function

 

[handleman]

In this case, making a separate function for exporting to Excel is probably not a good idea. You need to create the Excel workbook in your code immediately before you start traversing the design tree. So all the following lines go outside the loop:

Dim exApp As Excel.Application
Dim sheet As Excel.Worksheet
Set exApp = Excel.Application
exApp.Visible = True
exApp.Workbooks.Add
Set sheet = exApp.ActiveSheet

Then, inside your traversal loop, when you figure out what data you want to put into Excel you just use that one line

sheet.Cells([row],[col]).Value = [whatever]

Also, inside your loop you are going to have to keep track of the row/column where you want to put your data in the spreadsheet and change the numbers for [row] and [col] appropriately. Otherwise, you'll just keep overwriting the data in cell B1. (sorry, my previous post typo'd to B2 instead of B1)

-handleman, CSWP (The new, easy test)

 

[bguenther]

Perfect. Now it does just what I need thanks alot.

Dim exApp As Excel.Application
Dim sheet As Excel.Worksheet
Set exApp = Excel.Application
exApp.Visible = True
exApp.Workbooks.Add
Set sheet = exApp.ActiveSheet

Row = 1 + i
col = 1
sheet.Cells([Row], [col]).Value = [whatever]