ilogic configuration problem in inventor 2016?

[Anonymous]

hi!

we have just installed inventor 2016 but the rule who created my partist dont work now, but work great in inventor 2015, the problem is do not keep the vb macro in my excel sheet???

Thanks for your help!

[BrandonBG]

Can you post the code in the iLogic rule?

Did you update to a newer version of Excel, also?

Brandon

[Anonymous]

no i have a same version of excel than before, excel 97-2003!

[Anonymous]

the rule:

'define oDoc
oDoc = ThisDoc.Document

'specify the drawing sheet
oSheet = oDoc.Sheets("Sheet:1") ' sheet by name
'oSheet = oDoc.Sheets(1) ' first sheet

' say there is a Partslist on the sheet.
oPartslist = oSheet.PartsLists(1)
     
' create a new NameValueMap object
oOptions = ThisApplication.TransientObjects.CreateNameValueMap

'specify an existing template file
'to use For formatting colors, fonts, etc
oOptions.Value("Template") = "I:\INVENTOR\INVENTOR_2016\CARTOUCHE\LISTE_DE_COUPE_2016.xls"
 
'specify the columns to export         
oOptions.Value("ExportedColumns") = "COMPOSANTE;STOCK NUMBER;PI2;LONGUEUR;LARGEUR;QTY;QTE TOTALE;ÉPAISSEUR;CHANTS;DETAIL;COMMANDE;L;M;VEINAGE;USINAGE;NESTING;POIDS"

'specify the start cell
oOptions.Value("StartingCell") = "T2"
 
'specify the XLS tab name
'here the file name is used
oOptions.Value("TableName") = ThisDoc.FileName(False) 'without extension        

'choose to autofit the column width in the xls file
oOptions.Value("AutoFitColumnWidth") = False

'choose to apply cell formatting in the xls file
oOptions.Value("ApplyCellFormatting") = True

DOSSIER = ThisDoc.Path
POSITION1 = InStrRev(DOSSIER, "\")
POSITION2 = Right(DOSSIER, Len(DOSSIER) - POSITION1)

myFile2 = "I:\DOCUMENTS\ARTICLES\" & POSITION2 & "\" & POSITION2 & "_STANDARD" & ".xls"
If(System.IO.File.Exists(myFile2)) Then
Kill (myFile2)
End If
       
' export the Partslist to Excel with options
oPartslist.Export("I:\DOCUMENTS\ARTICLES\" & POSITION2 & "\" & POSITION2 & "_STANDARD" & ".xls", _
PartsListFileFormatEnum.kMicrosoftExcel, oOptions)

'define Excel Application object
excelApp = CreateObject("Excel.Application")
'set Excel to run visibly
'change to false if you want to run it invisibly
excelApp.Visible = True
'open the workbook
wb = excelApp.Workbooks.Open("I:\DOCUMENTS\ARTICLES\" & POSITION2 & "\" & POSITION2 & "_STANDARD" & ".xls")

'suppress prompts (such as the compatibility checker)
excelApp.DisplayAlerts = False
'select the original exported sheet
excelApp.Sheets(ThisDoc.FileName(False)).Select
'cut a column
excelApp.Columns("S:AM").Cut
'select sheet to paste to
excelApp.Sheets("EXPORTATION").Select
'select range to paste to
excelApp.Range("A1").Insert
excelApp.Range("T2")=iProperties.Value("Summary", "Author")
excelApp.Sheets(ThisDoc.FileName(False)).Select
excelApp.Range("A3").Select
'save the workbook
wb.Save

[Anonymous]

anybody can help me?

[adam.nagy]

Hi,

It usually helps if you can narrow things down on your side by removing code unnecessary for reproducing the issue and also test in VBA.

If people see that it can be reproduced with a couple of lines of VBA code and it's not iLogic specific, more of them might chip in 🙂

You only need an xls file with a macro in it and the following VBA code to reproduce the behaviour:

Sub SavePartsListUsingXlsTemplate()
  Dim oDoc As DrawingDocument
  Set oDoc = ThisApplication.ActiveDocument
  
  Dim oSheet As Sheet
  Set oSheet = oDoc.ActiveSheet
  
  Dim oPartslist As PartsList
  Set oPartslist = oSheet.PartsLists(1)

  Dim oOptions As NameValueMap
  Set oOptions = ThisApplication.TransientObjects.CreateNameValueMap
  
  ' This template is in 97-2003 format and
  ' contains a macro
  oOptions.value("Template") = "C:\temp\Excel_97-2003_with_macro.xls"
  
  ' In case of Inventor 2016 this saves the file in xlsx format
  ' which cannot contain macros, so the macro gets removed
  Call oPartslist.Export( _
    "C:\temp\Excel_97-2003_with_macro_export.xls", _
    kMicrosoftExcel, oOptions)
End Sub

It seems like Inventor 2015 saves the created file in old xls format which can contain macros whereas 2016 saves it in the new xlsx format (even though the file will have xls extension).

You can easily verify that the file saved by Inventor 2016 is an xlsx by changing the created xls file's extension to zip (because the new xlsx file format is actually a zip) and then try to unzip it.

The new xlsx does not support macros, only xlsm does, but that's not the format Inventor 2016 is saving to, and it does not seem like you can change that.

I cannot think of a workaround other than using the Excel API directly to create the xls file and fill it up with data yourself, but I'll ask around. 

Cheers,

[Anonymous]

thanks for your reply Adam 🙂

i change my extension file to xlsm and replace oPartlist.Extport("myFile.xlsm") PartsListFileFormatEnum.KMicrosoftExcel, oOptions)  by Call oPartslist.Export("myFile.xlsm") KMicrosoftExcel, oOptions) but dont worl again 😞

