topic Betreff: Excel location in Inventor Programming Forum

Excel location

jostroopers — Wed, 23 Jun 2021 07:29:00 GMT

I have an assy linked to an excel file.
This link was made with ilogic.
I cannot link or embed an .xslm file from Inventor.
The location of the excel file is stated in the ilogic code.
Everything is in Vault Basic.
When I make a Copy Design, the code keeps pointing to the same location.
How can I set up the ilogic code so that it is automatically pointed to the new location.

Or is there an action to add that the user has to select the new location and this is copied into the ilogic code.

'Koppelen van artikelnr vanuit de BOM In Excel naar de Configurator sheet.
Dim userParams As UserParameters = ThisApplication.ActiveDocument.ComponentDefinition.Parameters.UserParameters
Dim excelFileLocation As String = "D:\$WorkingFolder\Vault Workspace\02_Basis ontwerpen\02_8_Bedienbaar daklicht\02_8_02_Vast\BoM Passiefhuis Daklicht 05-07-2019V1.xlsm"
work = GoExcel.FindRow(excelFileLocation, "Configurator", "Artikelnr", "=", Artikelnr)


For Each param In userParams
	Try
		Parameter(param.Name) = GoExcel.CurrentRowValue(param.Name)
		Catch
		End Try
Next

iLogicVb.UpdateWhenDone = True

I saw the code below but that was explained when the excel file was embedded.

But you can't link or embed an .xlsm file.

Dim oDocument As Document
Set oDocument = ThisApplication.ActiveDocument
'Delete original reference
Dim oD As ReferencedOLEFileDescriptor
Set oD = oDocument.ReferencedOLEFileDescriptors.ItemByName("aa.xlsx")
oD.Delete

'Add new reference with updated project location
Call oDs.Add(ProjectLocation + "\aa.xlsx", kOLEDocumentLinkObject)

Re: Excel location

FINET_Laurent — Wed, 23 Jun 2021 07:46:31 GMT

Hi,

What you could do is have the excel file always in the same folder as the assembly document.

You could then get the current document path, remove the document display name from it, and add the excel file name.

Regards,

FINET L.

Betreff: Excel location

Ralf_Krieg — Wed, 23 Jun 2021 08:52:36 GMT

Hello

As @FINET_Laurent suggested, if the Excel file is located in the same folder as the document we can use the API to get the folder dynamically. Is the filename of Excelfile and Document the same (except extension) ?

Then we can use it too. We should insert an alternative way to select the file with a dialog, in case the generated path is wrong.

'Koppelen van artikelnr vanuit de BOM In Excel naar de Configurator sheet. Dim userParams As UserParameters = ThisApplication.ActiveDocument.ComponentDefinition.Parameters.UserParameters Dim sPath As String = ThisDoc.PathAndFileName(False) Dim excelFileLocation As String = sPath & ".xlsm" '"D:\$WorkingFolder\Vault Workspace\02_Basis ontwerpen\02_8_Bedienbaar daklicht\02_8_02_Vast\BoM Passiefhuis Daklicht 05-07-2019V1.xlsm" If System.IO.File.Exists(excelFileLocation) = False Then ' Create a new FileDialog object. Dim oFileDlg As FileDialog Call ThisApplication.CreateFileDialog(oFileDlg) oFileDlg.Filter = "Excel Files (*.xls;*.xlsx;*.xlsm)|*.xls;*.xlsx;*.xlsm|All Files (*.*)|*.*" oFileDlg.FilterIndex = 1 oFileDlg.DialogTitle = "Open Excel File" oFileDlg.InitialDirectory = ThisDoc.Path oFileDlg.ShowOpen If oFileDlg.FileName <> "" Then excelFileLocation = oFileDlg.FileName Else Exit Sub End If End If work = GoExcel.FindRow(excelFileLocation, "Configurator", "Artikelnr", "=", Artikelnr) For Each param In userParams Try Parameter(param.Name) = GoExcel.CurrentRowValue(param.Name) Catch End Try Next iLogicVb.UpdateWhenDone = True

Betreff: Excel location

jostroopers — Wed, 23 Jun 2021 12:03:00 GMT

Thanks @Ralf_Krieg

I have the excel file in the same folder as the Inventor model.
When I run the rule, the location is opened and I can click on the excel file and it is linked again.
If I make a change in the excel file, my model is updated properly.
However, when I now save my model, I am again asked for the excel file and I have to select it again in the opened folder.
Even if I change a parameter in a part, I am asked again for the excel file and the location is opened and I have to select the excel file again.
It would be nice if the first time the excel file is selected, you are no longer asked for it.

Betreff: Excel location

FINET_Laurent — Wed, 23 Jun 2021 12:05:44 GMT

Make sure this is checked :

Regards,

FINET L.

Betreff: Excel location

jostroopers — Wed, 23 Jun 2021 14:00:14 GMT

Thanks @FINET_Laurent

I checked that option but when i save the model it still ask for the Excel file.

Betreff: Excel location

FINET_Laurent — Wed, 23 Jun 2021 14:04:06 GMT

Maybe there is an even triggering the rule when saving the document.

Have a look inside this menu : Manage tab -> iLogic

Regards, FINET L.

Betreff: Excel location

