Edit linked Excel file from Visio.

[Default_User]

Hi,

I'm working on a project that involves a database of computers in an Excel file being linked to a Visio drawing. The database contains various information about the computers, and links them to shapes on the diagram using their MAC addresses.

So right now, everything is linked fine, and when I click "Shape Data" on a computer's shape, I get all the needed information about it.

If something is changed in the Excel file, I click refresh in Visio, and all the changes are applied. But I want to make it so that if the Shape Data is changed in Visio, I can apply the changes back to the Excel file.

I've been messing around with VBA code, but so far have gotten nowhere. I'm sure I can figure out most of it, but right now I'm stuck on two essential parts.

1.) How do I access a shape's "Shape Data" field? I know how to retrieve things like the width and height of a selected shape, but there doesn't seem to be a way of reading the Shape Data.

2.) How do I reference an external Excel file in the code? I've read a bunch of code sample about how to work with Excel files, but I can't find anything about how to work with a file that is not currently open. Is this possible?


Thanks.

[BreakfastGuy]

When you say "shape data" do you mean the data that the shape can access or the actual text within the shape?

[Default_User]

The data that the shape can access. The list of data you get when you:

Right click --> Data --> Shape Data

[Bob Phillips]

Can you post the Visio doc, I can't see Data>Shape Data when I right-click on a shape on my system.

[Default_User]

I can't send you the Visio file, so here's a picture of the Shape Data option in the tool bar. For me it's also there when I right click.

Also I have an excel file linked to the visio drawing, so when I click on Shape Data (with a linked shape selected), I get the data from a specified row in the excel file displayed on the screen.

I'm using Visio 2007 by the way.

[Bob Phillips]

Does this help?




Dim vsoShape1 As Visio.Shape

Set vsoShape1 = Application.ActiveWindow.Page.Shapes.Item(1)
With vsoShape1
MsgBox .CellsSRC(visSectionProp, 0, visCustPropsLabel).FormulaU & vbNewLine & _
.CellsSRC(visSectionProp, 0, visCustPropsType).FormulaU & vbNewLine & _
.CellsSRC(visSectionProp, 0, visCustPropsFormat).FormulaU & vbNewLine & _
.CellsSRC(visSectionProp, 0, visCustPropsLangID).FormulaU & vbNewLine & _
.CellsSRC(visSectionProp, 0, visCustPropsCalendar).FormulaU & vbNewLine & _
.CellsSRC(visSectionProp, 0, visCustPropsPrompt).FormulaU & vbNewLine & _
.CellsSRC(visSectionProp, 0, visCustPropsValue).FormulaU & vbNewLine & _
.CellsSRC(visSectionProp, 0, visCustPropsSortKey).FormulaU
End With

[Default_User]

Thaks! That helped a lot. This is the line that I needed:


.CellsSRC(visSectionProp, #, visCustPropsValue).FormulaU


Do you also know how to use VBA to work with an Excel file from within Visio?

I want to make it so that if the properties of a computer are changed from inside Visio, I can run a script, and they will also be changed inside the original Excel file.

I'm pretty sure I know how to use VBA to change the values inside the Excel WorkBook, but I can't find a way get access to the file.

I tried a few tutorials, and the code should look something like:


Dim ExcelFile As Excel.Application
Set ExcelFile = CreateObject("path/to/file")


But the examples don't seem to work.

[Dave]

This is VB6 code which I assume will work for Visio. HTH. Dave
To run an XL module code sub in a closed wb...

Dim objExcel As Object
Set objExcel = CreateObject("EXCEL.APPLICATION")
Set objWorkBook = objExcel.Workbooks.Open("C:\Wbname.xls")
objExcel.Run "Yourmacroname"
Set objWorkBook = Nothing
Set objExcel = Nothing

To manipulate the closed XL file...

Dim objExcel As Object, objWorkBook As Object
Dim objWorksheet As Object
Set objExcel = CreateObject("EXCEL.APPLICATION")
Set objWorkBook = objExcel.Workbooks.Open("C:\Wbname.xls")
Set objWorksheet = objWorkBook.Worksheets("Sheet1")
objWorksheet.Cells(13, 5).Formula = "A2*B2"
objWorkBook.Save
objWorkBook.Close
objExcel.Quit
Set objWorksheet = Nothing
Set objWorkBook = Nothing
Set objExcel = Nothing