Get the value from one cell in Excel and display it as "text" in a visio shape

[Jaco]

I want to read the value of a specific cell in an EXCEL spread sheet and use it as the text of a shape in Visio.

[Lars-Erik]

As you posted this in Programming and Code i suppose you want to do this using VBA?

Code Select Expand

Dim appExcel As Excel.Application
Dim xlBook As Excel.Workbooks
Dim xlSheet As Excel.Worksheets

Set appExcel = CreateObject("Excel.Application")
appExcel.Visible = False
' not sure what you want to do with the excel, think this will hide it, not 100% sure
Set xlBooks = appExcel.Workbooks
Set xlbook = xlBooks.Open("C:\File.xls")
' Open the excel file you need

Application.ActiveWindow.Selection.Item(1).Characters = appExcel.ActiveCell.Cells(1, 1)
'replate this first bit with the shape you need
'change the 1,1 to get a different Excel cell

appExcel.Quit
Set appExcel = Nothing
End Sub


Think something like this should do the trick, or atleast get you really close.
Don't forget to incluse Excel into the refferences of your VBA project:
VBA Editor -> Tools -> References

- Lars

Edit: forgot a bit of code... my bad

[Jaco]

 Thank you, I managed to "set" the text of a shape, based on the value in a specific cell in an EXCEL worksheet. When trying to set the width and Height, I discovered that you have to set it in Inches even though my program is set-up in mm. I now want to set the fill cover and maybe program a gradient fill, do you have any idea about that. The code I used is as follows:




Dim xlApplication As Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim FileName As String
    Dim MyShape As Visio.Shape
   
    FileName = "C:\My Documents\Excel-to-Visio.xls"
    Set xlApplication = New Excel.Application
    Set xlWorkBook = xlApplication.Workbooks.Open(FileName)
    Set MyShape = Visio.ActivePage.Shapes("ShapeName")
   
    MyShape.Text = xlApplication.Worksheets("Sheet1").Range("RangeNameOfCell")
    MyShape.Cells("Width") = CDbl(xlApplication.Worksheets("Sheet1").Range("Width")) / 25.4 'CDbl to get text to double and /25.4 because Visio want the value in inches.
   
   
    xlWorkBook.Save
    xlWorkBook.Close

[Lars-Erik]

Code Select Expand

MyShape.CellsSRC(visSectionObject, visRowFill, visFillForegnd).FormulaForceU = "THEMEGUARD(RGB(0,255,0))"
'Sets color 1 (foreground)
MyShape.CellsSRC(visSectionObject, visRowFill, visFillBkgnd).FormulaForceU = "THEMEGUARD(RGB(255,0,0))"
'Sets color 2 (background)
MyShape.CellsSRC(visSectionObject, visRowFill, visFillPattern).FormulaU = "31"
'Sets the fill pattern, use right-click menu, format, fill to find out what numer does what
Myshape.CellsSRC(visSectionObject, visRowFill, visFillForegndTrans).FormulaU = "0%"
'Sets transparency, for the foreground, isn't necessary to set
Myshape.CellsSRC(visSectionObject, visRowFill, visFillBkgndTrans).FormulaU = "0%"
'Sets transparency, for the background, isn't necessary to set

This should set a gradient fill, with 2 colors red and green. Tweak with the RBG collors etc.
Basicly it just sets the same items in the shape's shapesheet as the rightclick menu -> format -> fill, form does.

- Lars

[Jaco]

Thanks Lars, got it now. Something that still puzzles me, is why the values for the "width" and "Height" needs to be in inches? .....or am I doing something wrong. Everything is setup for mm, but if I enter a mm value in the spreadsheet, it is out by factor 25.4 (mm/inches).

Thanks for your time.

Jaco

[Lars-Erik]

Are you sure you started a Metric drawing... not a US drawing?
I don't think there would be any other explanation, or a way to switch it, you should copy paste all into a new metric drawing.

US templates also have other default page size I think...


A post about metric, imperial by Chris.

[Paul Herber]

Code Select Expand


    MyShape.Cells("Width") = CDbl(xlApplication.Worksheets("Sheet1").Range("Width")) / 25.4


Using the Cells method with just the cellname is the problem here, this defaults to using the Cells("xxxx").Value property which takes a value in internal units (inches). There are several ways to get round this, try ..

Code Select Expand


    MyShape.Cells("Width").Formula = CDbl(xlApplication.Worksheets("Sheet1").Range("Width"))  & "mm"


[nkafley.brt]

hi, could anyone please explain me step by step, as i need to replace 4000 names in visio using excel.
1. my visio text name as TABLE_1,TABLE2,TABLE3
2. In excel  cell A1,i have the same visio names information, TABLE1,TABLE2,TABLE3 etc
3. In excel  cell A2, i have TABLE1_texas, TABLE2_california, TABLE3_ottawa which i want to replace in visio
kindly suggest how can i replace all the TABLE1, TABLE2 and TABLE 3 with the new name that I have in excel A2.

[Thomas Winkel]

Please try the attached tool.
Then Ribbon -> Tools -> Replace.

Source Code is here:
https://github.com/ThomasWinkel/VisioShapePosition