Programmatic Sizing of Embedded Excel document

[gshell]

I have some VB.Net code that creates a Swimlane diagram from data retrieved from a SQL database.  That code has been stable for a couple of years.  I am now attempting to add some functionality to that code to also embed an Excel spreadsheet on the Visio Diagram.

So far, I can create an Excel spreadsheet and save it.  Then I can instantiate the spreadsheet in the Visio diagram using:

           

Code Select Expand

shpObj = Me.AxDrawingControl1.Document.Application.ActiveWindow.Page.InsertFromFile(System.IO.Path.GetTempPath & strTheFileName & ".xls", visInsertAsEmbed)

That works great!  But the problem is the resulting embedded spreadsheet has some columns and rows that are not visible. 

If I save the Visio document and open it up in Visio, I can double click on the embedded Excel spreadsheet to enter the spreadsheet.  This produces a border around the embedded Excel window which I can then resize to expose the columns and rows.  Clicking elsewhere on the Visio document.  Removes this border and I see the embedded Excel document sized as I wanted it. 

My problem is I can't figure out how to reproduce that in my VB.Net code.  I can't figure out how to expose the OLEObject to my code so I can manipulate the size of the OLEobject window.  (I am assuming that's what I need to do.)  I find references to doing this in PowerPoint VBA, but that involves OLEFormat and Visio doesn't seem to have such an object.

Color me confused and frustrated!

I hope someone else can shed some light.

Thanks in advance,

Gary

[Paul Herber]

           

Code Select Expand

shpObj = Me.AxDrawingControl1.Document.Application.ActiveWindow.Page.InsertFromFile(System.IO.Path.GetTempPath & strTheFileName & ".xls", visInsertAsEmbed)

Can you not just set the shpObj height and width?

Code Select Expand


shpObj.cellsSrc(visSectionObject, visRowXFormOut, visXFormWidth).FormulaU = 200mm


[gshell]

           

Code Select Expand

shpObj = Me.AxDrawingControl1.Document.Application.ActiveWindow.Page.InsertFromFile(System.IO.Path.GetTempPath & strTheFileName & ".xls", visInsertAsEmbed)

Can you not just set the shpObj height and width?

Code Select Expand


shpObj.cellsSrc(visSectionObject, visRowXFormOut, visXFormWidth).FormulaU = 200mm


Nope, that just makes the embedded Excel document appear larger, i.e. scales it.  It unfortunately does not expose more rows and columns. 

That gives the same behavior as manually grabbing one of the handles of embedded Excel document and resizing it WITHOUT first double clicking the embedded Excel document.  If you doube click the embedded document first, you get a different set of handles and an outline around the document that is made up of diagonal lines.  Then dragging a handle exposes more rows and columns and does not scale the document.  That is the behavior I am trying to mimic in code.

Gary

[Visio Guy]

You probably need to scale it up, then adjust the cropping as well.

Look what happens to the Width, Height cells and cells in the Foreign Image Info ShapeSheet section when you expand the Excel object with the Crop tool.

[wapperdude]

Just some observations from a non-programmer --
1.)  The double click behavior opens the Excel chart in Excel.  The dragging the width to expose more columns is taking place in Excel, not Visio.  This is true for Power Point and Word.  So, I don't think any of that behavior will be "caught" by Visio.

2.)  When the code places the Excel "worksheet"  initially, is the chart already correctly sized.  Manually pasting Excel cells works because all desired rows and columns have been selected, copied, and paste special as Excel worksheet.

3.)  I haven't followed thru the steps, but if you do a record a macro, and then go to menu bar > insert > objects > Excel worksheet ...  ...end macro.  That might give you more information on how to format and execute your spreadsheet placement issues.

[gshell]

You probably need to scale it up, then adjust the cropping as well.

Look what happens to the Width, Height cells and cells in the Foreign Image Info ShapeSheet section when you expand the Excel object with the Crop tool.

Scale it up, then crop it???  Not sure what you mean. If by scaling you mean the apparent size of the text in the embedded document, that is fine.  (As I mentioned before scaling, using that definition, is easily accomplished by just adjusting the height and width of the Visio shape that contains the embedded document.)

If by cropping you mean the action of first double clicking the shape to expose the "excel interface", signaled by the appearance of the diagonal barred frame around the shape and dragging THAT frame, then indeed it is that cropping that I am attempting.  

