Communicating with Excel

[JamesHall]

I am wondering if there is a way of transfering data between a shapesheet and
an Excel Spreadsheet using VBA. I can't seem to find any documentation on how
to do this. Any suggestions on how this can be done and any example pieces of
code would be very much appreciated!!

many thanks,
James

 

[Chris Roth [ Visio MVP ]]

Nothing so automatic as you might wish - ie: no Export ShapeSheet To Excel
function. The ShapeSheet and Excel are alike in appearance only : )

But it's not so bad:

With Excel, you manipulate cells using Range objects, primarily.
With Visio, it's all about the Shape.Cells( cell_name ) or Shape.CellsSRC(
section, row, column ) methods. There's plenty on these in the Visio
developer help that you can install with Visio. Simply stated, .Cells gives
you access to the whole ShapeSheet. You can get the .Formula of a cell, or
the ResultIU (numeric, in inches), or formatted .Result( unit_code )

Examples:

xl.Range.Text = Shape.Cells("Prop.Cost").Formula
xl.Range.Text = Shape.Cells("Width").ResultIU

An easy way to find a cell name is to click in some other cell, type "=",
then click in the desired cell. The name of the cell you want will appear as
the beginning of a formula in the cell you started typing in - just like
Excel. Hit ESC to throw away your edits, so you don't mess up the shape!

--

Hope this helps,

Chris Roth
Visio MVP

 

[JamesHall]

Chris,

Thanks for getting back to me. I am ok with manipulating excel when using
vba through excel and visio when using vba through visio. However, I have
some vba code in my Visio Document and I want it to go to an Excel
spreadsheet, open it and then move around the spreadsheet doing various
tasks. I wonder could you give me some starters on opening an excel document
from visio vba.

For example in the couple of lines you showed, how would i define xl?

Hope I've been a bit clearer in my question this time.

Thanks,

James

 

[Microsoft Communities]

you might try the example over here..
http://msdn.microsoft.com/library/en-us/dnvisio00/html/netconfig.asp?frame=true
al