topic Re: how to overwrite excel file name in iLogic rule in Inventor Forum

how to overwrite excel file name in iLogic rule

Anonymous — Tue, 25 Apr 2017 14:23:16 GMT

Hello I need help with my iLogic rule. First of all I am not experienced with VBA.

I have rule where excel file is always attached in same folder as parametric model, but name of excelfile is variable. In picture you can see that every time when i change excelfile name I must go to iLogic and overwrite it as well. Can anybody help me make it automatically?

Thank you in advance

Re: how to overwrite excel file name in iLogic rule

mcgyvr — Tue, 25 Apr 2017 15:09:30 GMT

Well.. you will need to give us a clue on how Inventor is supposed to know what excel file it needs to search for..

Is it always the same as the part number?

Example... part1.ipt would always be looking for part1.xlsx?

If so you could change the rule to pick up on the part number of the file and then use that to populate the excel filename code..

Something like this..

PN = iProperties.Value("Project", "Part Number")
GoExcel.Open(PN & ".xlsx", "Sheet1")

Re: how to overwrite excel file name in iLogic rule

Anonymous — Wed, 26 Apr 2017 12:27:20 GMT

Thank you,

well it´s not exactly true. one excell file belong to two or more ipt models.

when models have name ABCD_part1.ipt , ABCD_part2.ipt atc. and excell file is named ABCD.xlsm(same for all models)

Can I in your rule anyhow avoid the end of IPT filename?

Re: how to overwrite excel file name in iLogic rule

mcgyvr — Wed, 26 Apr 2017 12:32:54 GMT

@Anonymous wrote:

Thank you,

well it´s not exactly true. one excell file belong to two or more ipt models.

when models have name ABCD_part1.ipt , ABCD_part2.ipt atc. and excell file is named ABCD.xlsm(same for all models)

Can I in your rule anyhow avoid the end of IPT filename?

This should do it..

PN = iProperties.Value("Project", "Part Number")
Dim s() As String = PN.split("_")
GoExcel.Open(s(0) & ".xlsx", "Sheet1")

Re: how to overwrite excel file name in iLogic rule

Anonymous — Wed, 26 Apr 2017 14:19:54 GMT

It´s better. i try to handle with it but i got error message

"Object reference not set to an instance of an object."

I little bit update code and check it with message box if the address is correct, but it gave mi the same error message.

PN = ThisDoc.FileName(True)
Dim s() As String = PN.split("_")
GoExcel.Open(s(0) & ".xlsm", "Sheet 1")

Re: how to overwrite excel file name in iLogic rule

mcgyvr — Wed, 26 Apr 2017 14:22:56 GMT

Are you trying to run that in an assembly or something?

Re: how to overwrite excel file name in iLogic rule

Anonymous — Wed, 26 Apr 2017 14:29:04 GMT

no it´s .ipt model, but inside are more Solid Bodies. Can it affect that?

Re: how to overwrite excel file name in iLogic rule

mcgyvr — Wed, 26 Apr 2017 16:51:40 GMT

@Anonymous wrote:

no it´s .ipt model, but inside are more Solid Bodies. Can it affect that?

Please post your ipt file and the associated excel file and the ilogic code you have created..

The code I posted works just fine but its only a small piece so whatever code you have added is causing the problems..

Re: how to overwrite excel file name in iLogic rule

Anonymous — Fri, 28 Apr 2017 13:41:59 GMT

Please find files in attachement.

Re: how to overwrite excel file name in iLogic rule

mcgyvr — Fri, 28 Apr 2017 14:08:18 GMT

PN = ThisDoc.FileName(True)
Dim s() As String = PN.split("_")
MessageBox.Show(s(0), "Title")

Excelfile=s(0) & ".xlsx"


p1=GoExcel.CellValue(Excelfile,"Sheet1","B2")
p2=GoExcel.CellValue(Excelfile,"Sheet1","B3")
p3=GoExcel.CellValue(Excelfile,"Sheet1","B4")
p4=GoExcel.CellValue(Excelfile,"Sheet1","B5")
p5=GoExcel.CellValue(Excelfile,"Sheet1","B6")

Re: how to overwrite excel file name in iLogic rule

Anonymous — Fri, 28 Apr 2017 14:42:22 GMT

Excellent!!!

That´s exactly what I need. Many thanks mcgyvr.

Re: how to overwrite excel file name in iLogic rule

mcgyvr — Fri, 28 Apr 2017 16:40:51 GMT

You can remove the "message box show line" I forgot to cut that out..