Programatically activating a Worksheet object

[Josef Meile]

Hi,

I inserted a Microsoft Office Excel Object on a Visio 2003 drawing. Then I
fill it with some data. Finally I would like to call a macro that locates and
highligths an specified row. The steps I need to do are the following:

1) Select the Ole Object. This wasn't a big problem, I did:
ActiveWindow.DeselectAll
ActiveWindow.Select Application.ActiveWindow.Page.Shapes.ItemFromID(1),
visSelect

and worked fine.

2) Enter programatically into the Ole Object instead of having to double
click on it. I didn't succeeded here, see the last part of the message to see
what I have tried.

3) Selecting the desired row: For now I will give a number to the row, but
later, this will be done with any number:

ActiveWindow.Selection(1).Object.Sheets(1).Rows("7:7").Select

The last step should theorically work. I don't know it yet because I fail
doing the previous step: Entering in to the ole object.

On the second step I tried several things:

a) recorded a macro while doing double click on the worksheet object.
Unfortunately, an empty macro was recorded.

b) started the Visio Event Monitor from the SDK add-ins and tried to guess
where the following events are called:

10805 SelectionChanged window[1] Drawing1.vsd Page-1 subtype is 128
[/hwnd=4981414]
10823 SelectionChanged window[1] Drawing1.vsd Page-1 subtype is 128
[/hwnd=4981414]
10824 >EnterScope Open Object [1029;0;Open Object]

From there I understood that the first two events happen whenever I double
click on the mentioned object -> An SelectionChanged event pro click. Then,
an "Open Object" event happens when entering to the ole Object, but the
question is: where is this "Open" method? I tried several alternatives
without any success.

Then I tried the following things:

c) ActiveWindow.Selection(1).Object.Sheets(1).Activate -> didn't work

d) ActiveWindow.Selection(1).Object.Application.Visible = True -> didn't work

e) On the newsgroups, I found that from visual basic or excel, there is
something called "OLE Container" and you can use:

yourOLEContainer.DoVerb (-5) -> Where -5 means "in-place activation"
acording to the poster of this solution.

Translating it to visio, I think it would be something like:
ActiveWindow.Selection(1).DoVerb (-5)

but it complains and says that there isn't such method.

Does any body has a solution to this problem?

Thanks in advanced,
Josef Meile

 

[Chris Roth [ Visio MVP ]]

A quick guess here...

I looked at some code I had for accessing a VISIO object in a POWERPOINT
slide. A similar condition to what you want to do.

There seems to be this OLEFormat.Object method of getting at the thing you
want. In my case, it looks like this:

Set visDoc = pptSlide.Shape.OLEFormat.Object

For you, it's probably something like"

Set xlSheet = Visio.ActivePage.Shapes("xlObj").OLEFormat.Object

That might return you a workbook, a worksheet, or an Excel application. I'm
not sure.

--

Hope this helps,

Chris Roth
Visio MVP

Hi,

I inserted a Microsoft Office Excel Object on a Visio 2003 drawing. Then I
fill it with some data. Finally I would like to call a macro that locates
and
highligths an specified row. The steps I need to do are the following:

1) Select the Ole Object. This wasn't a big problem, I did:
ActiveWindow.DeselectAll
ActiveWindow.Select Application.ActiveWindow.Page.Shapes.ItemFromID(1),
visSelect

and worked fine.

2) Enter programatically into the Ole Object instead of having to double
click on it. I didn't succeeded here, see the last part of the message to
see
what I have tried.

3) Selecting the desired row: For now I will give a number to the row, but
later, this will be done with any number:

ActiveWindow.Selection(1).Object.Sheets(1).Rows("7:7").Select

The last step should theorically work. I don't know it yet because I fail
doing the previous step: Entering in to the ole object.

On the second step I tried several things:

a) recorded a macro while doing double click on the worksheet object.
Unfortunately, an empty macro was recorded.

b) started the Visio Event Monitor from the SDK add-ins and tried to guess
where the following events are called:

10805 SelectionChanged window[1] Drawing1.vsd Page-1 subtype is 128
[/hwnd=4981414]
10823 SelectionChanged window[1] Drawing1.vsd Page-1 subtype is 128
[/hwnd=4981414]
10824 >EnterScope Open Object [1029;0;Open Object]

From there I understood that the first two events happen whenever I double
click on the mentioned object -> An SelectionChanged event pro click.
Then,
an "Open Object" event happens when entering to the ole Object, but the
question is: where is this "Open" method? I tried several alternatives
without any success.

Then I tried the following things:

c) ActiveWindow.Selection(1).Object.Sheets(1).Activate -> didn't work

d) ActiveWindow.Selection(1).Object.Application.Visible = True -> didn't
work

e) On the newsgroups, I found that from visual basic or excel, there is
something called "OLE Container" and you can use:

