Excel in Visio 1

[Alcedes]

I am designing something that will be using a large amount of data as variables. And based on user selections, it will be drawing pages to their specifications.

I have decided to do this by using an excel spreadsheet off on its own page to pull data from.

What I need to know is how can I for example:

I want to have visio look at page "DATA" and look through the spreadsheet on that page called "BANK" and take cell "A7" and place it in a combo box called cmbCombo1.

What would that command look like?

Do I need to do anything else to make that command possible?

 

[Alcedes]

This is the code I am currently using:

Dim xlSheet As Excel.Worksheet

Set xlSheet = Application.ActiveDocument.Pages.ItemU("DataFile").Shapes("DataBank").Object.Worksheets(1)

Application.ActivePage.Shapes("Title").Text = xlSheet.Cell(A2)


I am getting a "user-defined type not defined" error. This means in other programs that I have not referenced the object i am referring to. That is normally under

Tools --> References in most other programs. I cannot even find a way to reference things in Visio.

 

[TheInsider]

In the VBA code window there should be Tools->References menu item. You could also use late-binding, instead...

    Dim objApplication As Object
    Set objApplication = GetObject(, "Excel.Application")
    If objApplication Is Nothing Then
        Set objApplication = CreateObject("Excel.Application")
    End If

However, why not just use an ADO Recordset? Then, you can use SQL queries to access named ranges or even specific columns. If you Google for "VBA ADO Excel", I'm sure you'll find a good tutorial.

 

[Alcedes]

I am going to stump you sooner or later, Insider!

 

[TheInsider]

LOL I wouldn't doubt that ;-). Until recently, I only used Visio for making UML diagrams and never used Excel for anything. Then, my employer asked me to do something similar to what you're doing, so I had to learn how to read and write to Excel from Visio. As a database programmer, I was thrilled to see that I could use ADO to interface with Excel via SQL.

 

[Alcedes]

Nice.