Excel driven custom shapes

Started by douglas.o, January 04, 2022, 11:36:52 AM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

douglas.o

Hi,

I have looked through the forum but can't find quite what i am looking for. I am in the process of simplifying a workflow for the generation of optical drawings. The raw data is exported from Zemax in an XML format, example file attached. Using VB I read this data into Excel and carry out some corrections via a user form (not all of the data that i want can be added in Zemax). Once this step is complete the VB then copies my data and pastes it into Visio. I am happy with how this is working but open to suggestions if anyone can see an easier way to do this? The next step that i wanted to automate was the generation of a 2D view of the lens. I have started to create a custom stencil of the basic lens shapes that i would like. Can anyone tell me if it is possible to take certain values from my Excel data to drive the shape?

I have attached my Excel file that i am using to generate my data. It still needs tidied up so apologies in advance.

Thanks,
Douglas

miless2111s

There are two ways to connect Excel to Visio to allow Excel to drive the display of shapes in Visio.
1) The older (and hence more compatible) method of using the Database wizard.  This allows you to assign data in a defined set of columns in Excel to a shape or shapes in Visio.  You can get around the restriction of the number of columns of data using 3 User Data fields which can be split apart using left/mid/right type expressions.
2) The Newer data link functionality which is only available in Professional versions of Visio.  This allows for more columns of data and I believe faster updates etc.  I am only starting to explore this option as backward compatibility has been more important until recently.

Using either of these methods you can control where a shape is positioned on the page, what size it is, how it looks etc.  If you have a custom shape you can start to manipulate individual edge points of the shape to control the shape of it.

When the data has updated in excel, close the excel engine and update the data in your linked Visio file...

douglas.o

Hi miless2111s,

Than you for the response. I realise now that i have missed a few details.

Not all users will have Visio Pro. I had initially set up my template sheet with a data link to my sheet. However when i tested this with a few users it became evident that we would need to update to Pro. To get this rolled out will be a massive hassle (although perhaps less so than trying to work around it. By using my VBA interface any user can import the data so i can get to the roll out stage faster and get some proper feedback on the tool.

I had after reading some threads fooled myself into thinking that i could create custom shapes and then just edit the shape data as required to get the end design as required. I realise now that it may not be as simple as i would have hoped.

miless2111s

Quote from: douglas.o on January 04, 2022, 04:50:46 PM
Hi miless2111s,

Than you for the response. I realise now that i have missed a few details.

Not all users will have Visio Pro. I had initially set up my template sheet with a data link to my sheet. However when i tested this with a few users it became evident that we would need to update to Pro. To get this rolled out will be a massive hassle (although perhaps less so than trying to work around it. By using my VBA interface any user can import the data so i can get to the roll out stage faster and get some proper feedback on the tool.

I had after reading some threads fooled myself into thinking that i could create custom shapes and then just edit the shape data as required to get the end design as required. I realise now that it may not be as simple as i would have hoped.
OK, so if all users don't have access to Visio Pro you will have to use the Database wizard method.  This works fine, the only limitations could be around that it sometimes has trouble with network or OneDrive files; to get around this I always use it on my C Drive only, however your experience may vary?  This in conjunction with your custom shape should work OK.