Embedded Excel Spreadsheet (Open, Save, and Close) (iLogic)

[Anonymous]

I guess I’m not bright enough to figure this one out. I would appreciate any help that you might be able to give. I have some code (below) that will open my embedded excel spread sheet and it works great.  Now I need the code to save and close the excel file after it’s been open.

The embedded excel spreadsheet pushes some stuff the model and does a couple other thing. So all I really need is for the excel file to open, save, and close when the rule is run. 

Imports Inventor.OLEDocumentTypeEnum
ExcelApp = CreateObject("Excel.Application")
Dim ExcelFile As ReferencedOLEFileDescriptor
ExcelFile = ThisDoc.Document.ReferencedOLEFileDescriptors.Item(1)
ExcelFile.Activate(kEditOpenOLEVerb, ExcelApp)

[Anonymous]

I guess this was a lot harder question then I thought. I figured I would at least get a “You can’t do that” or a “Why would want to do that” or something like that. 

[philippe.leefsma]

Unfortunately there seems to be no way to load the file in Excel if it is embedded. For a linked file it is possible, see sample below:

Public Sub OpenXls()

    Dim doc As DrawingDocument
    Set doc = ThisApplication.ActiveDocument
    
    Dim desc As ReferencedOLEFileDescriptor
    Set desc = doc.ReferencedOLEFileDescriptors(1)
    
    Dim xlsApp As Excel.Application
    Set xlsApp = CreateObject("Excel.Application")
    xlsApp.Visible = True
    
    Dim workbook As workbook
    
    If (desc.OLEDocumentType = kOLEDocumentEmbeddingObject) Then
    
        'No way to load file in Excel instance form there...
        Call desc.activate(kEditOpenOLEVerb, workbook)
        Exit Sub
        
    ElseIf (desc.OLEDocumentType = kOLEDocumentLinkObject) Then

        Set workbook = xlsApp.Workbooks.Open(desc.FullFileName)
    
    End If
    
    'Do something with the xls doc...
    MsgBox "Opened: " & workbook.FullName
    
    workbook.Save

    workbook.Close True
    Set workbook = Nothing
    
    xlsApp.Quit
    Set xlsApp = Nothing
   
End Sub

Regards,

Philippe.

[Anonymous]

Thank you so much for your response. I did figure out a way to get everything work the way I wanted. It’s not pretty but it works:

First I use this code to open the embedded excel spreadsheet and drive information to a cell: 

     Dim ExcelFile As ReferencedOLEFileDescriptor

     ExcelFile = ThisDoc.Document.ReferencedOLEFileDescriptors.Item(1)

     ExcelFile.Activate(kEditOpenOLEVerb, ExcelApp)

GoExcel.CellValue("3rd Party:Embedding 5", "Sheet2", "B2") = Activate_Macro

The Macro will first delete whatever is in cell Sheet 2 “B2” then save and close Excel. I know it’s not pretty but it gets the job done.

The reason I delete whatever is in cell Sheet 2 “B2” is so I can still get into the embedded excel spreadsheet at anytime without it running the Macro on me. 

[Anonymous]

In Excel I have a Macro the will run only if Sheet 2 “B2” cell says “Activate_Macro”.

[Anonymous]

Hi jddickson , Try this: ExcelFile.Save(False) ExcelFile.Close hope to help

[DeerSpotter]

I created this excel sheet to compare values... see the macro in there. Its just what you need.