Excel Visio VBA Linkage

[Ray Batig]

Greetings,

In my job I generate chemical plant layouts. Most of the work is done in
Excel or ends up there. One thing I end up doing is using Visio to make
scaled layout drawings. If I had an excel spreadsheet with data in two
columns; one Name, and two size, can I use Visio VB to read the drawing
sheet scale, and then draw circles which are scaled to match the size in
Excel and contain the Name. I would propose that the names would be a letter
and two numbers for example C01, C10, T01, or T10.

Any guidance, or links for examples, on how I might do this are appreciated.

Thanks in advance for your help

Ray

 

[JuneTheSecond]

You might find some examples from the link of passed discussions, recommended
keyword to search in this site might be excel.

 

[Ray Batig]

Thanks June, however I did that and nothing talks about programming VBA in
Visio to get to Excel. Everything is done with databases and not what I was
asking for...

 

[JuneTheSecond]

In VBA, the first thing you might do is to link with Excel application
by the menu tool/references/ and select and check the Microsoft
Excel 11.0 Object Library, amd press OK.
Next thing is to write in your procedure that
Dim xlApp As Excel.Application
Set xlApp = CreateObject("excel.application")
Then youcan start coding for Excel.
For farther details, please refer to the help for CreateObject or related
items.

 

[Al Edlund]

there are examples of accessing excel on msdn in the library section

these two show how to export to excel
http://msdn2.microsoft.com/en-us/library/aa140253(office.10).aspx
http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnarvisio/html/msofficedemo.asp

the visio sdk has a flowchart example which reads excel (as I remember) and
draws the shapes

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

al

Thanks June, however I did that and nothing talks about programming VBA in
Visio to get to Excel. Everything is done with databases and not what I
was asking for...

 

[Ray Batig]

Thanks June,

A good start.

 

[Ray Batig]

Thanks Al,

All good information, however, aa140253 really gave me some code to look at.
I am somewhat proficient in Excel VB, however, Visio just doesn't do things
the same way.

there are examples of accessing excel on msdn in the library section

these two show how to export to excel
http://msdn2.microsoft.com/en-us/library/aa140253(office.10).aspx (a real
gem)
http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnarvisio/html/msofficedemo.asp
the visio sdk has a flowchart example which reads excel (as I remember)
and draws the shapes

http://msdn2.microsoft.com/en-us/library/aa140351(office.10).aspx ( uses
the wizard to connect applications )

al

 

[Al Edlund]

ray,
it's a heritage thing, visio corporation was purchased by microsoft a couple
of years ago and didn't come from the MS office group.
al

 

[dirk.devos]

Do you mind posting a copy of the code. I have some VBA code that will
read a text file and create a diagram but I am having some issues with
the linking of two shapes. I would like to see how your code does it.

 

[Ray Batig]

Thanks for the info Al. What I see is that Visio is also behind in VB code
parameters as well. However, I am sure there is someone out there who is
always willing to help out.

 

[Ray Batig]

Dirk,

As soon as I finish it I will post. It may not be the most eloquent,
however, it will work.

Ray

 

[dirk.devos]

Ray,

Thanks in advance.

Dirk,

As soon as I finish it I will post. It may not be the most eloquent,
however, it will work.

Ray

 

[Ray Batig]

Dirk,

Here is the code I pulled together. It assumes that you have your data in an
existing workbook and that the data (names) are in Named ranges[columns].
The short name [Tx or Cx]and diameter are to the right of the name columns,
hence the offsets.

Public Sub Draw_Circles()

' This routine draws circles (Tanks and Columns) based on data from Excel
Dim pageObj As Visio.Page
Dim shpObj As Visio.Shape, shp1obj As Visio.Shape
Dim localCentx As Double, ShapeHeight As Double
Dim localCenty As Double, ShapeWidth As Double, ShapeRadius As Double
Dim celObj1 As Visio.cell, celObj2 As Visio.cell
Dim dPageWidth, dPageHeight As Double
Dim appExcel As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim cell As Excel.Range
Dim FileName As String
Dim shpNam As String
Dim i As Double

On Error Resume Next

'Read the PageWidth and PageHeight properties.
dPageWidth = ActivePage.PageSheet.Cells("PageWidth").ResultIU
dPageHeight = ActivePage.PageSheet.Cells("PageHeight").ResultIU

'Set the file to be used
' !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
' Want this to be set up as a file dialog display but have not had
sucess yet
' !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

FileName = "C:\My Documents\Local Files\Plant Layout Basis.xls"

' Start Excel
Set appExcel = CreateObject("excel.application")
appExcel.Visible = True
Set xlBook = appExcel.Workbooks.Open(FileName)
Set xlSheet = xlBook.Worksheets("sheet1")
i = 1 ' set space counter

For Each cell In Range("Tank_Short_Name") ' Do tanks
shpNam = cell.Value
ShapeRadius = cell.Offset(0, 1).Value
localCentx = (dPageWidth / 8 + i * 10) ' circle center x coordinate
for placement
localCenty = dPageHeight / 10 ' circle center y coordinate for
placement

