Open Excel file (start Excel Application and show the worksheet from iLogic)

[Anonymous]

Hi there,

I´m trying to start Excel and show the results of some BOM items I have written to a file like item number, part number and description.

The task is to find components in an assembly that have a part number longer then 39 characters, due to ERP limitations.

What I´m doing is cycling through all BOM (structured) items on first level and check if the part number is longer then 39 characters.

I write these item number, part number, description to an Excel file (temp.xlsx), so the user sees which Components he has to check for the Part Number (make it shorter). I figured it would be best to do that with an excel file, so the user can save the results to a new file or just check it.

Values will be erased from the temporary excel file afterwards.
Here I can´t figure out how to start the excel application and open the file in an actual application window if possible.

If someone has a good idea how to list the results with plain iLogic that would do it too or some other solution to list a snapshot from the BOM.

I guess there is no way to add your custom BOMViews ?

All the best

Falk

[Anonymous]

have you tried something like this..

Private Sub OpenExcel_File(ByVal sFileName As String)
        Dim excelapp As Object
        Dim wkbkobj As Object

        Try
            excelapp = CreateObject("excel.application")
            excelapp.Visible = True
            wkbkobj = excelapp.workbooks.Open(FileName:=sFileName, ReadOnly:=True)

            
            wkbkobj = Nothing
            excelapp = Nothing

        Catch
            Call MsgBox(" Request Terminated  ", vbCritical, " ERROR ")
        End Try

        wkbkobj = Nothing
        excelapp = Nothing

    End Sub

[xiaodong_liang]

Hi,

one more thread which might be helpful to you.

http://forums.autodesk.com/t5/Inventor-Customization/How-to-create-a-excel-file-using-ilogic-Thanks/...

[Anonymous]

Hi,

thanks for the help and info.

Right after I posted I found this blogpost from Curtis Waguespack (thanks Curtis), which shed some light

on how to handle Excel with plain vb code. I realize I probably have to solve my problem with the Excel Application object.

http://inventortrenches.blogspot.fr/2012/06/create-new-excel-file-with-ilogic.html

I´m running into problems with GoExcel snippets, somehow Excel seems to create an additional task for a backup file when I´m writing data to an excel sheet. In this case I always have a remaining task which seems to have the excel file open and I can´t shut it down with GoExcel.Close.

But the file seems to be named differently, it´s Resume.xlw and not the temp.xlsx I´m using. But it must hold a reference to the temp.xlsx too since I can´t open the excel after it cylced through the added data. Here is the code I´m using for writing to the excel-file.

For i = 0 To UBound(strListItems)
        Dim oPartNumProperty As Inventor.Property
        Dim oDescripProperty As Inventor.Property
        Dim oCompDef As ComponentDefinition
        
        
        
        MessageBox.Show(strListItems(i))
        MessageBox.Show(y, "Reihenwert")
        GoExcel.CellValue("K:\Autodesk\2012\Inventor\temp.xlsx", "Tabelle1", _
	"A" & y) = strListItems(i)
        oBOMRow = oBOMView.BOMRows.item(strListItems(i))
        oCompDef = oBOMRow.ComponentDefinitions.Item(1)
        oPartNumProperty = oCompDef.Document.PropertySets.Item _
	("Design Tracking Properties").Item("Part Number") 
        GoExcel.CellValue("K:\Autodesk\2012\Inventor\temp.xlsx", "Tabelle1", _
	"B" & y) = oPartNumProperty.Value
        oDescripProperty = oCompDef.Document.PropertySets.Item _
	("Design Tracking Properties").Item("Description")
        GoExcel.CellValue("K:\Autodesk\2012\Inventor\temp.xlsx", "Tabelle1", _
	"C" & y) = oDescripProperty.Value
        y = y + 1
    Next

strListItems is an array that holds the item numbers from the BOM with the items that have a part number that is bigger then 39 characters. The For loop goes through every item and writes the item Number, Part Number and Description to the excel file.

y starts with 2 and is the row in the excel file.

After I´m done with writing data to the excel file I want to open this excel file and show the results to the user so he can edit the Part Numbers in question which he probably will do through the BOM-Editor. After closing the excel file it should be cleaned up so no old data remains.

Cheers

Falk

[adam.nagy]

Hi Falk,

So if I understand you correctly you don't even need excel at all, you just want to enable the user to edit a table full of data.

In that case this could be another solution: http://adndevblog.typepad.com/manufacturing/2013/11/table-editing-from-ilogic.html

Cheers,

[Anonymous]

Hey Adam,

thanks for the tips and help. I didn´t know that this was possible. I haven´t decided yet, Excel seems to do the job.

My Problem right now is what is the best way to iterate through the whole structure of an assembly getting the Part Number and check the length.

We´re talking about 3k to 5k components. My first try was going through the BOM but only on first level, but if I want to do that for the whole structured BOM I would have to go recursively through the childs which probably will leave me without the Object Numbers from that BOM View.

So going through all referenced documents from the assembly might be a lot easier, but that will include all components that are on reference or phantom which I don´t need to check since they won´t be recognized by the PDM System when it derives the BOM based on items.

Cheers

Falk

[adam.nagy]

Hi Falk,

You should just give it a try and then you'll see if it provides what you need or not: http://adndevblog.typepad.com/manufacturing/2013/11/iterate-structured-bomview.html

Cheers,