As to the height and width cells of the Foreign Image Info shapesheet section, alas I already looked into that.  They are already express as a formula of Height*1 and Width*1, thereby referencing the size of the Visio shape itself.  I will, however try playing with them to use absolute values to see if that does anything... nope that didn't help at all.  It still does not expose more rows and columns.  It just scales the ones already visible. Changing the "cropping" i.e. exposing more rows and columns makes NO changes in any of the shapesheet values at all.  That was the first place I looked.  

It appears that what I need to be pragmatically manipulating is the OLE container itself.  Digging around in Google, I see references to doing this in Word and Power Point where an Excel document is embedded in either of those.  There the VBA object model exposes a OLEFormat object that seems to be what I am after, but no such object appears in the Visio VBA object model. <sigh>

Any help or direction anyone can give would be most appreciated.  This is a real show stopper for me.  I am in real trouble if I can't over come this.

Gary

[gshell]

Just some observations from a non-programmer --
1.)  The double click behavior opens the Excel chart in Excel.  The dragging the width to expose more columns is taking place in Excel, not Visio.  This is true for Power Point and Word.  So, I don't think any of that behavior will be "caught" by Visio.

2.)  When the code places the Excel "worksheet"  initially, is the chart already correctly sized.  Manually pasting Excel cells works because all desired rows and columns have been selected, copied, and paste special as Excel worksheet.

3.)  I haven't followed thru the steps, but if you do a record a macro, and then go to menu bar > insert > objects > Excel worksheet ...  ...end macro.  That might give you more information on how to format and execute your spreadsheet placement issues.

Your observations are correct, exposing more columns is indeed taking place in Excel.  I know that. But that means that I still need to figure out how to expose the Excel object model of the embedded document to my code.  Having just said that though, I am not so sure it IS taking place in Excel.  Yes, Excel is active because of the double click.  But I am not manipulating anything in the Excel spreadsheet itself.  I am manipulating the OLE container by dragging its edge.  As I said to Visio Guy in my reply above, I have dug around in Google and see references to doing this in both Word and Power Point.  Both have an object model that appears to expose the embedded OLE object in a different way than Visio.  I have seen references to "OLEFormat" in both (but alas not in Visio) and think that may be the trick I am looking for.

I did do exactly what you suggest about recording a macro (BTW that is my favorite way of figuring out how to pragmatically do things in Visio).  But the only thing that the macro records is the creation of the embedded Excel document.  It does NOT capture the double click of the document and subsequent expansion of the visible rows and columns.  Again, I think this is because as you said in your first point, this expansion is happening in Excel, not Visio. 

I am convinced from what I have read of other folks doing this with Word and Power Point being the container for the embedded Excel document.  But this is getting frustrating.

Thanks for your help!!!!!!!!!  Please keep the ideas flowing.  Any and all are most welcome.

Gary

[gshell]

After further experimentation, it is now obvious to me that double clicking the embedded spreadsheet and exposing more columns is NOT actually occurring in Excel either.  Yes the Excel menus are exposed etc. but if I record a macro in Excel while dragging the frame around the spreadsheet (the frame made up of diagonal lines), the macro recorder records no actions at all.

Therefore, it is becoming obvious that this dragging of the frame to expose more rows or columns is occurring in some "no man's land" betwixt Visio and Excel.  It seems to be that the OLE container itself is where this is occurring.  And it further seems that the OLE container is not exposed to the Visio object model or the Excel object model. 

As I am creating the Visio document itself using VB.Net and that same code also creates the Excel spreadsheet and embeds it into the Visio diagram, I am now trying to figure out if VB itself can expose the OLE containers object model and thereby control the dragging to expose more rows and columns.

Again, any insight any of you might have with this would be most helpful.  This has become a frustrating, yet engaging task.

Gary

[grtyvr]

I seems that you can not do that.....

"The size and position of the hatched border that displays when the container is made in-place active is determined by the size of the object and the options selected for the OLE control. This border is displayed to mark the boundaries of the editing window. The boundaries of the editing window often do not match the boundaries of the OLE container itself; this behavior is normal for an OLE object. The editing window cannot be programmatically altered from Visual Basic."

From

http://support.microsoft.com/kb/q242243/

But somehow the UI for Visio is able to do that, so it just an undocumented feature I'm guessing.

[Nikolay]

Here is the solution.
Unfortunately, it is only usable from .NET (or unmanaged code), i.e. you won't be able to do that with VBA:

http://blogs.msdn.com/b/vsod/archive/2008/11/01/resizing-the-embedded-ole-objects-using-net.aspx