greetings everyone! i need help once again
with the posts and solutions from this awesome forum, i have been working on a new iLogic project
using INV2016 and Excel 2013, i have created an IAM and IPT which reads all my required parameters from a simple Excel table.
now i have followed the exact coding that is required to read my Excel file (i am not writing any information to the file) but i keep getting a nagging Excel dialog box which asks me to save my excel file.
i have searched this forum and i have tried using the "GoExcel.Save" & "GoExcel.Close" to see if this remedies my issues but to no avail i keep getting the same "Save" dialog box. when i open the Excel file, it tells me that my file needs to be "Recovered" but i know that nothing has changed
is this an Inventor bug or is it Excel?
i have been banging my head against my keyboard for a few days now and i cannot seem to fix this.
Could you post the code that you're using to access Excel?
A handy code I use to turn off to have it run silently in the background is this:
ThisApplication.SilentOperation = True
Useful for lengthy rules.
Hey, try this (without the save):
GoExcel.Close(True)
there isn't much code right now. i'm just starting out but i was following this syntax...
SyntaxEditor Code Snippet
GoExcel.Open("filename.xls", "Sheet1") GoExcel.FindRow("filename.xls", "Sheet1", "column111", "=", Test01) ParameterA = GoExcel.CurrentRowValue("AAA") ParameterB = GoExcel.CurrentRowValue("BBB") Parameter("Part1:1", "Test02") = GoExcel.CurrentRowValue("CCC") Parameter("Part2:1", "Test03") = GoExcel.CurrentRowValue("DDD") GoExcel.FindRow("filename.xls", "Sheet2", "column222", "=", Test100) ParameterE = GoExcel.CurrentRowValue("EEE") ParameterF = GoExcel.CurrentRowValue("FFF") Parameter("Part3:1", "Test02") = GoExcel.CurrentRowValue("GGG") Parameter("Part4:1", "Test03") = GoExcel.CurrentRowValue("HHH") GoExcel.Close iLogicVb.UpdateWhenDone = True
is this the best way to approach this? i do have a multiple tab Excel file that i will be pulling out all of my parameters. i do know that i will be having a few rules similar to this format as i build my iLogic assy
where would i put this line in my code... ThisApplication.SilentOperation = True
also, i tried this... GoExcel.Close(True) but INV returned an error message
Try using
GoExcel.Application.ActiveWorkBook.Close(SaveChanges:= False)
i tried that and INV returned this error "Object variable or With block variable not set."
is my formatting incorrect? should i try different syntax for my iLogic?
My excel code is basically set up like this:
GoExcel.Open(FilePath, oSheet)
Try a = GoExcel.FindRow(FilePath, oSheet, "Part", "=", drawing) Sec1 = GoExcel.CurrentRowValue("Sec1") Sec2 = GoExcel.CurrentRowValue("Sec2") Catch Sec1 = 0 Sec2 = 0 End Try
GoExcel.Close
GoExcel.Close
The GoExcel function will look for the title of the column as opposed to the letter designation. It looks in the first row for the titles. Maybe there's a way around it but that's how it worked in my experience. If you're working in multiple sheets, it might not be a bad idea to try closing and opening the file to switch sheet to sheet. At least just to see if it works that way. Also, if the parameter doesn't already exist in the source document then it will put up an error when you try to fill it like that.
Hope that helps.
i've started completely over and i'm re-writing line by line. this is what i'm starting with..
'takes model number and removes 1st character
zModel_Value = Mid(Model,2,4)
'name and location of Excel file
ExcelFile = "U:\CAD SUPPORT FILES\iLogic_LookUp_Table.xlsx"
i = GoExcel.FindRow (ExcelFile, "Sheet1", "Size", "=", zModel_Value)
'gets value from Excel and assigns it to parameter
Parameter("P_B") = GoExcel.CurrentRowValue("B")
iLogicVb.UpdateWhenDone = True
even with this stripped down, Excel still want to save even tho i am just reading the file
i'll try your format and see if that helps
After looking through the documentation, I think this may work
First Line of code in rule:
iLogicVb.Automation.SilentOperation = True
Last line of code in rule:
iLogicVb.Automation.SilentOperation = False
Otherwise, you can try checking the documentation for something yourself:
https://forums.autodesk.com/t5/inventor-customization/ilogic-api-documentation/td-p/6785772
thanks for the links. i will try to get my brain wrapped around them. i've been staring at this code all day 😞
i've looked at a few dozen web sites... youtube videos... searched here and everywhere.
i just don't understand what is wrong. i even started new... re-wrote code... stepped line per line with dialog boxes for checking. researched the difference between XLS and XLSX. triple checked my Excel file for errors. but it's still Excel wanting to save but nothing has changed. it's similar to INV saving even after i've save 2X before LOL
thanks for your help. i will keep trying
how much different is using the API over iLogic. i'm still working my way around iLogic.
i've seen examples of what i'm trying to do and the code seems straightforward (i though) and i've played around with examples from what i downloaded
i'm just confused why Excel is acting up in my project but no other ones
Here is a sample of connecting to/using.close excel use in a rule:
Option Compare Text AddReference "Microsoft.Office.Interop.Excel" 'To use excel Imports Microsoft.Office.Interop.Excel 'To use excel 'need the class to share variables across subs. Public Class xlUsage Shared xlWb As Microsoft.Office.Interop.Excel.Workbook Shared xlWs As Microsoft.Office.Interop.Excel.WorkSheet Shared oExcelApp As Microsoft.Office.Interop.Excel.Application Sub Main() oXLSheet = "Sheet1" Dim oDoc As Document = ThisDoc.Document If oDoc.DocumentType <> kDrawingDocumentObject MsgBox("Drawing Documents Only!") Exit Sub End If On Error Resume Next OpenXL(True) If Err.Number <> 0 Exit Sub End If On Error Goto ErrHandler xlWs = xlWb.Worksheets(oXLSheet) ErrHandler: Dim lastrow As Integer = xlWs.Cells(xlWs.Rows.Count, 1).End(xlDirection.xlUp).Row xlWs.Range("A2:D" & lastrow).Sort(Key1:=xlWs.Range("A2:A" & lastrow), Order1 :=XlSortOrder.xlAscending, Header:=XlYesNoGuess.xlNo, Orientation:=XlSortOrientation.xlSortColumns) xlWs.Columns("A:D").AutoFit CloseXL(True) If lastRow > 1 OpenXL(True) End If End Sub Sub OpenXL(oMakeVisible As Boolean) Dim oXLWorkBook As String = "C:\Users\Public\Documents\iLogic Buffer File.xlsx" If System.IO.File.Exists(oXLWorkBook) = False MsgBox("Error opening excel!" & vbLf & "Please make sure this blank file exists: " & vbLf & vbLf & "C:\Users\Public\Documents\iLogic Buffer File.xlsx" & vbLf & "(File name copied to clipboard)") Process.Start("C:\Users\Public\Documents\") System.Windows.Forms.Clipboard.SetText("iLogic Buffer File.xlsx") Err.Raise(1) Exit Sub End If oExcelApp = GetObject("", "Excel.Application") xlWb = oExcelApp.Workbooks.Open(FileName:=oXLWorkBook) oExcelApp.Visible = oMakeVisible End Sub Sub CloseXL(oSaveBool As Boolean) xlWB.Close(oSaveBool,,) oExcelApp.Quit xlWs = Nothing xlWb = Nothing oExcelApp = Nothing End Sub End Class
whoa.... i bow to you with deep respect... although i can barely follow what have here... i have no way of understanding how you got that out of your head 🙂
i have been thinking that there may be something wrong with my Excel sheet. because my iLogic was 'working' prior to my last major revision. i'm going to track down my previous version (if possible) and try my code before i consider a different avenue.
as i have said previously, i have seen multiple examples of what i think should have been a simple task to do (reading data from Excel). i'm not reinventing the wheel here. i live by the simple rule of... work smarter, not harder.
to quote Owner2229 - "Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live."
thanks again for your assistance
okay... after being frustrated for days now... i completely started over again.
started a new blank Excel file... and began to re-create my tabs and created my look up data
in INV... line by line with dialog boxes tested everything
and having the Task Manager running to see if Excel is running in the background.
i even shut down my computer twice to make sure everything is good
and now, i'm not having any issues at all. my iLogic code isn't any different. my data isn't different.
i can only assume that somehow my Excel got corrupt with 'something'. going forward i will be stepping very slowing with iLogic and my Excel. if this same issue comes back and i determine what caused this, i shall post my findings.
thanks for all help and assistance
okay... i think i finally solved this issue on my own, and i have determined that it wasn't INV or my iLogic code. my problem was coming from Excel after all. in my Excel file, i have multiple worksheets. and in my one worksheet, i have a formula that gets one cell value from one worksheet (MODEL) and adds the tab name to it. see below...
='R2'!A6 & MID(CELL("filename",'R2'!A6),FIND("]",CELL("filename",'R2'!A6))+1,255)
so, what i've determined, Excel always wants to save based on it's save options. and since i was always saving my file, i never thought anything was wrong. but in INV, just reading the Excel file, Excel always prompted to be saved. so, i changed the save options and now INV reads my Excel file and there is no save dialog box. here is a screen capture of the save options which i use...
so if anyone else is having this issue, try this. let me know if this works for you. i've test this a few times and it seems to resolve my problem.
have a great day all and thanks again for everyone's help here
I'm also fighting this issue, while your solution provides a solution for Inventor, it creates another issue for Excel users; that being the fields no longer automatically update when cell data is changed or added. Trouble tickets and anxiety ensue when automatic updates don't happen for the excel user.
I don't suppose you've found another solution for this since your post?
for what I am using Excel for (just reading data, not changing anything), this works great for me. as for Excel, yes the automatic updates don't happen until you hit the save button. for me, yes, it took a while to get used to the workflow.
I am sorry that I haven't found another solution (yet)
Set EXCEL Workbook Calculation to "manual" from "automatic" works fine for me.
And I found the link on TIPS.NET : Saving Non-Existent Changes by Allen Wyatt
They say some functions (like INDIRECT, OFFSET, RAND, and RANDBETWEEN ... etc) will make EXCEL contents change.
Then, I changed my excel function in complicated way without "INDIRECT".
The EXCEL Save message problem never appear again on setting Calculation to "automatic".