' Create the circle
Set shpObj = ActivePage.DrawOval(localCentx, localCenty +
ShapeRadius, localCentx + ShapeRadius, localCenty)
shpObj.Cells("LineColor") = 0 ' Colour the line Black -
(Black;White;Red;Green;Blue;Yellow)
shpObj.Cells("Fillforegnd") = 1 ' Use a fill colour of White

'Set the text of the circle
shpObj.Text = shpNam

shpObj.SendToBack
i = i + 1
Next

i = 1 ' reset space counter
For Each cell In Range("Column_Short_Name") ' Do Columns
shpNam = cell.Value
ShapeRadius = cell.Offset(0, 1).Value
localCentx = (dPageWidth / 2 + i * 10) ' circle center x coordinate
for placement
localCenty = dPageHeight / 10 ' circle center y coordinate for
placement

' Create the circle
Set shpObj = ActivePage.DrawOval(localCentx, localCenty +
ShapeRadius, localCentx + ShapeRadius, localCenty)
shpObj.Cells("LineColor") = 0 ' Colour the line Red -
(Black;White;Red;Green;Blue;Yellow)
shpObj.Cells("Fillforegnd") = 1 ' Use a fill colour of Yellow

'Set the text of the circle
shpObj.Text = shpNam

shpObj.SendToBack
i = i + 1
Next


appExcel.Quit
Set appExcel = Nothing

End Sub

 

[dirk.devos]

Ray,

Thanks for the code but it does not link the shapes. My problem is that
I can draw hundreds of shapes but I can not assign an ID to each so
that I can link a given two shapes after I have drawn all the shapes.

Dirk,

Here is the code I pulled together. It assumes that you have your data in an
existing workbook and that the data (names) are in Named ranges[columns].
The short name [Tx or Cx]and diameter are to the right of the name columns,
hence the offsets.

Public Sub Draw_Circles()

' This routine draws circles (Tanks and Columns) based on data from Excel
Dim pageObj As Visio.Page
Dim shpObj As Visio.Shape, shp1obj As Visio.Shape
Dim localCentx As Double, ShapeHeight As Double
Dim localCenty As Double, ShapeWidth As Double, ShapeRadius As Double
Dim celObj1 As Visio.cell, celObj2 As Visio.cell
Dim dPageWidth, dPageHeight As Double
Dim appExcel As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim cell As Excel.Range
Dim FileName As String
Dim shpNam As String
Dim i As Double

On Error Resume Next

'Read the PageWidth and PageHeight properties.
dPageWidth = ActivePage.PageSheet.Cells("PageWidth").ResultIU
dPageHeight = ActivePage.PageSheet.Cells("PageHeight").ResultIU

'Set the file to be used
' !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
' Want this to be set up as a file dialog display but have not had
sucess yet
' !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

FileName = "C:\My Documents\Local Files\Plant Layout Basis.xls"

' Start Excel
Set appExcel = CreateObject("excel.application")
appExcel.Visible = True
Set xlBook = appExcel.Workbooks.Open(FileName)
Set xlSheet = xlBook.Worksheets("sheet1")
i = 1 ' set space counter

For Each cell In Range("Tank_Short_Name") ' Do tanks
shpNam = cell.Value
ShapeRadius = cell.Offset(0, 1).Value
localCentx = (dPageWidth / 8 + i * 10) ' circle center x coordinate
for placement
localCenty = dPageHeight / 10 ' circle center y coordinate for
placement

' Create the circle
Set shpObj = ActivePage.DrawOval(localCentx, localCenty +
ShapeRadius, localCentx + ShapeRadius, localCenty)
shpObj.Cells("LineColor") = 0 ' Colour the line Black -
(Black;White;Red;Green;Blue;Yellow)
shpObj.Cells("Fillforegnd") = 1 ' Use a fill colour of White

'Set the text of the circle
shpObj.Text = shpNam

shpObj.SendToBack
i = i + 1
Next

i = 1 ' reset space counter
For Each cell In Range("Column_Short_Name") ' Do Columns
shpNam = cell.Value
ShapeRadius = cell.Offset(0, 1).Value
localCentx = (dPageWidth / 2 + i * 10) ' circle center x coordinate
for placement
localCenty = dPageHeight / 10 ' circle center y coordinate for
placement

' Create the circle
Set shpObj = ActivePage.DrawOval(localCentx, localCenty +
ShapeRadius, localCentx + ShapeRadius, localCenty)
shpObj.Cells("LineColor") = 0 ' Colour the line Red -
(Black;White;Red;Green;Blue;Yellow)
shpObj.Cells("Fillforegnd") = 1 ' Use a fill colour of Yellow

'Set the text of the circle
shpObj.Text = shpNam

shpObj.SendToBack
i = i + 1
Next


appExcel.Quit
Set appExcel = Nothing

End Sub

