Excel and organization chart

[gul791]

Hi all

I want to access some text from origanization chart in excel. But my doce is giving me error. I think, I need to add some com reference but do not know which one??

here is my code, please help me
Dim xshape As Excel.Shape
Dim xbook As Excel.Workbook
Dim xsheet As Excel.Worksheet
Dim xapp As Excel.Application

xapp = CType(GetObject(, "excel.Application"), Excel.Application)
xbook = xapp.ActiveWorkbook
xsheet = xbook.ActiveSheet
xshape = xsheet.Shapes.Item(1)
If xshape.Type = Microsoft.Office.Core.MsoShapeType.msoDiagram Then
MsgBox(xshape.DiagramNode.Diagram.Nodes.Count)
' it wirks perfectally
MsgBox(xshape.DiagramNode.Diagram.Nodes.Item(1).TextShape.TextEffect.T ext)
' this give me error
end if

[herilane]

'instead of
.TextShape.TextEffect.Text
'try this
.TextShape.TextFrame.Characters.Text

[gul791]

yes i tried it also, but still not working

[herilane]

"Not working" is not very precise.
In what way is it not working? What error do you get?

[gul791]

the error is "An unhandelled exception of type 'system.invalidcastException' occured in myapplication.exe".

I do not why? Please help me, It has been two day i am working hard to solve this problem.

Thanks
Atif

[Mike Rosenblum]

I think we need to try to get this running in VBA first, which unfortunately is not proving easy... :(

Converting your code to VBA, I get the following:Sub Main()
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlWS As Excel.Worksheet
Dim xlShape As Excel.Shape

Set xlApp = ThisWorkbook.Application
Set xlWB = xlApp.ActiveWorkbook
Set xlWS = xlWB.ActiveSheet
Set xlShape = xlWS.Shapes.Item(1)

If xlShape.Type = MsoShapeType.msoDiagram Then
' This works, returning 4:
Call MsgBox(xlShape.DiagramNode.Diagram.Nodes.Count)

' This returns "":
Call MsgBox(xlShape.DiagramNode.Diagram.Nodes.Item(1).TextShape.TextEffect. Text)

' This raises a RTE:
Call MsgBox(xlShape.Diagram.Nodes(1).TextShape.Text)
End If
End Sub As you can see by the comments, I can't quiet get it to work. I also tried Herilane's '.TextShape.TextFrame.Characters.Text' idea, but it doesn't seem to exist... unless maybe I'm doing something wrong?

Anyway, I made a quick example Workbook (see attached) that we can all kick around and at least be talking about the same objects, etc...

-- Mike

[herilane]

In your workbook, using Excel10, this line worked for me:Call MsgBox(xlShape.Diagram.Nodes(1).TextShape.TextFrame.Characters.Text)

[Mike Rosenblum]

Ahhh! The magic bullet! :cool:
A subtle change, but this does indeed work.

Gul, give it a try...

[gul791]

Thanks buddies for your sincere help. But I do not why the hundred percent same code is not working in VB dotnet.

Here is my code


Dim xlshape As Excel.Shape
Dim xlbook As Excel.Workbook
Dim xlsheet As Excel.Worksheet
Dim xlapp As Excel.Application

xlapp = CType(GetObject(, "excel.Application"), Excel.Application)
xlbook = xlapp.ActiveWorkbook
xlsheet = xlbook.ActiveSheet
xlshape = xlsheet.Shapes.Item(1)
If xlshape.Type = Microsoft.Office.Core.MsoShapeType.msoDiagram Then
' It works perfectly
MsgBox(xlsheet.Shapes.Item(1).Diagram.Nodes.Count)
' it throughs exception "system.Invalidcast exception"
MsgBox(xlshape.Diagram.Nodes.Item(1).TextShape.TextFrame.Characters.Te xt)
End If


Please help me. I am a experienced programmer of VC6 and Java, but this problem is really mind boggling for me.

thanks and regards
Atif

[Mike Rosenblum]

Yeah, you're right, it does not work in .Net. :(

My best attempt at it was with:MessageBox.Show(CStr(xlshape.Diagram.Nodes.Item(1).TextShape.Text Frame.Characters.Text)) The reason for using MessageBox.Show() is thas it requires that the input be a String and so this requires that you use Cstr() or the like to enforce the coercion/cast to a String Data Type.

But this did not help matters at run-time and I got thrown the same exception that you were. :(

I think this is probably just one of those things that won't work right in .Net. The COM Interop, which allows the .Net Framework to communicate with legacy COM programs is not bad, but ultimately it's a quirky, clunky beast that can create lots of headaches to get around, or at times simply out right fail. :(

For this reason, I am still, personally, staying away from .Net development, even though I love the .Net language. I just don't think it's ready yet to handle complex Automation. Some smaller Apps, maybe, but anything full-featured or even moderately complex is going to hit some major headaches that would not even exist if using VB 6.0 or VBA to develop.

That said, if you really need to get past this problem, and if .Net is your only available solution, then the guys at our Sister Forum at Extreme .Net Talk / Office Integration (http://www.dotnetforums.net/f59) handle some tougher exception handling issues and the like than we do here. I don't know that the odds of getting this one fixes is terribly hight, but it could be worth a shot?

Hope this was at least a little helpful...

-- Mike

[gul791]

Thanks buddy. Is it a bug or something that is difficult to do? If it is a bug, where can i post this bug? I have post this thread also on www.xtremedotnettalk.com, but I did not get any reply. The dotnet website forums are very less active then www.xtremevbtalk.com forums.

Thanks again for your sincere help
Atif

[Mike Rosenblum]

I'm going to guess that it is a bug. It may not be a bug from either the point of view of VB.Net nor from Excel. It does work in Excel VBA and VB6, so I will say that it is not Excel's fault. I'm going to guess that it's not .Net's fault either because this command is probably doing something with respect to type-casting that is not valid within .Net.

This therefore puts the "blame" on .Net's COM Interop. It may be a bug or oversight in the COM Interop. I guess an oversight is the most likely, and maybe a future, improved version of the COM Interop, or patch, could get this working right... Or it could be a deeper compatibility issue that will never really be solved right until MS Office is finally moved to a .Net platform, which will have to happen eventually...

Yeah, even for .Net issues we now get better volumes here than does the www.xtremedotnettalk.com, certainly for Automation / COM Interop issues, anyway. But they do get more Q's regarding difficult incompatibility issues and bizarre exceptions being thrown. I can't say that a lot of them get solved, but quite a few do. Over here, we wouldn't even have chance.

So if it's something that's working, but tricky, especially within Excel, we can usually help out over here. But if it's a bug, incompatiblity issue or installation problem then, well, we're pretty clueless over here. :(

I honestly don't know where to post this "bug". Frankly, I wouldn't bother, it's probably known at some level. Perhaps not this exact command, but I would guess that the issue causing this type of failure probably is known. Until MSFT puts MS Office onto a .Net platform, the COM Interop will continue to be an imperfect work-around...

-- Mike