topic Re: iLogic Excel Hide / Close in Inventor Programming Forum

iLogic Excel Hide / Close

matt_jlt — Wed, 13 Jul 2011 03:43:19 GMT

I have been trying to get a cell from an embedded spreadsheet but my problem is that i cannot get excel to be hidden or even close when it's done.

What I want it to do is simply retrieve the cell value without any windows opening; i just want it to work in the background.

The code i'm currently using is:

ThisApplication.SilentOperation = True ' Doesn't work
GoExcel.DisplayAlerts = False
mystr = GoExcel.CellValue("3rd Party:Embedding 2", "Bearing Housings", "L5")
GoExcel.Close ' This Doesn't work
MsgBox (mystr)

Thanks in advance, Matt.

Re: iLogic Excel Hide / Close

Anonymous — Wed, 13 Jul 2011 17:20:12 GMT

Matt,

If you just need to read a value from the embedded workbook and assign the value to a parameter there is no need to open and close excel implicity.

You can use the ilogic function GoExcel.FindRow to find the row in the worksheet

then use GoExcel.CurrentRowValue to assign the value to your Inventor parameter

Here is a code sample which does that

GoExcel.FindRow

("3rd Party:Embedding 4", "Sheet1", "bushing_mount_type", "=", xvbushingmountstring)

xv_flange_part_number

= GoExcel.CurrentRowValue("flange_part_number")

Re: iLogic Excel Hide / Close

matt_jlt — Thu, 14 Jul 2011 01:05:52 GMT

Hi swordmaster, I wasn;t trying to open excel just get the value for that cell, Thanks for helping, I tried your code but get the same problem of excel opening.

Also the CurrentRowValue doesn't work for me as i have many values on each row which is why I was trying to use the get cell value. There has to be something i'm doing wrong.

I'm using Inventor 2012 & Office 2007 on Windows XP Pro 64 bit.

Thanks, Matt.

Re: iLogic Excel Hide / Close

Anonymous — Thu, 14 Jul 2011 01:58:38 GMT

Matt,

"Also the CurrentRowValue doesn't work for me as i have many values on each row which is why I was trying to use the get cell value"

If i understand your meaning, currentrowvalue will work for you, you can include multiple arguments to determine the correct row

GoExcel.FindRow

("3rd Party:Embedding 4", "Sheet1", "column1", "=", 1, "column2", ">" 3)

does that work for you?

Re: iLogic Excel Hide / Close

matt_jlt — Thu, 14 Jul 2011 02:47:37 GMT

Sorry, I didn't explain myself very well.

I can get your code to work but it does not do what i require and excel still stays open even after the function is complete; my problem is not with the getting cell value because my existing code (below) works. My problem is that excel seems to open when any "GoExcel" functions are run and it does not close when it's finished, it just leaves a blank excel window running.

And by blank i mean that there is no workbook/spreadsheet open.

mystr = GoExcel.CellValue("3rd Party:Embedding 2", "Bearing Housings", "L5")

This is very frustrating as i thought it would be a simple thing to do.

Onve again, thanks for your help.

Regards, Matt.

Re: iLogic Excel Hide / Close

Anonymous — Thu, 14 Jul 2011 18:38:39 GMT

Matt,

Ok i understand your problem now. That is strange behaviour i use this code frequently with no issues 🙂

How did you embedd the workbook?

Unfortunately i am using INV2009 so i cannot recreate your problem. However if you wish you could save the excel as a excel2000 file and post it here. I could then do some testing.

Re: iLogic Excel Hide / Close

matt_jlt — Fri, 15 Jul 2011 05:11:00 GMT

Good news, and bad new.

Good news is that I worked out the problem. I forgot that i have been using a macro enabled spreadsheet ( I made it months ago). I got inventor to embed it by selecting the embed option before changing the filetype to "*All Filetypes", then inventor will take a minute to process and recognise as a spreadsheet but then it will import like a normal one.

It will work fine even as a macro enabled spreadsheet until you actually add some macros in there, this is where the problem is coming from. As soon as some vba is added, this is when you get that stupid excel window staying open.

Bad news is it looks like i'm stuck with this until Autodesk fixes it i suppose.

It didn't occur to me that my spreadsheet was the problem and not inventor until you pointed it out to me, thanks swordmaster. Sorry i've given you a bit of a runaround.

Thanks again, Matt.