Ray,

Thanks in advance.

 

[Ray Batig]

Hi Dirk,

You are right. I didn't have a need to link the shapes so I didn't code it.
I was just interested in getting Visio VB to open Excel and automatically
read data and then draw circles based on the data. You can probably convert
some code from some examples found on Chris Roth's web page.

Ray

Ray,

Thanks for the code but it does not link the shapes. My problem is that
I can draw hundreds of shapes but I can not assign an ID to each so
that I can link a given two shapes after I have drawn all the shapes.

Dirk,

Here is the code I pulled together. It assumes that you have your data in
an
existing workbook and that the data (names) are in Named ranges[columns].
The short name [Tx or Cx]and diameter are to the right of the name
columns,
hence the offsets.

Public Sub Draw_Circles()

' This routine draws circles (Tanks and Columns) based on data from Excel
Dim pageObj As Visio.Page
Dim shpObj As Visio.Shape, shp1obj As Visio.Shape
Dim localCentx As Double, ShapeHeight As Double
Dim localCenty As Double, ShapeWidth As Double, ShapeRadius As Double
Dim celObj1 As Visio.cell, celObj2 As Visio.cell
Dim dPageWidth, dPageHeight As Double
Dim appExcel As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim cell As Excel.Range
Dim FileName As String
Dim shpNam As String
Dim i As Double

On Error Resume Next

'Read the PageWidth and PageHeight properties.
dPageWidth = ActivePage.PageSheet.Cells("PageWidth").ResultIU
dPageHeight = ActivePage.PageSheet.Cells("PageHeight").ResultIU

'Set the file to be used
' !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
' Want this to be set up as a file dialog display but have not had
sucess yet
' !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

FileName = "C:\My Documents\Local Files\Plant Layout Basis.xls"

' Start Excel
Set appExcel = CreateObject("excel.application")
appExcel.Visible = True
Set xlBook = appExcel.Workbooks.Open(FileName)
Set xlSheet = xlBook.Worksheets("sheet1")
i = 1 ' set space counter

For Each cell In Range("Tank_Short_Name") ' Do tanks
shpNam = cell.Value
ShapeRadius = cell.Offset(0, 1).Value
localCentx = (dPageWidth / 8 + i * 10) ' circle center x
coordinate
for placement
localCenty = dPageHeight / 10 ' circle center y coordinate for
placement

' Create the circle
Set shpObj = ActivePage.DrawOval(localCentx, localCenty +
ShapeRadius, localCentx + ShapeRadius, localCenty)
shpObj.Cells("LineColor") = 0 ' Colour the line Black -
(Black;White;Red;Green;Blue;Yellow)
shpObj.Cells("Fillforegnd") = 1 ' Use a fill colour of White

'Set the text of the circle
shpObj.Text = shpNam

shpObj.SendToBack
i = i + 1
Next

i = 1 ' reset space counter
For Each cell In Range("Column_Short_Name") ' Do Columns
shpNam = cell.Value
ShapeRadius = cell.Offset(0, 1).Value
localCentx = (dPageWidth / 2 + i * 10) ' circle center x
coordinate
for placement
localCenty = dPageHeight / 10 ' circle center y coordinate for
placement

' Create the circle
Set shpObj = ActivePage.DrawOval(localCentx, localCenty +
ShapeRadius, localCentx + ShapeRadius, localCenty)
shpObj.Cells("LineColor") = 0 ' Colour the line Red -
(Black;White;Red;Green;Blue;Yellow)
shpObj.Cells("Fillforegnd") = 1 ' Use a fill colour of Yellow

'Set the text of the circle
shpObj.Text = shpNam

shpObj.SendToBack
i = i + 1
Next


appExcel.Quit
Set appExcel = Nothing

End Sub

Ray,

Thanks in advance.


Ray Batig wrote:
Dirk,

As soon as I finish it I will post. It may not be the most eloquent,
however, it will work.

Ray
Do you mind posting a copy of the code. I have some VBA code that
will
read a text file and create a diagram but I am having some issues
with
the linking of two shapes. I would like to see how your code does
it.

Ray Batig wrote:
Thanks Al,

All good information, however, aa140253 really gave me some code to
look
at.
I am somewhat proficient in Excel VB, however, Visio just doesn't
do
things
the same way.


there are examples of accessing excel on msdn in the library
section

these two show how to export to excel
http://msdn2.microsoft.com/en-us/library/aa140253(office.10).aspx
(a
real
gem)
http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnarvisio/html/msofficedemo.asp
the visio sdk has a flowchart example which reads excel (as I
remember)
and draws the shapes

http://msdn2.microsoft.com/en-us/library/aa140351(office.10).aspx
(
uses
the wizard to connect applications )

al


Thanks June, however I did that and nothing talks about
programming
VBA
in Visio to get to Excel. Everything is done with databases and
not
what
I was asking for...

in
message
You might find some examples from the link of passed
discussions,
recommended
keyword to search in this site might be excel.