yourOLEContainer.DoVerb (-5) -> Where -5 means "in-place activation"
acording to the poster of this solution.

Translating it to visio, I think it would be something like:
ActiveWindow.Selection(1).DoVerb (-5)

but it complains and says that there isn't such method.

Does any body has a solution to this problem?

Thanks in advanced,
Josef Meile

 

[Josef Meile]

Hi Chris,

There seems to be this OLEFormat.Object method of getting at the thing you
want. In my case, it looks like this:

Set visDoc = pptSlide.Shape.OLEFormat.Object

For you, it's probably something like"

Set xlSheet = Visio.ActivePage.Shapes("xlObj").OLEFormat.Object

That might return you a workbook, a worksheet, or an Excel application. I'm
not sure.

Actually it is:
ActiveDocument.OLEObjects("myObjId").Object

but what I want is to activate the edit mode of the OLE Object on my visio
drawing and not get the object itself. Normally you have to double click on
the excel worksheet, but I want to do this programatically. I have tried
several methods on the "Object" property and its subobjects, but I haven't
had any success. I found that when you set the "Double click" event of such
OLE Object manually:
Right mouse click on the Object, then format/Behavior/Double-click

There is an OLE Verb field. I set it to "edit" and recorded this on a macro
and got:

Application.ActiveWindow.Page.Shapes.ItemFromID(1).CellsSRC(visSectionObject, visRowEvent, visEvtCellDblClick).FormulaU = "DOOLEVERB(""Edit"")"

The question is: how can I call the DOOLEVERB method from a macro? If I just
do:
DOOLEVERB("Edit"), it raises an error which tells me that that's an invalid
method. I think somehow you have to give it an ole instance, but how? Other
solution would be forced the excecution of the double click event, but I
haven't also found a solution for this.

Thanks for your reply,
Josef

 

[Josef Meile]

Ok, finally I got it. It wasn't easy to figured out, but it works:

Dim cellFormula As String
Dim vsoCell As Visio.Cell
Dim vsoShape As Visio.Shape
cellFormula = "DOOLEVERB(""Edit"")"
Set vsoShape = ActiveDocument.OLEObjects("work").Shape
vsoShape.AddSection (visSectionUser)
vsoShape.AddNamedRow visSectionUser, "editMode", visRowUser
Set vsoCell = vsoShape.Cells("User.editMode")
vsoCell.Formula = cellFormula
vsoCell.Trigger
ActiveDocument.OLEObjects("myObjId").Object.Sheets(1).Rows("7:7").Select

Is there any easier way or this is the correct one?

Hi,

I inserted a Microsoft Office Excel Object on a Visio 2003 drawing. Then I
fill it with some data. Finally I would like to call a macro that locates and
highligths an specified row. The steps I need to do are the following:

1) Select the Ole Object. This wasn't a big problem, I did:
ActiveWindow.DeselectAll
ActiveWindow.Select Application.ActiveWindow.Page.Shapes.ItemFromID(1),
visSelect

and worked fine.

2) Enter programatically into the Ole Object instead of having to double
click on it. I didn't succeeded here, see the last part of the message to see
what I have tried.

3) Selecting the desired row: For now I will give a number to the row, but
later, this will be done with any number:

ActiveWindow.Selection(1).Object.Sheets(1).Rows("7:7").Select

The last step should theorically work. I don't know it yet because I fail
doing the previous step: Entering in to the ole object.

On the second step I tried several things:

a) recorded a macro while doing double click on the worksheet object.
Unfortunately, an empty macro was recorded.

b) started the Visio Event Monitor from the SDK add-ins and tried to guess
where the following events are called:

10805 SelectionChanged window[1] Drawing1.vsd Page-1 subtype is 128
[/hwnd=4981414]
10823 SelectionChanged window[1] Drawing1.vsd Page-1 subtype is 128
[/hwnd=4981414]
10824 >EnterScope Open Object [1029;0;Open Object]

From there I understood that the first two events happen whenever I double
click on the mentioned object -> An SelectionChanged event pro click. Then,
an "Open Object" event happens when entering to the ole Object, but the
question is: where is this "Open" method? I tried several alternatives
without any success.

Then I tried the following things:

c) ActiveWindow.Selection(1).Object.Sheets(1).Activate -> didn't work

d) ActiveWindow.Selection(1).Object.Application.Visible = True -> didn't work

e) On the newsgroups, I found that from visual basic or excel, there is
something called "OLE Container" and you can use:

yourOLEContainer.DoVerb (-5) -> Where -5 means "in-place activation"
acording to the poster of this solution.

Translating it to visio, I think it would be something like:
ActiveWindow.Selection(1).DoVerb (-5)

but it complains and says that there isn't such method.

Does any body has a solution to this problem?

Thanks in advanced,
Josef Meile