Visio is quite popular drawing tool. Many tech companies are using it to produce various drawings.
Using Visio drawings as a data source for database load presents a challenge as the information on the drawing is unstructured and depends heavily on user visual interpretation.
A relatively simple solution to get data from a Visio drawing is to somehow ask the user to select data that needs to be collected. For example cards in telco device - if you have specific shapes for cards devices and slots, you can possibly analyse these and collect necessary information. However if the drawing is free form - boxes with text you need to ask user to select each card and corresponding slot where that card resides and collect user selection.
Here is an example of collecting device and card information from a free form Visio drawing.
Now when you look at the drawing, you know how many cards, slots, what is device name etc. but how do you export all this to a .csv file.
Here is a way to do that ...
First create a new stencil file, I called it "Data Collect". Added one master shape there and called it "Collect Device Info".
Defined "Collect Device Info" shape data. I've added "Device Name" and "Number of Slots" so when user selects device name on the drawing I store it. Number of slots is configured for user to enter the number of slots that are on the drawing (device) when shape is dropped. I use the entry as iterator to collect data later.
Next needed some VBA code to do the heavy lifting. To make the VBA code file independent, make it part of the stencil - ThisDocument module of the Data Collect stencil. This way the VBA code becomes available when the stencil is being used, added to the drawing file.
In this module, add some global variables:
Dim WithEvents appObj As Visio.Application
Dim WithEvents pageObj As Visio.Page
Dim devname As String
Dim numSlots As Integer
Dim currSlot As Integer
Dim boolColled As Boolean
Dim shpCollect As Visio.Shape
Add Sub to initialize global variables:
Sub Init()
Set appObj = Visio.Application
Set pageObj = ActivePage
Set shpCollect = Nothing
boolCollected = False
End Sub
Add Initialize on Document Opened event
Private Sub Document_DocumentOpened(ByVal doc As IVDocument)
Init
End Sub
When shape is added, if it is "Collect Device Info" shape, initialize a shape object - shpCollect and boolean indicating if collection process is completed.
Private Sub appObj_ShapeAdded(ByVal Shape As IVShape)
' if adding new collection shape
' get ready to collect
Debug.Print "appObj_ShapeAdded " & Shape.Name
If Shape.Name = "Collect Device Info" Then
Set shpCollect = Shape
boolCollected = False
End If
End Sub
SelectionChanged event fires up when user selects something on the VIsio drawing. Main logic to collect data is here. User is guided as to what needs to be selected by changing the text on the "Collect Device Info" shape.
- User selects device name
- Iteration over number of slots entered when "Collect Device Info" shape was dropped on the drawing. Each iteration user selects slot and corresponding card for it and collected card - slot info is added to the shapes shapeSheet.
- Export collected information to CSV file
Private Sub appObj_SelectionChanged(ByVal Window As IVWindow)
' MsgBox "Selection Changed"
Dim winShp As Shape
Dim cellObj As Visio.Cell
Dim shp As Shape
If boolColled Then
Exit Sub
End If
' no collection shape - no action
If shpCollect Is Nothing Then
Debug.Print "shpCollect Is Nothing"
Exit Sub
End If
Debug.Print Window.Selection(1).Text
Set winShp = Window.Selection(1)
' do nothing if user selected collection shape
If winShp.Name = "Collect Device Info.35" Then
Exit Sub
' do nothing if collection scompleted
ElseIf boolCollectionShape Then
Exit Sub
End If
' selection is not shape collection shape and text is to get the device name
If winShp <> shpCollect And shpCollect.Text = "Select Device Name" Then
' ask user to select card
shpCollect.Text = "Select Card"
Set cellObj = shpCollect.Cells("Prop.devName.Value")
cellObj.Formula = "=""" & winShp.Text & """"
numSlots = shpCollect.Cells("Prop.numSlots.Value").Formula
'Debug.Print "numSlots " & numSlots
currSlot = 1
Set cellObj = Nothing
' selection is not shape collection shape and text is to collect card
ElseIf winShp <> shpCollect And shpCollect.Text = "Select Card" And currSlot <= numSlots Then
shpCollect.AddNamedRow visSectionProp, "Slot" & currSlot, 0
Set cellObj = shpCollect.Cells("Prop.Slot" & currSlot & ".Value")
cellObj.Formula = "=""" & winShp.Text & """"
' ask user to select slot
shpCollect.Text = "Select Slot"
' selection is not shape collection shape and text is to collect slot
ElseIf winShp <> shpCollect And shpCollect.Text = "Select Slot" And currSlot <= numSlots Then
Set cellObj = shpCollect.Cells("Prop.Slot" & currSlot & ".Label")
cellObj.Formula = "=""" & winShp.Text & """"
currSlot = currSlot + 1
If currSlot <= numSlots Then
' ask user to select next card
shpCollect.Text = "Select Card"
Else
' collected everything, time to export to file
shpCollect.Text = "Export Collected"
ExportToCsv
boolColled = True
End If
End If
End Sub
At the end of the collection process the shapeSheet of Collect Device Info" shape should look like this.
The file with exported information would be located in the same folder where Visio file is, export file name would be same as Visio file name with extension .csv
