topic Re: ilogic configuration problem in inventor 2016? in Inventor Programming Forum

ilogic configuration problem in inventor 2016?

Anonymous — Mon, 11 Jan 2016 16:46:42 GMT

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!

Re: ilogic configuration problem in inventor 2016?

BrandonBG — Mon, 11 Jan 2016 18:57:29 GMT

Can you post the code in the iLogic rule?

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

Brandon

Re: ilogic configuration problem in inventor 2016?

Anonymous — Mon, 11 Jan 2016 19:00:43 GMT

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

Re: ilogic configuration problem in inventor 2016?

Anonymous — Mon, 11 Jan 2016 19:19:23 GMT

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

Re: ilogic configuration problem in inventor 2016?

Anonymous — Fri, 15 Jan 2016 15:39:59 GMT

anybody can help me?

Re: ilogic configuration problem in inventor 2016?

adam.nagy — Fri, 15 Jan 2016 16:02:43 GMT

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,

Re: ilogic configuration problem in inventor 2016?

Anonymous — Mon, 18 Jan 2016 11:43:24 GMT

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 😞

Re: ilogic configuration problem in inventor 2016?

adam.nagy — Wed, 20 Jan 2016 14:42:28 GMT

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.

Re: ilogic configuration problem in inventor 2016?

Anonymous — Wed, 20 Jan 2016 14:44:14 GMT

ok thanks for your help Adam 😞

Re: ilogic configuration problem in inventor 2016?

Anonymous — Wed, 20 Jan 2016 16:53:43 GMT

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

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

Re: ilogic configuration problem in inventor 2016?

Anonymous — Tue, 26 Jan 2016 11:11:16 GMT

anybody know if is possible ???

Re: ilogic configuration problem in inventor 2016?

adam.nagy — Tue, 26 Jan 2016 11:18:06 GMT

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

Re: ilogic configuration problem in inventor 2016?

Anonymous — Tue, 26 Jan 2016 11:23:05 GMT

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?

Re: ilogic configuration problem in inventor 2016?

adam.nagy — Tue, 26 Jan 2016 15:37:22 GMT

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-p/5142990

Re: ilogic configuration problem in inventor 2016?

Anonymous — Tue, 26 Jan 2016 15:40:36 GMT

I try that this week!

Thanks again 🙂

Re: ilogic configuration problem in inventor 2016?

Anonymous — Mon, 08 Feb 2016 17:15:45 GMT

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?

Re: ilogic configuration problem in inventor 2016?

adam.nagy — Mon, 08 Feb 2016 20:02:58 GMT

Hi,

This should help 🙂

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

Cheers,

Re: ilogic configuration problem in inventor 2016?

Anonymous — Tue, 09 Feb 2016 10:23:51 GMT

thank you very much Adam 🙂

Re: ilogic configuration problem in inventor 2016?

Anonymous — Tue, 09 Feb 2016 10:58:25 GMT

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

Re: ilogic configuration problem in inventor 2016?

adam.nagy — Tue, 09 Feb 2016 10:55:41 GMT

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.