VBA to Control Viso from Access

NeilClark · February 15, 2018, 06:49:19 PM

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.

Surrogate · February 16, 2018, 06:11:26 AM

cross-post

Yacine · February 16, 2018, 10:22:29 AM

Sure you can.
What do you have so far?

NeilClark · February 16, 2018, 08:01:40 PM

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

NeilClark · 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

wapperdude · February 16, 2018, 08:44:36 PM

@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

NeilClark · February 16, 2018, 10:48:26 PM

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 · February 17, 2018, 02:02:43 AM

😀

Wapperdude

Surrogate · February 17, 2018, 03:22:08 AM

Neil,
We wait your db simple

Yacine · February 18, 2018, 11:08:35 AM

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?

NeilClark · February 19, 2018, 09:18:04 PM

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.

Dupes · April 09, 2018, 05:45:56 AM

Dear sir, any solution reached for this? Thanks

Yacine · April 09, 2018, 07:24:35 AM

@Dupes, what are you looking for exactly?

Dupes · April 09, 2018, 11:26:26 AM

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

Surrogate · April 09, 2018, 12:08:34 PM

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 ?