Inventor Customization

Inventor Customization

Reply
Valued Contributor
falkmassmann
Posts: 82
Registered: ‎05-30-2008
Message 1 of 7 (644 Views)

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

644 Views, 6 Replies
11-11-2013 06:22 AM

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

Valued Contributor
wowens63
Posts: 98
Registered: ‎06-22-2007
Message 2 of 7 (611 Views)

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

11-12-2013 09:13 AM in reply to: falkmassmann

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

 

ADN Support Specialist
xiaodong.liang
Posts: 1,303
Registered: ‎06-12-2011
Message 3 of 7 (583 Views)

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

11-14-2013 11:12 PM in reply to: falkmassmann

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/...



Xiaodong Liang
Developer Technical Services
Autodesk Developer Network

Valued Contributor
falkmassmann
Posts: 82
Registered: ‎05-30-2008
Message 4 of 7 (576 Views)

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

11-15-2013 12:56 AM in reply to: xiaodong.liang

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

ADN Support Specialist
adam.nagy
Posts: 587
Registered: ‎03-26-2007
Message 5 of 7 (528 Views)

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

11-18-2013 04:00 PM in reply to: falkmassmann

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,



Adam Nagy
Developer Technical Services
Autodesk Developer Network
Valued Contributor
falkmassmann
Posts: 82
Registered: ‎05-30-2008
Message 6 of 7 (503 Views)

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

11-21-2013 05:28 AM in reply to: adam.nagy

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

ADN Support Specialist
adam.nagy
Posts: 587
Registered: ‎03-26-2007
Message 7 of 7 (479 Views)

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

11-23-2013 05:43 AM in reply to: falkmassmann

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,



Adam Nagy
Developer Technical Services
Autodesk Developer Network
Post to the Community

Have questions about Autodesk products? Ask the community.

New Post
Need installation help?

Start with some of our most frequented solutions or visit the Installation and Licensing Forum to get help installing your software.