Author Topic: Problem with Excel OLE Objects turning into Box with X/Cross on it  (Read 218 times)

0 Members and 1 Guest are viewing this topic.

Blinkenlogs

  • Newbie
  • *
  • Posts: 2
I am creating a flow diagram for a chemical process where I need to include details about each step in the process flow.  I am using Excel OLE objects to include the details, but they keep converting to generic rectangular boxes with crosses from corner to corner ("X" shaped).  I can "refresh" their appearance by double-clicking to open Excel and then closing again, but they eventually just revert back to the unwanted rectangular box appearance.

Attachment should make all this clear.

vojo

  • Hero Member
  • *****
  • Posts: 1513
Re: Problem with Excel OLE Objects turning into Box with X/Cross on it
« Reply #1 on: May 08, 2020, 04:07:34 PM »
if there is not too much data per shape (20 items straight forward, more can be done with a hierarchical approach), you can use shapedata for this kind of thing.

If relatively static data (e.g. maybe 5 times life of drawing), can use Excel copy as picture and insert picture into visio.

May not be same problem, but putting large drawings into power point can get "out of memory" errors.

Blinkenlogs

  • Newbie
  • *
  • Posts: 2
Re: Problem with Excel OLE Objects turning into Box with X/Cross on it
« Reply #2 on: May 08, 2020, 04:25:38 PM »
Yeah, I was thinking the behavior (intermittently blanking into the nondescript crossed-box) seemed like an out of memory issue but I don't *think* what I'm asking it to do is especially intensive.  I'm a little disappointed as having calculations in Excel performed right in the Visio file was a pretty nice feature.

Thanks for your thoughts...I'll check out the shapedata thing!

vojo

  • Hero Member
  • *****
  • Posts: 1513
Re: Problem with Excel OLE Objects turning into Box with X/Cross on it
« Reply #3 on: May 09, 2020, 01:36:27 PM »
regarding shape data, its only viable if
- not too many fields to worry about (shape data plays well if all can be displayed within window)
  *** before you do all this, I would try to work out if its a memory issue ***
  *** note that memory issue may involvethe excel sheet handle - maybe huge - as well as the actual region of interest***

  So if too many fields, then got to use a hierarchical scheme.   Essentially
       - Need to play with the hidden field for each field in the props section of the shapesheet.
       - May want to use actions to control
             for example
                 actions.abc.menu = "show ABC"
                 actions.abc = setf(getref(actions.abc.checked),1) + docmd(1312) + setf(getref(actions.abc.checked),0)
                 props.a.hide = actions.abc.checked
                 props.b.hide = actions.abc.checked
                 etc.
            what this does is set the checked field in actions.abc = 1 then call the shape data engine to show shapes
            when done, sets actions.abc.checked back to 0
         
            user would right click the shape and click on "show ABC"

Once you have your values in shape data, you can use the <roll your own function> to make calculations
I am not familiar with exact syntax but I believe its something like this
user.AAA = props.A
user.BBB = props.B
user.CCC = props.C
user.myroot = (-user.bbb + sqrt(user.bbb^2 - 4* user.AAA*user.CCC))/(2*user.AAA)    /say quad equation if memory serves

Then in the insert field (insert==>field==>user.myroot) you can show the result.

I would proto this using a simple square and get familiar with the shapesheet idiosyncrasies before jumping in on your shapes.

Again, only if there is no other way to import Excel in your drawings.

Of course, you could do this in VBA but that usually means security settings that your use may not have set.


       

wapperdude

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 3821
  • Ideas Visio-lized into solutions
Re: Problem with Excel OLE Objects turning into Box with X/Cross on it
« Reply #4 on: May 10, 2020, 12:24:15 AM »
Thought just occurred... rather than running Excel from inside Visio, you might pass parameter values from Visio into Excel, let it run as stand-alone, and then pass values back to Visio.  Might avoid the memory issue if that's the problem.


Just a thought.
Visio 2019 Pro