Announcements
Attention for Customers without Multi-Factor Authentication or Single Sign-On - OTP Verification rolls out April 2025. Read all about it here.
op_thorsager
369 Views, 2 Replies

Import/replace embedded .xlsx file through form built with iLogic

Hi, I have created a rule inside a simple part drawing that lets you open a filebrowser inside a form. The code looks like this:

'present a File Selection dialog
Dim oFileDlg As Inventor.FileDialog = Nothing
InventorVb.Application.CreateFileDialog(oFileDlg)
oFileDlg.InitialDirectory = oOrigRefName
oFileDlg.Filter = "Excel Spec Sheet(*.xlsx)|*.xlsx"
oFileDlg.CancelError = True
On Error Resume Next
oFileDlg.ShowOpen()
If Err.Number <> 0 Then
Return
ElseIf oFileDlg.FileName <> "" Then
selectedfile = oFileDlg.FileName
End If

MessageBox.Show("You selected: " & selectedfile , "iLogic")

at its current stage, the form is very simple, and only includes an image as Banner, and the mentioned code.

op_thorsager_0-1673355838835.png

The form looks as shown above.

 

everything works as inteded, and i know its only displaying an explorer in which you can select a file you want to use. What i want it to do is have it embed the file selected, as the inteded use of the tool im working on, is to have a standardized spreadsheet layout with necessary parameters defined within. The reason why i'm not using a spreadsheet link for this, is due to functionality, as the final product is gonna be used internally on an international scale. 

 

The reason i want it to embed/replace the currently embedded spreadsheet, is so that a non inventor user can fill out the spreadsheet template i've made, send it to one of our engineers, who will then use the form and define the filepath for the received spec sheet. 

what is the most convenient approach to this setup? 

the file you import through the form will define the parameters inside the assembly, and update dimensions accordingly. 

 

My thought would be to have a "default" setup spreadsheet embedded inside the assembly, which functions as a dummy. when our engineer receives a specsheet, or updates a specsheet, they will then use the form to define filepath to the new and updated sheet with new parametric values, but with the same name definitions as the dummy sheet. After selecting the path of the updated specsheet, i want it to replace the currently embedded dummy sheet, but keep the same parameter names, so that it doesn't end up just adding a copy of the already added sheet parameters. 

 

I don't know what would be the best solution to this, or if it's even possible to do what i want it to do. 

Alternatively i guess i could make a spreadsheet setup, which looks identical to an exported XML file from the parameter window inside Inventor - that way would just need to write an iLogic rule which functions as an "import XML" button. 

 

Any recommendations regarding which solution would work in my case? The currently used code does not import anything from the file selected through the form - i'm slowly testing my way through all of this, as i'm very new to all of this iLogic and VB setup. 

 

I hope my description of what i'm trying to do is sufficient. If not, feel free to ask :slightly_smiling_face:

 

A.Acheson
in reply to: op_thorsager

There is likely no need to import/ embed the excel sheet. Based on your previous method to import the iproperties from the excel file using go excel. As long as the excel file is located in the same folder as the inventor document and you have defined the name of the file then go excel function will find the excel file. 

 

Filename only

GoExcel.Open("dwg-param.xlsx", "Sheet2")

Another method is to use the Path function of the document your running the rule on to give the directory The file is stored in.

GoExcel.Open(ThisDoc.Path & "\dwg-param.xlsx", "Sheet2")

 

If this solved a problem, please click (accept) as solution.‌‌‌‌
Or if this helped you, please, click (like)‌‌
Regards
Alan
op_thorsager
in reply to: A.Acheson

Hi, sorry for the late response - i've been caught up in learning iLogic, as i find it very fun to write. 

The frustration i get from a non functioning code just gives me more drive to solve it, so i end up hyperfocusing on it and forget everything else around me, so i just now remembered that i made this post. 

 

I ended up going away from the embedding/replace embedded file method and decided using goexcel. I've created a standard sheet layout which is locked, so the people using my tool wont be able to mess up the cells, then i set iLogic to read from the driving sheet cells. which reference a userfriendly sheet within the same excel file.

 

solved my issue by using the following code:

'Declare Iventor File Dialog
Dim oFileDlg As Inventor.FileDialog
Dim oDocFile As Document, oDocFileName As String
'Run File Selection Code
InventorVb.Application.CreateFileDialog(oFileDlg)

'Allow Selection Of Excel Files
oFileDlg.Filter = "Excel Spec Sheet (*.xls;*.xlsx;*xlsm)|*.xls;*.xlsx;*xlsm"