[adam.nagy]

That's what I was trying to say too 🙂

"but that's not the format Inventor 2016 is saving to, and it does not seem like you can change that."

That's why I said that I think probably the only solution is to create the Excel file directly using Excel API.

(instead of using oPartslist.Export) 

Sorry for the bad news.

[Anonymous]

ok thanks for your help Adam 😞

[Anonymous]

anybody know the excel code to insert a .bas file in excel?

excelApp.Workbooks.VBProject.VBComponents.Import ("I:\INVENTOR\INVENTOR_2016\CARTOUCHE\Module3.bas")   ???

[Anonymous]

anybody know if is possible ???

[adam.nagy]

This is more of an Excel question, so an Excel forum might be more responsive on this.

However, according to this thread it should work but you need to set the security settings in Excel accordingly:

http://www.excelforum.com/excel-programming-vba-macros/356856-importing-bas-files.html 

[Anonymous]

Thanks again for your help Adam 🙂

And really sorry for my horrible english 😞

do you think it's possible to make a code same to that in ilogic?

[adam.nagy]

There is nothing wrong with your English! 🙂

There is a thread with a sample where Excel API is used from iLogic:

https://forums.autodesk.com/t5/inventor-customization/excel-optimization-using-ilogic-and-solver/td-...

[Anonymous]

ok

I try that this week!

Thanks again 🙂

[Anonymous]

hi!

i try and make this rule with your link and now keep all my Vb Macro in my xlsm file.

____________________________________________________________________________

AddReference "Microsoft.Office.Interop.Excel"
Imports Microsoft.Office.Interop.Excel

 Dim oExcelApp As New Microsoft.Office.Interop.Excel.Application
         'make Excel visible (not required - for debugging only)
         oExcelApp.Visible = False
         'opem some Excel file
         oExcelApp.workbooks.Open("I:\INVENTOR\INVENTOR_2016\CARTOUCHE\LISTE_DE_COUPE_2016.xlsm") 
       
         'reference to the active workbook
         WB = oExcelApp.activeWorkbook
         'Reference to the active worksheet by its name
   'WS = WB.Sheets("EXPORTATION")
 'Reference to the specified excel cell (Range object). Row = 3, column = 2
         'R = WS.Cells(3, 2)
         'do something - e.g., put some value
         'R.Value = oExcelApp.WorksheetFunction.PI()
       
DOSSIER = ThisDoc.Path
POSITION1 = InStrRev(DOSSIER, "\")
POSITION2 = Right(DOSSIER, Len(DOSSIER) - POSITION1)

myFile2 = "I:\DOCUMENTS\ARTICLES\" & POSITION2 & "\" & POSITION2 & "_STANDARD" & ".xlsm"
If(System.IO.File.Exists(myFile2)) Then
Kill (myFile2)
End If
WB.SaveAs (myFile2)

______________________________________________________________________

Any other way to take the NameValueMap of the partlist  and put to the open Excel sheet?

[adam.nagy]

Hi,

This should help 🙂

http://adndevblog.typepad.com/manufacturing/2016/02/use-excel-api-to-export-partslist-content.html

Cheers,

[Anonymous]

thank you very much Adam 🙂

[Anonymous]

it's possible to change the stratingcell and sheet in EXCEL in this ilogic rule?

[adam.nagy]

Where the data is written is controlled by the iRow and iCol variables' value.

So whenever in the code they are being set to 1, you would have to set them to a different starting value.

Have updated the blog post to use iRowStart and iColStart which you can set to change where the table should start on the sheet.