Open embedded XL sheet with iLogic

[TONELLAL]

Hello,

Is it possible to open an embedded XL sheet with iLogic ?

I tried "GoExcel.Open("3rd Party:Embedding 2", "ENTREE DES DONNEES"), but nothing happen...

[TONELLAL]

Nobody open an XL file with iLogic ?

[Anonymous]

Are you looking to open... open  the excel document, as have it displayed and come up on your screen?

With a quick look at the description of that code in the Snippets, it says:

"Open an Excel file (for further use by GoExcel functions)"

I believe this is programmically accessing or "Opening" the document in the background in order to perform some functions, and not literally opening it to view on the screen.

Regards

Mitch

[TONELLAL]

I think you are right...

In fact I want to open and display the sheet by the iLogic rule, instead of double-click on "3rd party". There should be a way to do that ?

[Anonymous]

Hi TONELLAL,

It looks like under the "Document" Snippets there is a Snippet by the name of "Launch Document"

It's Description reads:

"Launch or open another document in its native application"

The Code is as follows:

ThisDoc.Launch("path\file.ext")

I beleive if you include the COMPLETE path and file name of the excel document, as well as the extension, this should do what you are looking for.

Regards

Mitch

Let me know if it helps... Kudos if it works!

[TONELLAL]

This work, for example, to open an Excel file in "c:\temp" :

ThisDoc.Launch("c:\temp\file.xls")

But not for an embedded file...

[Anonymous]

I haven't worked with embedded documents very much at all, but is the embedded file not just a link to the real document?

The Excel document itself should exist somewhere and you can simply place that file's path and name in order to open it that way, which would do the same thing as what you're asking.

I can give you code that will find this File and Path Name programmically and then proceed to open the document.

Simply put your excel file's display name into the "YOUR_FILE_NAME_HERE" spot, while keeping the proper extension:

Dim ExcelFile As String
ExcelFile = ThisDoc.Document.ReferencedOLEFileDescriptors.ItemByName("YOUR_FILE_NAME_HERE.xls").FullFileName

ThisDoc.Launch(ExcelFile)

Regards

Mitch

Let me know if it helps... Kudos if it works!!

[TONELLAL]

The problem is when the file is embedded, there is no "file.xls" existing. It is not a link to a file in a folder and a name on your computer, this is the difference between "linked" file and "embedded" file. Using your code, I can see FullFileName = "".

[Anonymous]

Embedded spreadsheets are as the name suggests "embedded" within the Inventor file. It does not reside in a folder and therefore does not have a path. I really question the wisdom of having this embedded if it needs to be opened by the user. In this case i would simply link the excel and then it can be opened using the code snippet you provided.

[Anonymous]

TONELLAL,

The below code is what you'd be looking for, unfortunately I don't know how to apply this directly in an iLogic rule. This is because the "OLEDocumentObject" you see in the "Activate" functions needs to have an object from the application its accessing (ie. needs to be an Excel.Workbook). This cannot be done without adding a reference to Excel, which I don't know how to do in iLogic directly.

Dim ExcelFile As String
ExcelFile = ThisDoc.Document.ReferencedOLEFileDescriptors.Item(1).Activate(kEditOpenOLEVerb, OLEDocumentObject)

I'd suggest the same thing as swordsmaster, make the file linked instead of embedded and life will be a lot easier.

Thanks for the info swordsmaster, I have a better understanding of how it works. I've seen linked files and they were explained to me as being "embedded" in the file, but I guess they aren't Literally embedded like the file here.

Regards

Mitch

Let me know if it helps.... Kudos if it works!!!

[TONELLAL]

Why "embedded file" instead of "linked" file ? Because it is easy to manage :

-you have only 1 file : the ipt. If you need to copy, move, rename,... the file, you don't have to know if there is another file to modify too. If you have a linked file, you cannot rename it or the ipt, if the folder is renamed the linked does not function any more, etc. With only 1 file it is much easier.

-each ipt file has its own parameters sheet. If you have a linked file you have to copy and rename the xls file for each ipt file. With an embedded file you don't have to manage this

-all needed information to use the ipt is in the ipt. No links managed by Windows.

-impossible to modify the xls file if you are not in the ipt file. The only person who can modify it is the Inventor user. If you have an external file, evrybody can modify it and so corrupt the ipt.