jostroopers — Tue, 29 Jun 2021 13:41:47 GMT

@Ralf_Krieg Is it possible that the excel sheet gets opened after this rule is run.
I can then immediately enter the desired information and update my model.

Betreff: Excel location

Ralf_Krieg — Tue, 29 Jun 2021 19:45:38 GMT

Hello

I've added a short line to open file in Excel. Don't forget to add the first to lines to Header section of your rule.

AddReference "microsoft.office.interop.excel.dll" Imports XL = Microsoft.Office.Interop.Excel Private Sub Main 'Koppelen van artikelnr vanuit de BOM In Excel naar de Configurator sheet. Dim userParams As UserParameters = ThisApplication.ActiveDocument.ComponentDefinition.Parameters.UserParameters Dim sPath As String = ThisDoc.PathAndFileName(False) Dim excelFileLocation As String = sPath & ".xlsm" '"D:\$WorkingFolder\Vault Workspace\02_Basis ontwerpen\02_8_Bedienbaar daklicht\02_8_02_Vast\BoM Passiefhuis Daklicht 05-07-2019V1.xlsm" If System.IO.File.Exists(excelFileLocation) = False Then ' Create a new FileDialog object. Dim oFileDlg As FileDialog Call ThisApplication.CreateFileDialog(oFileDlg) oFileDlg.Filter = "Excel Files (*.xls;*.xlsx;*.xlsm)|*.xls;*.xlsx;*.xlsm|All Files (*.*)|*.*" oFileDlg.FilterIndex = 1 oFileDlg.DialogTitle = "Open Excel File" oFileDlg.InitialDirectory = ThisDoc.Path oFileDlg.ShowOpen If oFileDlg.FileName <> "" Then excelFileLocation = oFileDlg.FileName Else Exit Sub End If End If work = GoExcel.FindRow(excelFileLocation, "Configurator", "Artikelnr", "=", Artikelnr) For Each param In userParams Try Parameter(param.Name) = GoExcel.CurrentRowValue(param.Name) Catch End Try Next iLogicVb.UpdateWhenDone = True 'try open the file in Excel OpenExcelFile(excelFileLocation) End Sub Private Sub OpenExcelFile(ByVal ExcelPath As String) Dim oExcelApp As XL.Application = GetObject("","Excel.Application") If oExcelApp Is Nothing Then MsgBox("Unable to start Excel") Exit Sub End If Dim oWB As xl.Workbook = oExcelApp.Workbooks.Open(ExcelPath) oExcelApp.Visible = True End Sub

Betreff: Excel location

jostroopers — Wed, 30 Jun 2021 06:20:44 GMT

Thanks @Ralf_Krieg

But when i put the code in ilogic the firts two lines put into the Header. Is that normal?

I can't put them above Privat Sub Main.

When i run the rule i get an error.

Excel is not opening the file.

More info:

System.InvalidCastException: Unable to cast COM object of type 'Microsoft.Office.Interop.Excel.ApplicationClass' to interface type 'Microsoft.Office.Interop.Excel._Application'.
This operation failed because the QueryInterface call on the COM component for the interface with IID '{000208D5-0000-0000-C000-000000000046}' failed due to the following error: Element not found.
(Exception from HRESULT: 0x8002802B (TYPE_E_ELEMENTNOTFOUND)).
at System.StubHelpers.StubHelpers.GetCOMIPFromRCW(Object objSrc, IntPtr pCPCMD, IntPtr& ppTarget, Boolean& pfNeedsRelease)
at Microsoft.Office.Interop.Excel.ApplicationClass.get_Workbooks()
at ThisRule.OpenExcelFile(String ExcelPath)
at ThisRule.Main()
at Autodesk.iLogic.Exec.AppDomExec.ExecRuleInAssembly(Assembly assem)
at iLogic.RuleEvalContainer.ExecRuleEval(String execRule)

Betreff: Excel location

Ralf_Krieg — Wed, 30 Jun 2021 14:17:45 GMT

Hello

It is normal, the two lines are for the Header section.

Which version of Excel is installed on your PC?

Betreff: Excel location

jostroopers — Thu, 01 Jul 2021 05:54:23 GMT

@Ralf_Krieg we hebben office 2016

Betreff: Excel location

Ralf_Krieg — Thu, 01 Jul 2021 07:09:59 GMT

Hello

This problem is often caused by installing a newer Office version and downgrade to an older version. Can you try a Quick repair of Office from Control panel? If this don't work you can try delete (Export Key before to have a backup!!!) this RegKey:

- right mouse click on Start button

- select "Run..."

- type "regedit" and hit OK

- under HKEY_CLASSES_ROOT/TypeLib search for {00020813-0000-0000-C000-000000000046} and if exist delete Keys "1.9"/"1.8"/"1.7"

I'm not sure if Office needs to be 64-bit for 64-bit Inventor, but that is my next point to think of.

Betreff: Excel location

jostroopers — Fri, 02 Jul 2021 05:13:54 GMT

@Ralf_Krieg thanks for your reaction.

I did the regedit but that was not the solution.

I had a 32 bits office version and installed a 64 bits version.

And that was the solution.

Now it opens excel when i run the rule.

Great, thanks for the tips.