Access 2003 and Visio 2003 automation - putting text fields on a Visio drawing

How can you make a Visio diagram with a text shape for each record in an Access table using vba? The text comes from a text field in each record.
mrandersAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
this is not easy..
to have an idea how data from visio is saved in access
create a new drawing in visio, place any objects

from visio window
tools>export to database
select ms access database from the Name dropdown

when your done
open the access database and open the table...
0
mrandersAuthor Commented:
Thanks, I wanted to go the other way around and use vba in Access to produce a visio drawing.
0
Scott HelmersVisio Consultant, Trainer, Author, and DeveloperCommented:
The code below is fairly simple-minded but will certainly get you started. It assumes you already have a visio drawing, which it will open. Then it will create rectangles on the first page and set the text of each rectangle to a  text string.

To use this code in Access you'll have to set a reference to the Visio type library.
Sub CreateTextBoxes()
' Opens an existing visio drawing and adds rectangles, then adds text and sets font size
 
    Dim docCurrent As Visio.Document
    
    Dim shpCurrent As Visio.Shape
    Dim x1 As Double, x2 As Double, y1 As Double, y2 As Double
    Dim TextFromAccess As String
        
    Set docCurrent = Visio.Documents.Open("D:\temp\Testdoc.vsd")
    
    x1 = 2  'x coordinate of lower left corner of rectangle (in inches)
    x2 = 6  'x coordinate of upper right corner of rectangle (in inches)
    
    For i = 1 To 3
        'Replace line below with text from next access record
        TextFromAccess = "Text from Access record " & i
        
        y1 = 11 - (i * 2)   'y coordinate of lower left corner of rectangle
        Set shpCurrent = ActivePage.DrawRectangle(x1, y1, x2, y1 + 1)
        shpCurrent.Text = TextFromAccess
        shpCurrent.Cells("Char.Size").Formula = "12 pt"
    Next i
    
    ActiveWindow.DeselectAll    'turn off selection of last shape added to page
    
End Sub

Open in new window

0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

mrandersAuthor Commented:
Thanks, code looks great, but I get this error message:

"Microsoft Visual Basic Compile error: User-defined type not defined"

On this statement:

"docCurrent As Visio.Document"

Do I need to link to a library?
0
mrandersAuthor Commented:
I added this type library in the Access 2003 vba editor under Tools\Refences:
"Microsoft Visio 11.0 Type Library"

Now I get this error message when I try to run CreateTextBoxes():
"License information for this component not found.  
You do not have an appropriate license to use this functionality in the design environment."
0
Scott HelmersVisio Consultant, Trainer, Author, and DeveloperCommented:
Interesting -- I assume you have a valid, licensed copy of Visio on the PC where you're running this code? The error message you're getting sounds like something in Windows or VB thinks you don't.

Can you run my code in Visio directly? I'd try that if you haven't, just to make sure it executes there.
0
mrandersAuthor Commented:
Yes, I have valid licences for both Access 2003 and Visio 2003 at work and at home. I can run the code directly in Visio but not in Access.
0
Scott HelmersVisio Consultant, Trainer, Author, and DeveloperCommented:
This is the first time I've tried to run Visio code in another application -- I've only done it from within Visio in the past. But I've experimented a bit and the modified code below declares a Visio object and does run correctly in Access on my PC.

In my experimenting I didn't get the same license-related error message you did but figured I'd pass this code along to see whether it works for you as it does for me.

Note that this code will start a new instance of Visio (it doesn't check for a running instance) and expects the file testdoc.vsd to be closed. Once it's working this way, you can add other parameters to modify either or both of those behaviors.
Sub CreateTextBoxes()
' Opens an existing visio drawing and adds rectangles, then adds text and sets font size
 
    Dim vsoApp As Visio.Application
    Dim docCurrent As Visio.Document
    
    Dim shpCurrent As Visio.Shape
    Dim x1 As Double, x2 As Double, y1 As Double, y2 As Double
    Dim TextFromAccess As String
        
    Set vsoApp = CreateObject("visio.application")
    Set docCurrent = vsoApp.Documents.Open("D:\temp\Testdoc.vsd")
    
    x1 = 2  'x coordinate of lower left corner of rectangle (in inches)
    x2 = 6  'x coordinate of upper right corner of rectangle (in inches)
    
    For i = 1 To 3
        'Replace line below with text from next access record
        TextFromAccess = "Text from Access record " & i
        
        y1 = 11 - (i * 2)   'y coordinate of lower left corner of rectangle
        Set shpCurrent = vsoApp.ActivePage.DrawRectangle(x1, y1, x2, y1 + 1)
        shpCurrent.Text = TextFromAccess
        shpCurrent.Cells("Char.Size").Formula = "12 pt"
    Next i
    
    vsoApp.ActiveWindow.DeselectAll    'turn off selection of last shape added to page
    
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mrandersAuthor Commented:
Great!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.