CODEWITHSUNDEEP
vbaexcel
stpdevi
stpdevi

Reputation: 1114

How to Open Embedded Object in Excel with Caption in Vba?

Try to open embedded object in excel file with caption,able to open file with name: can any one help how to open with caption or dynamically

Worksheets(SheetName).Activate
Set o = Worksheets(SheetName).OLEObjects("object 3")
o.Verb xlVerbOpen
MsgBox "Attachmene open"

Note: object will add continuously in excel file, how to find object dynamically to open with caption?

Upvotes: 1

Views: 2437

Answers (2)

TBoulz
TBoulz

Reputation: 351

I open an embedded WORD document through Excel using the following verb command.

Set o = .OLEObjects("Object 1")
  o.Verb xlVerbOpen

"Object 1" is the default name of the embedded object so that would be changed as needed.

Upvotes: 0

Vityata
Vityata

Reputation: 43575

The embedded object in Excel is a Shape. Add two embeded workbooks in your ActiveSheet and try this code:

Public Sub TestMe()

    Dim obj As Object

    For Each obj In ActiveSheet.Shapes
        Debug.Print obj.Application.Caption
    Next obj

End Sub

Then try to change the code, with a simple condition, opening the obj, if the caption is the expected one:

If obj.Application.Caption = "someCaption" Then OpenTheWorkbook(obj)

At the end write some check to make sure that it skips some possible errors.

Upvotes: 1

Related Questions