How to "SIMPLY" Export a Visio Data Value (1 Cell) to an Excel Range(1 Cell)

[SteveM]

I'm lost. I'm not a programmer, but an optimization/simulation
modeler who often builds models using Excel as the User Interface for
model and data management. I can program fairly well in VBA for
Excel. I having been playing with Visio 2007 Professional as a
possible interface because my models usually map to real objects so
being able to adjust parameter values using Visio Shapes would be very
appealing to clients.

OK, so enough about me. I've looked and looked and can't for the life
of me find some end to end code that shows me how I can link 1 data
cell in a Visio Shape to 1 Range cell in an Excel workbook. Such that
a change in the Visio cell will update the workbook cell value.

I understand all of the one-way Excel to Visio data linking
functionality. But this apparently simple chore of going the other
way leaves me mystified.

Can anybody help me with advice and/or a reference to not too
complicated code on either the Visio and/or Excel end?

I.e. I don't want to build a BOM app. I just what say, "youExcelCell
= youVisioCell" Once I have that down, I'm pretty sure I could figure
out the rest.

Thanks Very Much,

SteveM

 

[Al Edlund]

Well, there's basically two pieces to your challange.

The first piece is recognizing that something has changed in the drawing, so
you will need an event handler. Typically this is done using an addadvise
piece of code that watches for a cellchanged or formula changed event (for
this I prefer the formula changed event). Once you have a pointer to what is
changed you get to read/evaluate what you want to do with the data (I suspect
you will save the data in a custom property field in your shapes).

The second piece of course is saving the data to a specific excel cell.
There is very little (if any) difference between what you are asking for and
what a BOM application can do.

Both of these components have examples in the Visio SDK or MSDN programming
examples (look under office development). The vb examples are close enough to
VBA that you should have no problems with them given your background in
Excel.

Al

 

[SteveM]

Well, there's basically two pieces to your challange.

The first piece is recognizing that something has changed in the drawing, so
you will need an event handler. Typically this is done using an addadvise
piece of code that watches for a cellchanged or formula changed event (for
this I prefer the formula changed event). Once you have a pointer to what is
changed you get to read/evaluate what you want to do with the data (I suspect
you will save the data in a custom property field in your shapes).

The second piece of course is saving the data to a specific excel cell.
There is very little (if any) difference between what you are asking for and
what a BOM application can do.

Both of these components have examples in the Visio SDK or MSDN programming
examples (look under office development). The vb examples are close enough to
VBA that you should have no problems with them given your background in
Excel.

Al

Al, thanks for the response. I checked the BOM reference and
unfortunately the Excel link is coded in C. There is a link at the
bottom of the tutorial called, "Automating Excel Using the Excel
Object Model" but it's dead.

I'm getting lost in the weeds of links that have not provided me with
any real clarity on this subject. Are there any other references that
you can recommend?

One last point, the models I build are not used to support business
unit operations directly. They provide more planning and analysis
value. So are only used episodically by a small number of people.
That's why I'm looking for more simple functional way to do things.

Thanks Again,

Steve

 

[Al Edlund]

Steve,
I understand 'getting lost in the weeds'. Try this

http://msdn2.microsoft.com/en-us/library/aa140253(office.10).aspx

The demo shows reading a database, getting data from shapes, sending it off
to excel. Part of our challange is that often the professional developers
are using some 'c' derivative and we are using vb. Hope this helps,
al

 

[SteveM]

Steve,
I understand 'getting lost in the weeds'. Try this

http://msdn2.microsoft.com/en-us/library/aa140253(office.10).aspx

The demo shows reading a database, getting data from shapes, sending it off
to excel. Part of our challange is that often the professional developers
are using some 'c' derivative and we are using vb. Hope this helps,
al

Al,

Thanks again so much for the effort. I'm going to work through the
link tomorrow and see if I can get them talking to each other. If you
could check the site maybe Saturday or Sunday to see if I have any
follow up questions or perhaps have left a suicide note, that would be
great.

Regards,

SteveM

 

[Al Edlund]

da nada, good luck,
al

Al,

Thanks again so much for the effort. I'm going to work through the
link tomorrow and see if I can get them talking to each other. If you
could check the site maybe Saturday or Sunday to see if I have any
follow up questions or perhaps have left a suicide note, that would be
great.

Regards,

SteveM