Macro enabled excel templates

[hkempeneers]

Hi,

Does Inventor 2023 supports macro enabled excel templates?

And if so, is there an example of what the iLogic code will look like to write out iProperties to such an Excel?

Thank you!

[bradeneuropeArthur]

What do you exact mean?
What do you want to do?

you can write to a xlsm file.

[hkempeneers]

Arthur,

Thank you for your response!
We want to write from an assembly of some parts the Part Numbers and some iProperties in an Excel sheet.
We can get this done in an ordinary Excel sheet without macros.

Our working method in brief:
Using iLogic, we copy a template Excel to the location of the assembly, which Excel is populated with data and saved.
When an Excel with macros is used the result is that the macros disappear. For unknown reasons Inventor cannot copy and save an Excel with macros retained.

For further processing of the data, it would help us a lot if we could apply an Excel with macros.
Hence my question.

I hope I've clarified it a bit this way?

[A.Acheson]

Hi @hkempeneers 

I use the same setup in my current BOM export and it works like this.

1.Use BOM export code to export bom to .xlsx file.

2.Use the same ilogic rule to open the .xlsm template

3. Copy .xlsx content to template

4. Place a value into a cell on template and with this cell populated and cell value change  event trigger the internal macros are triggered. 

So in short using a temp place holder workbook to hold the data and allow it to be positioned in the correct place in the template. The BOM export options don't allow this specific positioning of data. 

I had attempted to operated macros in the template direct from the ilogic rule but it seems it is not supported perhaps security issue or it has a specific  method which I could not find. 

If your open to the idea maybe get rid of the .xlsm file and put all the processing within an ilogic rule. 

To further the conversation can you share the code your using to understand what is manual /automated. 

[hkempeneers]

@A.Acheson 

Hi Alan,

Thanks for your response.
I don't think I fully understand it unfortunately....
I'm sure that's just me and certainly not you. 😉

In the attachment I am sharing our code.

(In .txt because .iLogicVb is not supported on this forum.)

So this rule works great in .xlsx, but we can't get it to work in an Excel with macros (.xlsm).

Because when an Excel .xlsm is copied and saved, the macros disappear.

[A.Acheson]

Hi @hkempeneers 

I tested your code and it is opening a copy of the macro workbook and all the macros are there. Can you confirm you have pointed to the right workbook?

Dim OXLTempfile As String = "C:\VaultWS2\CAD Instellingen\Inventor 2023\Templates\Quote_Template.xlsm"

[hkempeneers]

Hi @A.Acheson ,

The workbook the code refers to contains macros.
Of course because we need them precisely in the sequel.
This workbook is copied by the code, opened and filled with the relevant data.
So far so good.
However, when the Workbook is saved by the code, the macros disappear.
The extension of this file then becomes .xlsx.
And no longer contains macros.
Somehow, it must be possible to choose "xlOpenXMLWorkbookMacroEnabled" during saving.
But how that works in iLogic I don't know.

It could well be that the solution must come in this line of the rule:
excelWorkbook.SaveAs(oFileName)

[A.Acheson · Accepted solution]

Hi @hkempeneers 

A quick look at stackoverflow  here shows the method for VB.NET. This will assume you have supplied a path fullfilename with .xlsm extension. 

excelWorkbook.SaveAs (fullfilename,FileFormat:=52)

[hkempeneers]

Hi @A.Acheson,

This code works!
Thank you very much.