Hello,
i need a macro in VBA who export the structurated part list in xls separated by comme with only two cullom in order: Quantity and Part Number.
This .xls file mus be saved in the same folder where the .iam file is saved and with the same name of the .iam file.
Can someone help me to make this marco????
Hi,
about sorting CSV file, it is nothing Inventor API can do. Probably the posts below help?
http://www.cadtutor.net/forum/showthread.php?40843-sort-ascending-using-VBA
http://www.vbdotnetforums.com/database-general-discussion/49212-sort-csv-file-specified-column.html
Thank you very much for your replay, I think I modify manualy the csv exported with my VBA code.
Can I import a xml template in the structurated part list with VBA?????
I use one template who set automaticly the column in the structurated part list!
My approach would probably be to populate an excel worksheet by iterating throught the appropriate BOM items then plucking out the part number and quanitites. Once it is in excel you can use then sort the data using the vba commands for excel.
You will need to reference excel in the inventor VBA to be able to do it.
Then you will have to start an instance of excel. Iterate through the cells as you copy the data from the BOM.
Sorry I cant help any more I dont have inventor on my laptop.
Dan
There is not way to do this from VBA? I need only open the corect template for the structurated part list in order to set the two column!??
Ok so yes you can work with excel from Inventor VBA.
{
Dim excel_app As Excel.Application
' Create the Excel application.
Set excel_app = CreateObject("Excel.Application")
' Uncomment this line to make Excel visible.
'excel_app.Visible = False
}
This little tiny bit of code will make it so you can edit a spreadsheet
{
excel_app.Workbooks.Open (filepath)
excel_app.Range("A2").Select
}
Basically you put excel_app in front of your standard excel vba code to make them work from inventor vba.
Try This it should get you somewhere near where you need to be.
Sub BOM_Export() Dim oApp As Application Set oApp = ThisApplication If oApp.ActiveDocument.DocumentType = kAssemblyDocumentObject Then Dim oAssyDoc As AssemblyDocument Set oAssyDoc = oApp.ActiveDocument Dim oAssyCompDef As AssemblyComponentDefinition Set oAssyCompDef = oAssyDoc.ComponentDefinition Dim excel_app As Excel.Application ' Create the Excel application. Set excel_app = CreateObject("Excel.Application") ' Uncomment this line to make Excel visible. excel_app.Visible = True 'Create new workbook Call excel_app.Workbooks.Add Dim oBomR As BOMRow Dim oBOMPartNo As String With excel_app .Range("A1").Select .ActiveCell.Value = "Quantity" .Range("B1").Select .ActiveCell.Value = "Part Number" 'Iterate through parts only BOM View Dim i As Integer For i = 1 To oAssyCompDef.BOM.BOMViews(3).BOMRows.Count 'Set oBomR to current BOM Row Set oBomR = oAssyCompDef.BOM.BOMViews(3).BOMRows(i) 'Get Current Row part number from part oBOMPartNo = oBomR.ComponentDefinitions(1).Document.PropertySets(3).ItemByPropId(5).Value 'Write values to spreadsheet .Range("A" & i + 1).Select .ActiveCell.Value = oBomR.TotalQuantity 'Quantity value .Range("B" & i + 1).Select .ActiveCell.Value = oBOMPartNo Next i End With Else Exit Sub End If End Sub
Dont forget toset your reference to excel in the VBA. Basically go to "Tools", "References" then look for "Microsoft Excel 14.0 Object Library". Without it this wont work.
Thank you dano0310, i try this code but there is error and i donn't konw what is the problem.
Hi Zsolt,
Two things:
1) I cannot see the csv file you mention attached anywhere
2) The errer.jpg that is supposed to show the error you've run into does not actually show any error. It simply shows the line that VBA is about to execute, so it gives no clue about what problem you are running into
Also, it might be worth first just printing everything into the "Immediate" window of VBA to see what is going on.
E.g. :
Sub ListRows(brs As BOMRowsEnumerator) Dim br As BOMRow For Each br In brs Debug.Print br.ItemNumber & ", " & br.ItemQuantity If Not br.ChildRows Is Nothing Then Call ListRows(br.ChildRows) End If Next End Sub Sub test() Dim ad As AssemblyDocument Set ad = ThisApplication.ActiveDocument Dim acd As AssemblyComponentDefinition Set acd = ad.ComponentDefinition Dim bom As bom Set bom = acd.bom ' Depending on what you need bom.StructuredViewEnabled = True bom.StructuredViewFirstLevelOnly = False ' Structured BOM view is second if it's enabled Dim bv As BOMView Set bv = bom.BOMViews(2) ' Header Debug.Print "Item Number, Quantity" ' Data Call ListRows(bv.BOMRows) End Sub
Cheers,
1- Scuse me but i can't attach the *.csv file in this forum.
2 - The vba code give me the error you can see in the attached jpg.
This is the line who gives me the error:
...
For i = 1 To oAssyCompDef.BOM.BOMViews(3).BOMRows.Count
...
I heve find a solution!!
Sub BOM_Export() Dim oApp As Application Set oApp = ThisApplication Dim invDoc As Document Set invDoc = ThisApplication.ActiveDocument Dim oDocument As Inventor.Document Set oDocument = ThisApplication.ActiveDocument Dim invDesignInfo As PropertySet Set invDesignInfo = invDoc.PropertySets.Item("Design Tracking Properties") Dim invPartNumberProperty As Property Set invPartNumberProperty = invDesignInfo.Item("Part Number") NumeroParte = invPartNumberProperty.Value Estensione = (".csv") Patch = GetFilePatch(oDocument.FullFileName) PercaorsoNomeEst = (Patch & NumeroParte & Estensione) 'Data Dim DataCmp As String DataCmp = (Day(Date) & "/" & Month(Date) & "/" & Year(Date)) Dim oDoc As AssemblyDocument Set oDoc = ThisApplication.ActiveDocument Dim oBOM As BOM Set oBOM = oDoc.ComponentDefinition.BOM ' Imposta corrente il livello Principale Dim oAsmDef As AssemblyComponentDefinition Set oAsmDef = ThisApplication.ActiveDocument.ComponentDefinition oAsmDef.RepresentationsManager.LevelOfDetailRepresentations.Item("Principale").Activate If oApp.ActiveDocument.DocumentType = kAssemblyDocumentObject Then Dim oAssyDoc As AssemblyDocument Set oAssyDoc = oApp.ActiveDocument Dim oAssyCompDef As AssemblyComponentDefinition Set oAssyCompDef = oAssyDoc.ComponentDefinition Dim excel_app As Excel.Application ' Setta la proprietà della vista strutturale al solo di primo livello oBOM.StructuredViewFirstLevelOnly = True ' Attiva la vista strutturata della distinata componenti oBOM.StructuredViewEnabled = True ' Crea l'applicazione Excel Set excel_app = CreateObject("Excel.Application") ' Commenta questa linea se vuoi excel invisibile excel_app.Visible = False 'Crea il foglio di lavoro Call excel_app.Workbooks.Add Dim oBomR As BOMRow Dim oBOMPartNo As String With excel_app .Range("A1").Select .ActiveCell.Value = "Quantity" .Range("B1").Select .ActiveCell.Value = "Part Number" .Range("R1").Select .ActiveCell.Value = "Inizio Validità" 'Iterate through parts only BOM View Dim i As Integer For i = 1 To oAssyCompDef.BOM.BOMViews(2).BOMRows.Count 'Set oBomR to current BOM Row Set oBomR = oAssyCompDef.BOM.BOMViews(2).BOMRows(i) 'Get Current Row part number from part oBOMPartNo = oBomR.ComponentDefinitions(1).Document.PropertySets(3).ItemByPropId(5).Value 'Write values to spreadsheet .Range("A" & i + 1).Select .ActiveCell.Value = oBomR.TotalQuantity 'Quantity value .Range("B" & i + 1).Select .ActiveCell.Value = oBOMPartNo .Range("R" & i + 1).Select .ActiveCell.Value = DataCmp Next i End With Else Exit Sub End If ActiveWorkbook.SaveAs filename:=(PercaorsoNomeEst), FileFormat:=xlCSVWindows, CreateBackup:=False, Local:=True Application.DisplayAlerts = False ActiveWorkbook.Close End End Sub 'Funzione che estrae il percorso completo nel quale si trova il file attualmente in uso Private Function GetFilePatch(ByVal sFullFileName As String) As String Dim sFilePatch As String Dim nPos1 As Integer Dim nPosf1 As Integer Dim nPos2 As Integer sFilePatch = sFullFileName nPos1 = InStrRev(sFullFileName, "\") nPos2 = InStrRev(sFullFileName, ".") If nPos1 > 0 Then sFilePacth = Mid$(sFullFileName, nPos1 + 1) End If nPosf1 = InStr(sFullFileName, ".") If nPos1 > 0 Then sFilePatch = Left$(sFilePatch, nPosf1 - (nPos2 - nPos1)) End If GetFilePatch = sFilePatch End Function
Hi,
Instead of creating an Excel application using CreateObject("Excel.Application") you will create an Inventor application or access a currently running one:
http://modthemachine.typepad.com/my_weblog/2008/09/in-the-last-posting-i-discussed-the-object-model-...
You might also be able to use Apprentice: http://modthemachine.typepad.com/my_weblog/2010/03/iproperties-without-inventor-apprentice.html
And to work inside a drawing instead of an assembly. Basically you'll have to find which part or assembly document shown inside the drawing you are inetersted in. Once you have the assembly or part document, the above code should be the same.
Cheers,