Open Embeded excel files within another excel file

[abenitez77]

I have a macro that goes and copies certain cells from the sreadsheet. Sometimes I run into a excel file that has an embedded excel file. I want to open that embeded file so I can check to see if there are any values I need from that spreadsheet. How can I code this in vba, so that it opens the embedded file if one exists in my excel file?

 

[Domenic]

The following code will loop through each OleObject in the active sheet, and first check whether it's an embedded object. If so, it then checks whether it's an Excel workbook. If so, it opens it for you to do whatever it is you need to do, and then it closes it without saving it.

    [COLOR=darkblue]Dim[/COLOR] oOleObj [COLOR=darkblue]As[/COLOR] OLEObject
    [COLOR=darkblue]Dim[/COLOR] wbOpen [COLOR=darkblue]As[/COLOR] Workbook
    [COLOR=darkblue]Dim[/COLOR] sProgID [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    
    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] oOleObj [COLOR=darkblue]In[/COLOR] ActiveSheet.OLEObjects
        [COLOR=darkblue]If[/COLOR] oOleObj.OLEType = xlOLEEmbed [COLOR=darkblue]Then[/COLOR]
            [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] [COLOR=darkblue]Next[/COLOR]
            sProgID = oOleObj.progID
            [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]GoTo[/COLOR] 0
            [COLOR=darkblue]If[/COLOR] Len(sProgID) > 0 [COLOR=darkblue]Then[/COLOR]
                [COLOR=darkblue]If[/COLOR] Left(oOleObj.progID, 5) = "Excel" [COLOR=darkblue]Then[/COLOR]
                    oOleObj.Verb xlVerbOpen
                    [COLOR=darkblue]Set[/COLOR] wbOpen = oOleObj.Object
                    [COLOR=green]'do stuff[/COLOR]
                    '
                    [COLOR=green]'[/COLOR]
                    wbOpen.Close SaveChanges:=[COLOR=darkblue]False[/COLOR]
                [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
                sProgID = vbNullString
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]Next[/COLOR] oOleObj

Hope this helps!

 

[abenitez77]

Domenic, This is great! How about if the embedded file is a pdf or tif file?

thanks,
Alex