[Anonymous]

Hi TONELLAL,

You do make some good points.

I'm not sure what your end goal is, or why the need to open the file. Does the user need to enter some values into the excel file for parametric reasons?

If so, a work around method would be to use parameters. If there is still a need to have the excel sheet rather than using parameters alone, you can link the parameters to cells in the excel sheet.

Using parameters (either by themselves or connected to a cell in the spreadsheet) you would be able to display the values in a form and have the users edit the form instead, which woul in turn be editting the spreadsheet.

Regards

Mitch

[TONELLAL]

Hi Mitch,

The workaround is :

-create a new file, based on a template containing geometry

-open the xl sheet to choose values. This sheet is very complicated, with links between Excel sheets, Excel search in tables, etc.

-depending of selected values, all neede parameters are sent on a sheet.

-this sheet is red by iLogic with GoExcel... to fill Inventor parameters.

How to open the sheet by iLogic ?

I made an iLogic form, which can launch several rules using buttons. One of this rules should be "Open the parameters sheet".

Best regards,

Alain

[Anonymous]

TONELLAL,

As I explained a few posts back with some code, if you want to be able to open the excel sheet from within iLogic, you have to research how to "Add Reference" to excel through iLogic.

If you open the VBA Editor (Tools Tab > VBA Editor), click the "Tools" tab again in this screen, and then "References..." you'll access this ability to add references if you were programming directly in the VBA Editor. There should be a library called "Microsoft Excel...... Object Library." 

This is what you'd need to add in order to begin using the code I gave (although there is still a little more to the code), but must figure out how to do this and "Add Reference" in iLogic. As I'm not exactly sure how this works.

As a different root, I would suggest switching to programming directly in the VBA Editor, as it makes it much easier to add these references to other libraries. When I was programming my big iLogic program, I wanted to keep everything in iLogic instead of using the VBA Editor... it turned out to be a lot more work than it should have been I believe.

Regards

Mitch

[Anonymous · Accepted solution]

Hi TONELLAL,

I finally got it to work!

The following code will open the first item embedded in the document (I'm assuming you only have one):

Imports Inventor.OLEDocumentTypeEnum

ExcelApp = CreateObject("Excel.Application")

Dim ExcelFile As ReferencedOLEFileDescriptor
ExcelFile = ThisDoc.Document.ReferencedOLEFileDescriptors.Item(1)

ExcelFile.Activate(kEditOpenOLEVerb, ExcelApp)

Regards

Mitch

Let me know if it helps... Kudos if it works!!!

[TONELLAL]

Hi Mitch,

It works perfectly ! You were right, you need to create the link with Excel, then create an object, then use it. I think the iLogic commands GoExcel... do this automatically.

As you said in your previous message, I think it is easy to make this in VBA instead of iLogic. Basically, iLogic = simple parameterization, VBA = programmation. When you need to use "Dim ..." or "xxx.yyy.zzz... " in iLogic, it's time to switch to VBA !

Subsidiary question : where have you found the syntax ??

Regards,

Alain

[Anonymous]

Hi TONELLAL,

I'm glad to hear it worked for you. The object creation for excel I found as part of Curtis' code at the following link:

http://inventortrenches.blogspot.ca/2012/06/create-new-excel-file-with-ilogic.html

The rest of the code referencing the embedded file I did through the API. Once you learn how to navigate your way through the API everything becomes a lot easier.

Regards

Mitch

[Anonymous]

Mitch,

I've just found your code above after searching around for a way to open a linked excel sheet - it's exactly what I was after, cheers.

As a follow up, do you (or anyone else here) know if there's a way to monitor the status of the linked spreadsheet?

Ideally, I'd want to run an iLogic rule when the user saves the spreadsheet.

I've had a look into the API in what look to me to be the relevant areas, but haven't found anything to help - although with little experience in there I think there's a good chance I could be missing something.

Thanks,

Andy.

[djhunsucker]

In 2014 this worked great.  In Inventor 2015, it merges the screen with excel and is unusable.  Has anyone seen a way to open an embedded spreadsheet with 2015?

Thanks
Dan

[djhunsucker · Accepted solution]

Incase someone else stumble across this, found a way for 2015 to like it.

I changed the last line to:

ExcelFile.Activate(OLEVerbEnum.kEditOpenOLEVerb, ExcelApp)