Excel location

[jostroopers]

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)

[FINET_Laurent]

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.

[Ralf_Krieg]

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

[jostroopers]

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.

[FINET_Laurent]

Make sure this is checked :

Regards,

FINET L.

[jostroopers]

Thanks @FINET_Laurent 

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

[FINET_Laurent]

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

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

Regards, FINET L.

[jostroopers]

@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.

[Ralf_Krieg · Accepted solution]

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

[jostroopers]

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)

[Ralf_Krieg]

Hello

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

Which version of Excel is installed on your PC?

[jostroopers]

@Ralf_Krieg we hebben office 2016

[Ralf_Krieg · Accepted solution]

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.

[jostroopers]

@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.