Create Visio document from Excel using VBA

[bydganwil]

Hi there,

I have a spreadsheet with 20 rows of data with just 1 column. I need to create a visio document for each row and display the data within it. I am not concerned about how the data is displayed within Visio.

I hope somebody can help.

Rob

 

[Bond7042]

It would be really helpful, if anybody who is a part of this forum can help me through my request.

Thanks again!!

 

[sebhoo]

Hi Sayre,

Thank you very much for the effort. When I try to run the code, I get a 'compile error: user-defined type not defined'. What can I do about this? Can you help me out with this?

 

[Sayre]

Sebhoo, did you add a reference to the visio application in your VBE?

Phil actually put some instructions for this in earlier versions of his script:

'If the error "User-defined type not defined' is displayed you must add a
'Reference to the Visio Application to the VBA Environment as follows:
'1) From the Microsoft Visual Basic window menu: Tools | References
'2) Scroll to and select 'Microsoft Visio x.x Type Reference Library
'3) Click OK.
'4) From the Microsoft Visual Basic window menu: File | Close and Return to Microsoft Excel
'Start code with the worksheet containing the array as the Active Worksheet

 

[kramerdk]

I appreciate all the help from this forum and thread.

I have 250 people in my organization and I'm trying to create a Visio chart that reflects the correct hierarchy, but using different shapes that aren't natural to Visio. I've created a custom stencil and have been playing with earlier versions of scripts from this forum but I'm running into a strange issue.

Here's my data:

Name Reports To Title
David EVP
Bob David SVP
Karen David SVP
Doug Karen SD
Beth Karen Manager


I'd like connecters between the top person down to the next person, but also use the custom shape. The import wizard will do it but not use the correct shapes for our company, and doesn't like the number of people we have.

I've been playing with this code which just looks at their basis name (not the other two columns), but when I use natural shapes, e.g., diamonds, triangles, etc. it works perfectly; yet when I use my custom shapes the names all get added to 1 or 2 shapes and the remaining ones are blank.

Sub VisioFromExcel()

    Dim AppVisio As Object
    Dim vsoCharacters1 As Visio.Characters
    Dim lX As Long
    Dim dXPos As Double
    Dim dYPos As Double
    Dim xs As Double
    Dim ys As Double
    
    
    'Const visSectionCharacter = 3
    'Const visCharacterSize = 7
    
    Set AppVisio = CreateObject("visio.application")
    AppVisio.Visible = True
    
    AppVisio.Documents.AddEx "", visMSDefault, 0 'Open Blank Visio Document
    AppVisio.Documents.OpenEx "Custom1.vssx", visOpenRO + visOpenDocked   'Add Basic Stencil
    
    dXPos = AppVisio.ActivePage.PageSheet.Cells("PageWidth") / 2
    dYPos = AppVisio.ActivePage.PageSheet.Cells("PageHeight") / 2


    xs = 0


    For lX = 1 To Cells(Rows.Count, 1).End(xlUp).Row
    
        
       AppVisio.Windows.ItemEx(1).Activate
       AppVisio.ActiveWindow.Page.Drop AppVisio.Documents.Item("Customs1.vssx").Masters.ItemU("SVP"), dXPos +xs, dYPos
    
        Set vsoCharacters1 = AppVisio.ActiveWindow.Page.Shapes.ItemFromID(lX).Characters
        vsoCharacters1.Begin = 0
        vsoCharacters1.End = 0
        vsoCharacters1.Text = CStr(Cells(lX, 1).Value)
        Debug.Print vsoCharacters1.Text
        
        AppVisio.ActiveWindow.Page.Shapes.ItemFromID(lX).CellsSRC(visSectionCharacter, 0, visCharacterSize).FormulaU = "24 pt"
        
        xs = xs + 1
    
    Next
    
    Set AppVisio = Nothing
    
End Sub

Thoughts on why I am getting all the names in 1 or 2 of the custom shapes? How hard would it be to do what I'm looking to do at the top? I could also break the 250 up into separate organizations so that it limits the number of shapes/people in one organization.

 

[cdjones]

I'm trying to connect two complex stencils in Visio from Excel . What is the best way to identify the connection points ?

 

[pbornemeier]

Activate the connection point tool. It shows small X where the connection points are and you can add more if needed.

 

[cdjones]

Activate the connection point tool. It shows small X where the connection points are and you can add more if needed.

I'm trying to do this from excel VBA. I need to link complex stencils (123) and (456) in example below in sequence. I have two connection points at each edge, and need to connect as shown below. How do I identify the edge shapes or connection points within a group that comes from a stencil ?

 

[cdjones]

Image for my post above

 

[pbornemeier]

I don't know. Post in the forums at Visio Guy and see if they can tell you how to do it in Visio VBA, then use the earlier examples in this post to modify that code to run from Excel.

 

[cemdemir]

Dear Phil,

When i read this item about create visio from excel, i am so excited. Because i always try to use work with excel first than draw visio flowcharts.

I have process steps in excel as you see in the attached file. this tell which step is process or decision; step goes to another one it is also mentioned.

I also add visio flowchat which i draw manual and i add step explanation myself.

How can i draw flawchart in visio automatically when i link the excel file? Is it possible? I use excel file always same rows and coloumns same titles.

Could you please help me, how can i draw automatically in visio from excel process below.

C:\Users\demirtas_c\Desktop\2016-09-26_232948.jpg

Process Step	Flow	Department	Explain	go to process step	go to process step (2)
1	Start	Final Inspection	inspection starts	2
2	Process	Final Inspection	detect wider(10cm more) or narrow width	3
3	Decision	Final Inspection	is it sample order or production order ?	4	5
4	Process	Final Inspection	if it is sample order inspect as 1st choice and cut as 2nd choice	12
5	Process	Final Inspection	inspect and cut as 1st choice, do not block the pieces	6
6	Data	Final Inspection	inform QA by e-mail	7
7	Data	Quality Assurance	analyse similar qualities and previous productions then inform Sales by e-mail	8
8	Decision	Customer Service	is it ok or not?	9	10
9	Process	Quality Assurance	if the width is ok, note in material and inform Final Inspection for the next orders	16
10	Process	Quality Assurance	ask Product Development to create new material	11
11	Process	Product Development	create new material	16
12	Data	Sample Room	inform sales about nonconform width by e-mail	13
13	Decision	Customer Service	is it ok or not?	14	15
14	Process	Sample Room	if the width is ok, note in material	16
15	Process	Customer Service	create new material to reach requested width(new sample order)	16
16	End	x	End

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>