opening an embedded object

[mike]

I'm trying to write a macro in vba that will open an embedded excel object so
that I can paste a range of data into it from the clipboard, but with no
success.

Can someone put me on the right track?

Thanks!

 

[JuneTheSecond]

Hoe did you write your code?
Would you show us your code?

 

[JuneTheSecond]

This is my test code, I think it is not the best.
Sub test()
Dim shp As Visio.Shape
Dim ob As Object
Dim ex As Excel.Application
Dim st As Excel.Worksheet
Set shp = ActivePage.Shapes(1)
Set ob = shp.Object
Set ex = ob.Application
Set st = ex.Worksheets(1)
Debug.Print st.Range("A1").Value
st.Range("A1").Value = "Tomas"
Debug.Print st.Range("A1").Value
End Sub
Reference to the excel object library is needed.

 

[JuneTheSecond]

This test code\ fails almost all time. it works when just after you paste
excel sheet and excel sheet is steill opened. But the pasted shape is not
edired. Just the excelsheet opened is changed. And more, if you paste excel
sheet as linked, this macro fails.

 

[mike]

Thanks. I'm having similar problems. I think what I need is the code that
will open the embedded excel object, similar to double-clicking on it. Do you
know how to do that?

 

[JuneTheSecond]

If the full path is known, Excel sheet might be opened by createobject command,
or I cannot the way to open.

 

[JuneTheSecond]

SendKeys command may be open the embeded sheet.
Sub test()
Application.ActiveWindow.Activate
Dim shp As Visio.Shape
Set shp = ActivePage.Shapes(1)
ActiveWindow.Select shp, visSelect
SendKeys "%E", True
DoEvents
SendKeys "O", True
SendKeys "O", True
End Sub

 

[JuneTheSecond]

Next code may open the sheet for edit as a manner when you doubleclick the
shape.
Sub test()
Dim shp As Visio.Shape
Set shp = ActivePage.Shapes(1)
ActiveWindow.Select shp, visSelect
DoEvents
Application.DoCmd visCmdEditOpenObject
End Sub

 

[JuneTheSecond]

Above code sometimes fails.
But it succed always, if you put msgbox at the last line like,

Sub OpenEandEditObject()

Application.ActiveWindow.Activate
Dim shp As Visio.Shape
Set shp = ActivePage.Shapes(1)
ActiveWindow.Select shp, visSelect
SendKeys "%E", True
SendKeys "O", True
SendKeys "O", True

MsgBox "Pess OK Button."
End Sub