Rather than dragging a set of shapes onto a drawing page from the master shapes in a stencil and then using the Visio database wizard to individually link each shape to an external data source (Access, Excel, etc.), is there a way to generate the shapes onto the drawing page directly from the external data source?
The situation involves a set of building materials that I have already created a stencil for, with master shapes that represent things like roofing sheets, gutters, posts, etc. I can drag a master roofing sheet onto the page and change its length and width within bounds (that I have set in the Master Shape Sheet), and I can choose a specific colour for this sheet from a fixed selection list of colours (drop-down) in the shape data.
I COULD place all the components onto the drawing page, one by one, then adjust the sizes and colours to what I need for THIS drawing and then export them via the reporting function to XL, or link them via the database wizard to an external data source. But this would have to be done one component at a time and is very time consuming and prone to operator error.
Now, the actual building components to be used are initially created in an Access database, complete with the specific lengths and widths (within the same Visio Master Shape bounds) and colour.
My question is, can I use MS Access to tell Visio to take each Visio Shape master that I need, in turn, and place a shape onto the page (doesn't matter where, I can move them later) based on that Master Shape definition and the data in Access for that record? I envisage this to be a one step action; like run an Access query or VBA Recordset that, in one operation, places all the shapes on the Visio page. An example of one of the items could be a master roof sheet that is 9 metres X 800 mm with no colour selected, but the roof sheet required by the Access database for this structure is 3 metres X 750 mm with a specific colour (red). That should be the shape that appears on the page.
As a final step it would be nice if the Visio Data could be synchronised back to the Access database so that if the operator decided change the length of the roof sheet on the Visio drawing, for instance, from 3 metres, to 4, this would also update the Access database record.
cross-post (https://social.msdn.microsoft.com/Forums/office/en-US/b22c6650-e97d-4055-9fb4-9542a0f5959a/vba-to-control-viso-from-access?forum=officegeneral#8ae0c5f9-6424-4056-b87b-493bcd5bcd19)
Sure you can.
What do you have so far?
Hi Surrogate,
Yes - I did put this into the Technet Forum, where your reply was very valuable indeed - thanks again for that. But also realise that, before I posted here on Visio Guy, I checked with Chris - the Admin - and he told me it was ok to cross-post anb told me to go ahead. I am aware that some forums don't like this, but here it is ok.
Neil
Hi Racine,
You will see from above that Surrogate gave me a clue (Drop-shape) as the answer to my question and I am following that up. Looks good so far. Thanks for your interest.
Neil
@All: while there is no rule against cross-posting, my complaint would be that none of the various forums track one another. Phew! What a mess that could be. So, Forum "A" might provide an answer, and the poster goes his merry way. Meanwhile, Forum "B" isn't aware that the post had been answered on a different site which results in some wasted effort at Forum "B". Posters never indicate they cross-post, let-alone have an answer.
There are of course, exceptions,and this looks like one of those cases.
So, for you cross-posters out there, it would be nice to let a forum know, especially when a topic has been answered else where.
Thanks and cheers, y'all.
Wapperdude
You make a good point Wapperdude. I can see that it would be quite annoying to spend a lot of time working on a post for someone when all the time they already had their answer from another forum. I'll make sure this does not happen again in my world.
Cheers....Neil
😀
Wapperdude
Neil,
We wait your db simple :)
Quote from: NeilClark on February 16, 2018, 08:04:18 PM
Hi Racine,
You will see from above that Surrogate gave me a clue (Drop-shape) as the answer to my question and I am following that up. Looks good so far. Thanks for your interest.
Neil
problem solved?
No, the problem is not solved yet, but the ball is in my court. I don't have the fully working database yet as I am still working at concept level. Once I figure out how to actually write the VBA code in Access that will select a particular stencil in Visio and then drop a shape from that stencil onto a drawing page with dimensions that are specified in the the VBA code, I will be able to built it. I am currently studying the syntax for writing this code.
As an example, I have a stencil with numerous shapes representing things like roof steel of various profiles (flat, corrugated, etc.). The stencil shapes have predefined sizes with bounding to prevent the from being stretched or reduced beyond certain limits and each with a selection of colours available in the Shape Data (I can do all that in the Shape Sheet). The default size of one roof sheet may be 5 metres X 800 mm. I am looking at writing the VBA code in access that will:
- Select the correct Visio stencil forroof sheeting
- Select the Corrugated Steel roof sheet shape and drop it on the page
- Designate the specific location (X-Y) on the page (different for each shape dropped)
- Resize the shape on the page to 4 metres X 600 mm
- Designate the colour selection from the Shape Data as Red
From Access I have already written the code that will open Visio, open a specific template, rename the Visio Drawing as the Job Number, open a page, place data into certain fields on the page, like Job, Name, Address.
Here is some of the code to give you an idea:
Dim FName As String, VisioApp As Object
Set VisioApp = GetObject(, "Visio.Application")
If VisioApp Is Nothing Then
Set VisioApp = CreateObject("Visio.Application")
If VisioApp Is Nothing Then
MsgBox "Can't connect to Visio"
Exit Sub
End If
End If
FName = LocalDb & "Projects\" & Me!ProjectNumber & "\" & ProjectNumber & ".vsdm"
VisioApp.Documents.Open FName '
VisioApp.Visible = True
VisioApp.ActivePage.Name = Me!ProjectNumber
VisioApp.ActiveDocument.Manager = EmployeeID.Column(1) & " - " & EmployeeID.Column(2)
VisioApp.ActiveDocument.Company = Customer
VisioApp.ActiveDocument.Description = InstallAddress & ", " & InstallCity
As I said, I am looking for the syntax to continue this code in order to drop a specific shape onto the page from a specific template (of which there area several) and resize and colour the dropped shape according to the VBA code.
Dear sir, any solution reached for this? Thanks
@Dupes, what are you looking for exactly?
Sir, I want to write a VBA code in MS Access to copy a Shape/Stencil from a Template Visio file A i have kept in a fix folder to a Visio file B i have kept in a different folder.
Also i wish to place the Shape /Stencil to a defined X-Y coordinates
Can you please help, Thanks
I also created a new topic for this
Hi, Dupes !
1.You want drop some shapes from some stencil (build-in or external storage of master-shapes) ?
For example you can place in db cells names of stencil and master-shape which you want drop ?
2. If you want copy shapes from template, you need know where shape placed: page and coordinates
or page and shape name
3. IMHO main trouble how define coordinates where you want place this shape ! Can you share your
ideas about this ?
Thanks for replying,
stencil are build in to visio itself
Project is basically to create a cabinet diagram with multiple components placed inside the cabinet.
So i have created templates for individual components. depending on the design of the cabinet and the components which will select using forms in ms access i wish to copy the these specific components from template to the final visio drawing
i am struggling on how to do this in MS Access VBA
Try something like this
Sub bbb()
Dim FName As String, VisioApp As Object
Set VisioApp = GetObject(, "Visio.Application")
If VisioApp Is Nothing Then
Set VisioApp = CreateObject("Visio.Application")
If VisioApp Is Nothing Then
MsgBox "Can't connect to Visio"
Exit Sub
End If
End If
Dim td As Object ' target visio document
Dim tp As Object ' active page in target document
Set td = VisioApp.Documents.OpenEx("c:\Test\change shape issue.vsd", visOpenRW) ' define your own target doc
Set tp = VisioApp.ActiveWindow.Page
Dim ts As Object ' define stencil
Dim tm As Object 'define master-shape for drop
Set ts = VisioApp.Documents("BASFLO_M.vssx") ' define your desired external stencil
Set tm = td.Masters.ItemU("Circle") ' define your own desired master shape
tp.Drop tm, 0, 0 ' instead "0,0" you need assign you real coordinates each time
' Do something else
End Sub
Thanks for your help,
I am getting an error at the following line:
Set tm = td.Masters.ItemU("Square")
Error says "Object cannot be found"
i am using following stencil:
Set ts = VisioApp.Documents("basic_u.vss")
Any idea, thanks again
Quote from: Dupes on April 09, 2018, 02:45:23 PMI am getting an error at the following line:
Set tm = td.Masters.ItemU("Square")
Error says "Object cannot be found"
This code try find master shape named "Square" in your target document (td). If your document don't have this master in it Document stencil you get error !
Quote from: Dupes on April 09, 2018, 02:45:23 PMi am using following stencil:
Set ts = VisioApp.Documents("basic_u.vss")
This code works only if this stencil already open
Please try this modified code
Sub bbb()
Dim FName As String, VisioApp As Object
Set VisioApp = GetObject(, "Visio.Application")
If VisioApp Is Nothing Then
Set VisioApp = CreateObject("Visio.Application")
If VisioApp Is Nothing Then
MsgBox "Can't connect to Visio"
Exit Sub
End If
End If
Dim wn As Object, chk As Boolean
chk = False
Dim td As Object ' target visio document
Dim tp As Object ' active page in target document
Set td = VisioApp.Documents.OpenEx("c:\Test\change shape issue.vsd", visOpenRW) ' define your own target doc
Set tp = VisioApp.ActiveWindow.Page
Dim ts As Object ' define stencil
Dim tm As Object 'define master-shape for drop
For Each wn In VisioApp.Windows ' iterate all open windows in current Visio session
If wn.document.Name = "BASIC_M.vssx" Then chk = true
Next
' define external stencil (vssx document) as variable named ts
If chk = false Then ' if no found opened stencil, open this stencil
Set ts = VisioApp.Documents.OpenEx("basic_m.vssx", visOpenRO + visOpenDocked)
Else
Set ts = VisioApp.Documents("basic_m.vssx") ' define opened stencil as variable named ts
End If
' define master shape from extertan stencil (ts)
Set tm = ts.Masters.ItemU("Circle") ' define your own desired master shape
tp.Drop tm, 0, 0 ' instead "0,0" you need assign you real coordinates each time
' Do something else
End Sub
Awsm
I worked like a charm, thanks :)
is it also possible to resize the droped shape?
Replace this code
tp.Drop tm, 0, 0 ' instead "0,0" you need assign you real coordinates each time
' Do something else
with that code
dim shp as object
set shp.= tp.Drop (tm, 0, 0) ' instead "0,0" you need assign you real coordinates each time
shp.cells("Height") = 7 ' set height to 7 in
shp.cells("Width") = 40 ' set width to 40 in
Thanks :)
It working awsm !
So this is exactly what I have been working on (on and off) for a few years, and have a _lot_ of code to do this (with probably lots and lots of things that you don't necessarily care about). I am happy to help round out/guide your effort depending on where you need help.
The 'DynamicDrawings' tools I have been building is used to visualize system-to-system interactions (think Enterprise Architecture interface diagrams). The general method I use is to connect to an Access database (from Visio, not the other way around) and open 3 queries: 1 for containers, 1 for shapes, and 1 for links (in that order).
Connecting to Access from Visio is pretty gross (in my view), because I have (at times) spent long hours trying to debug a problem only to find that the SQL I have written isn't kosher for access through ADO.
The queries have some generic columns to help me define the visual attributes (such as 'shapetype' and 'theme'), and all the translation happens in the Access SQL queries. e.g. a field like 'SYSTEM_STATUS' is used to determine the shapetype (the stencil to use).
The Shapes query would need to identify which container it belongs in. Likewise, the Links query needs to identify the source/destination shapes that the line should connect to.
For my purposes, I have a lot of additional code to assign shapes to layers and a bunch of code to change how the layers are visualized (since turning layers on and off via Visio's standard methods don't work the way I want since shapes will be in more than one layer). To make this all work, I dynamically add/modify the shapesheet data for the shapes I drop on the page.
I can provide some stripped down snippets if you need, just tell me which parts.
Is it also possible to link data from table to visio? really?
Like can we also define attributes linked to access database?
This video about build-in Visio Data linking.
Also you can create your custom solution, or use solutions from 3rd developers (like as JM in this thread)
Quote from: Surrogate on April 12, 2018, 09:19:49 AM
This video about build-in Visio Data linking (https://www.youtube.com/watch?v=zqBN3soVQQE)
Also you can create your custom solution, or use solutions from 3rd developers (like as JM in this thread)
So one other comment - I started down the road of trying to link data using Visio's built-in mechanisms. I found there was too much 'special' behavior I wanted, that I ended up coding the data portion myself. (e.g. whether or not I wanted all fields in all shapes, or just some fields in some shapes based off of certain values..)
I also found I wanted to control what shapes were created and I wanted to tweak the shapesheet cells dynamically.. thus I ended up at a custom solution..