Community
Inventor Programming - iLogic, Macros, AddIns & Apprentice
Inventor iLogic, Macros, AddIns & Apprentice Forum. Share your knowledge, ask questions, and explore popular Inventor topics related to programming, creating add-ins, macros, working with the API or creating iLogic tools.
cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Inventor VBA xls export

19 REPLIES 19
Reply
Message 1 of 20
varga_zsolt
4693 Views, 19 Replies

Inventor VBA xls export

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????

19 REPLIES 19
Message 2 of 20
varga_zsolt
in reply to: varga_zsolt

Please can you help me?

Message 3 of 20
varga_zsolt
in reply to: varga_zsolt

I have made this code it works perfectly but I don't be able to order the culomn in csv file! I need only two culomn in A quantity and in B number of parts.

Public Sub BOMExport()

    ' Set a reference to the assembly document.

    ' This assumes an assembly document is active.

    Dim oDoc As AssemblyDocument

    Set oDoc = ThisApplication.ActiveDocument

   

    ' Set a reference to the BOM

    Dim oBOM As BOM

    Dim oDocument As Inventor.Document

    Dim sFileName As String

    Dim PN As String 'Nome File

    Dim Patch As String 'Percorso del File

    Dim fine As String 'Messaggio dell'avvenuta esportazione della distinta

   

    ' Imposta corrente il livello Principale

    Dim oAsmDef As AssemblyComponentDefinition

    Set oAsmDef = ThisApplication.ActiveDocument.ComponentDefinition

    oAsmDef.RepresentationsManager.LevelOfDetailRepresentations.Item("Principale").Activate

   

    ' Variabili che servono a ricavare il nome del file (PN) e il percorso (Patch)

    Set oDocument = ThisApplication.ActiveDocument

    Set oBOM = oDoc.ComponentDefinition.BOM

    sFileName = ThisApplication.ActiveDocument.FullFileName

    PN = GetFileName(oDocument.FullFileName)

    Patch = GetFilePatch(oDocument.FullFileName)

   

   ' Assicurati che il nuovo documento sia salvato

    If Patch = "" Then

    msgsalva = MsgBox("PER PRIMA COSA SALVARE IL NUOVO FILE!!!", vbCritical, "ATTENZIONE!")

    Exit Sub

    End If

   

    ' Setta la proprietà della vista strutturale al solo di primo livello

     oBOM.StructuredViewFirstLevelOnly = True

 

    ' Attiva la vista strutturata della distinata componenti

    oBOM.StructuredViewEnabled = True

 

    ' Setta BOMView come vista strutturata

    Dim oStructuredBOMView As BOMView

    Set oStructuredBOMView = oBOM.BOMViews.Item("Strutturata")

   

    ' Esporta la distinta in formato csv delimitato da virgole

    oStructuredBOMView.Export Patch & PN & ".csv", kTextFileCommaDelimitedFormat

   

    fine = MsgBox("La seguente distina: " & PN & ".csv" & _

    " esportata qui: " & Patch, , "Finito")

    

End Sub

'Funzione che estrae il nome del file senza estensione

Private Function GetFileName(ByVal sFullFileName As String) As String

 

  Dim sFileName As String

 

  Dim nPos  As Integer

  Dim nPosf As Integer


  sFileName = sFullFileName

 

  nPos = InStrRev(sFullFileName, "\")

 

  If nPos > 0 Then

 

    sFileName = Mid$(sFullFileName, nPos + 1)

 

  End If

 

  nPosf = InStr(sFullFileName, ".")

 

  If nPos > 0 Then

 

    sFileName = Left$(sFileName, nPosf - nPos - 1)

 

  End If

 

  GetFileName = sFileName

 

End Function

'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
Message 4 of 20
varga_zsolt
in reply to: varga_zsolt

Nobody can help me??? Smiley Indifferent

Message 5 of 20

Message 6 of 20

Thank you very much for your replay, I think I modify manualy the csv exported with my VBA code.

Message 7 of 20
varga_zsolt
in reply to: varga_zsolt

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!

Message 8 of 20
dano0310
in reply to: varga_zsolt

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

Message 9 of 20
varga_zsolt
in reply to: dano0310

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!??

Message 10 of 20
dano0310
in reply to: varga_zsolt

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.

 

Message 11 of 20
dano0310
in reply to: varga_zsolt

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.

Message 12 of 20
varga_zsolt
in reply to: dano0310

Thank you dano0310, i try this code but there is error and i donn't konw what is the problem.

 

Message 13 of 20
varga_zsolt
in reply to: varga_zsolt

For precision i need this: i need the file like the csv attached, in the R column i need the date of the day whan i lunch the vba code 😄 and i need to save the file in the folder where the *.iam is saved with the same name of the *.iam file but in *.csv separated by coma.
Message 14 of 20
varga_zsolt
in reply to: dano0310

Please can someone help me Smiley Sad

Message 15 of 20
adam.nagy
in reply to: varga_zsolt

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,



Adam Nagy
Autodesk Platform Services
Message 16 of 20
varga_zsolt
in reply to: adam.nagy

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.

Message 17 of 20
varga_zsolt
in reply to: dano0310

This is the line who gives me the error:

 

 

... 
For i = 1 To oAssyCompDef.BOM.BOMViews(3).BOMRows.Count
...
Message 18 of 20
varga_zsolt
in reply to: dano0310

I heve find a solution!! Smiley Tongue

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

 

Message 19 of 20
PoYHove
in reply to: varga_zsolt

1. How to convert the code to run from Excel and extract *.idw BOM ?

Message 20 of 20
adam.nagy
in reply to: PoYHove

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,



Adam Nagy
Autodesk Platform Services

Can't find what you're looking for? Ask the community or share your knowledge.

Post to forums  

Autodesk Design & Make Report