iLogic and reading Excel file - Excel Save message when no changes to file

[leonardbordian]

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.

[briandaley]

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.

[Owner2229]

Hey, try this (without the save):

GoExcel.Close(True)

[leonardbordian]

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

[MechMachineMan]

Try using 

GoExcel.Application.ActiveWorkBook.Close(SaveChanges:= False)

[leonardbordian]

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?

[briandaley]

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.

[leonardbordian]

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

[MechMachineMan]

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

[leonardbordian]

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

[MechMachineMan]

The only other thing I could offer is to move away from iLogic and use the
API directly or to see if (using either method) if you can open as read
only. Or close, specifying save changes as false.

[leonardbordian]

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

[MechMachineMan]

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

[leonardbordian]

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

[MechMachineMan]

Which is why I like to code things in a way I know will work for me
instead, instead of having to deal with the hidden, undocumented nuances of
iLogic 🙂

The resources used to make the excel API work directly can be found through
googling stackoverflow for it, or by googling "MSDN + Excel + vba" + your
topic

[leonardbordian]

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

[leonardbordian]

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

[llorden4]

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?

[leonard.bordian]

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)

[Rogers2017]

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