'Get File Path, setting the initial path to the same folder that the model is saved In.
oFileDlg.InitialDirectory = ThisDoc.Path
oFileDlg.CancelError = True
On Error Resume Next

'Show Open File Dialog Window.
oFileDlg.ShowOpen()

If oFileDlg.FileName = "" 
	MessageBox.Show("No File Selected", "Error")

GoTo LAUNCH_FORM
End If


'declares selected file name
Dim xSpec As String = oFileDlg.FileName
'Set Filename parameter to the path.
	Parameter("Filename") = xSpec
	Parameter("Veneer:1", "VeneerDepth") = VeneerDepthBuild
	Parameter("Veneer:1", "VeneerWidth") = VeneerWidthBuild
	Parameter("Veneer:1", "VeneerHeight") = VeneerHeightBuild
	Parameter("CrossBar:1", "CrossH") = CrossHeightBuild
	Parameter("CrossBar:1", "CrossD") = CrossDepthBuild
	Parameter("CrossBar:1", "CrossW") = CrossWidthBuild
	Parameter("CrossBar:2", "CrossH") = CrossHeightBuild
	Parameter("CrossBar:2", "CrossD") = CrossDepthBuild
	Parameter("CrossBar:2", "CrossW") = CrossWidthBuild
	Parameter("CrossOffBot") = CrossOffBuild
	Parameter("CtopOffset") = CtopOffsetBuild
	GoExcel.Open(xSpec, "Driver")
VeneerHeightBuild = GoExcel.CellValue(xSpec, "Driver", "B7")
VeneerDepthBuild = GoExcel.CellValue(xSpec, "Driver", "C7")
VeneerWidthBuild = GoExcel.CellValue(xSpec, "Driver", "D7")
CrossHeightBuild = GoExcel.CellValue(xSpec, "Driver", "B6")
CrossDepthBuild = GoExcel.CellValue(xSpec, "Driver", "C6")
CrossWidthBuild = GoExcel.CellValue(xSpec, "Driver", "H12")
CrossOffBuild = GoExcel.CellValue(xSpec, "Driver", "H7")
CtopOffsetBuild = GoExcel.CellValue(xSpec, "Driver", "H8")
FirstVeneerOffset = GoExcel.CellValue(xSpec, "Driver", "-H6")
ModuleDim = GoExcel.CellValue(xSpec, "Driver", "D6")
	GoExcel.close

'ask if user wants to update dialog
question = MessageBox.Show("Update model To spec sheet?", "xxx Gen v.3", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1)
	If question = vbYes Then
		Parameter.UpdateAfterChange = True
		iLogicVb.UpdateWhenDone = True
		Parameter("Filename") = xSpec
		Parameter("Veneer:1", "VeneerDepth") = VeneerDepthBuild
		Parameter("Veneer:1", "VeneerWidth") = VeneerWidthBuild
		Parameter("Veneer:1", "VeneerHeight") = VeneerHeightBuild
		Parameter("CrossBar:1", "CrossH") = CrossHeightBuild
		Parameter("CrossBar:1", "CrossD") = CrossDepthBuild
		Parameter("CrossBar:1", "CrossW") = CrossWidthBuild
		Parameter("CrossBar:2", "CrossH") = CrossHeightBuild
		Parameter("CrossBar:2", "CrossD") = CrossDepthBuild
		Parameter("CrossBar:2", "CrossW") = CrossWidthBuild
		Parameter("CrossOffBot") = -CrossOffBuild
		Parameter("CtopOffset") = CtopOffsetBuild

	Else If question = vbNo Then
		Parameter.UpdateAfterChange = False
		iLogicVb.UpdateWhenDone = False
		Parameter("Veneer:1", "VeneerDepth") = DefaultVD
		Parameter("Veneer:1", "VeneerWidth") = DefaultVW
		Parameter("Veneer:1", "VeneerHeight") = DefaultVH
		Parameter("CrossBar:1", "CrossH") = DefaultCH
		Parameter("CrossBar:1", "CrossD") = DefaultCD
		Parameter("CrossBar:1", "CrossW") = DefaultCW
		Parameter("CrossBar:2", "CrossH") = DefaultCH
		Parameter("CrossBar:2", "CrossD") = DefaultCD
		Parameter("CrossBar:2", "CrossW") = DefaultCW
	End If
	
LAUNCH_FORM:
iLogicForm.Show("xxx Product Generator")