Timelines and swimlanes from Excel to Visio

[Legion]

Hi everybody,

I am new to this forum and I also am pretty new to Visio 2010. I am using Excel 2010 and doing a lot with VBA and I thought I can just start a small project to use data from Excel to create workflows in Visio. However due to my lack of knowledge of the Visio internals I am still at the very beginning and I guess this projected is doomed to fail. I searched the web but I only find fragments of information and I am not making progress. There are some examples of using data from excel to create shapes in Visio, however I find almost no examples dealing with timelines and swimlanes.

I am using complex Excel files to calculate workflows for customers. By saying "workflow" I am referring to a timeline with several intervals showing the occupancy of a analytical device. The timeline itself is placed in a swimlane. At the moment I create wokflows manually however working with Visio is quite a pain in the ass, especially if you have to modify intervals and everything shifts. I thought it should be possible to generate the worklfow(s) automatically.
I am able to call Visio out of Excel and I also can place a swimlane and a timeline within the swimlane. I also managed to to get an interval onto the timeline. However I am not able to change the timeline's properties and I run into Excel errors I cannot get rid off. This is the code in Excel I have so far:

Code Select Expand

Sub VisioFromExcel()

Dim AppVisio As Object
Dim theTimeline As Visio.Shape
Dim dYPosTL As Long
Dim xPosSL, yPosSL, xPosTL, yPosTL As Double

Set AppVisio = CreateObject("visio.application")
AppVisio.Visible = True
'AppVisio.AlertResponse = 1

AppVisio.Documents.AddEx "", visMSDefault, 1 'Open blank document
AppVisio.ActiveWindow.Page.PageSheet.CellsSRC(visSectionObject, visRowPrintProperties, visPrintPropertiesPaperKind).FormulaForceU = "9" 'DinA4
AppVisio.ActiveWindow.Page.AutoSize = False     'Don't rescale the page
AppVisio.Documents.OpenEx "xfunc_m.vss", visOpenRO + visOpenDocked      'open shapes
AppVisio.Documents.OpenEx "TIMELN_M.vss", visOpenRO + visOpenDocked     'open shapes

xPosSL = 2.716535   'x-coordinate for dropping the first swimlane
yPosSL = 10.03937   'y-coordinate for dropping the first swimlane
xPosTL = 5.413386   'x-coordinate for dropping the first timeline
yPosTL = 10.03937   'y-coordinate for dropping the first timeline
dYPosTL = 1.181103    'Increment of y-positions for placing a timeline in a second swimlane given the dimensions of the swimlanes are standard

AppVisio.ActiveWindow.Page.Drop AppVisio.Documents.Item("XFUNC_M.VSS").Masters.ItemU("Swimlane"), xPosSL, yPosSL        'first swimlane
AppVisio.ActivePage.DropIntoList AppVisio.Documents.Item("XFUNC_M.VSS").Masters.ItemU("Swimlane"), AppVisio.ActivePage.Shapes.ItemFromID(7), 2 'Second swimlane connected to the first. Why ID = 7?

'AppVisio.ActiveWindow.Page.Drop AppVisio.Documents.Item("TIMELN_M.VSS").Masters.ItemU("Block timeline"), xPosTL, yPosTL      'first timeline
'AppVisio.ActiveWindow.Page.Drop AppVisio.Documents.Item("TIMELN_M.VSS").Masters.ItemU("Block timeline"), xPosTL, yPosTL - dYPosTL    'second timeline

Set theTimeline = AppVisio.ActiveWindow.Page.Drop(AppVisio.Documents.Item("TIMELN_M.VSS").Masters.ItemU("Block timeline"), xPosTL, yPosTL)      'first timeline
theTimeline.CellsU("User.visBeginDate").FourmulaU = AppVisio.ConvertResult("1/3/1983", VisUnitCodes.visDate, VisUnitCodes.visInches)
theTimeline.CellsU("User.visEndDate").FourmulaU = AppVisio.ConvertResult("2/3/1983", VisUnitCodes.visDate, VisUnitCodes.visInches)
AppVisio.Addons("ts").Run "/cmd=3"

AppVisio.ActiveWindow.Page.Drop AppVisio.Documents.Item("TIMELN_M.VSS").Masters.ItemU("Block interval"), xPosTL, yPosTL     'interval
AppVisio.ActiveWindow.Page.Drop AppVisio.Documents.Item("TIMELN_M.VSS").Masters.ItemU("Block interval"), xPosTL, yPosTL - dYPosTL   'interval on second timeline

Set AppVisio = Nothing
'AppVisio.AlertResponse = 0
End Sub
This code is very basic, there is no real interaction with excel right now. It is just to get a basic structure which then includes actual values from Excel.

The thing is if I use

Code Select Expand

AppVisio.ActiveWindow.Page.Drop AppVisio.Documents.Item("TIMELN_M.VSS").Masters.ItemU("Block timeline"), xPosTL, yPosTL      'first timeline
AppVisio.ActiveWindow.Page.Drop AppVisio.Documents.Item("TIMELN_M.VSS").Masters.ItemU("Block timeline"), xPosTL, yPosTL - dYPosTL    'second timeline I am able to get both timelines in their respective swimlanes. Somewhere I fond this code to also set the start and end dates but it's not working:

Code Select Expand

Set theTimeline = AppVisio.ActiveWindow.Page.Drop(AppVisio.Documents.Item("TIMELN_M.VSS").Masters.ItemU("Block timeline"), xPosTL, yPosTL)      'first timeline
theTimeline.CellsU("User.visBeginDate").FourmulaU = AppVisio.ConvertResult("1/3/1983", VisUnitCodes.visDate, VisUnitCodes.visInches)
theTimeline.CellsU("User.visEndDate").FourmulaU = AppVisio.ConvertResult("2/3/1983", VisUnitCodes.visDate, VisUnitCodes.visInches)
AppVisio.Addons("ts").Run "/cmd=3"
As soon as the execution reaches

Code Select Expand

Set theTimeline = AppVisio.ActiveWindow.Page.Drop(AppVisio.Documents.Item("TIMELN_M.VSS").Masters.ItemU("Block timeline"), xPosTL, yPosTL) I get the popup for the timeline properties and I get a runtime error '-2147319779 (8002801d). A library is not registered. Unfortunately there is no hint which library If I set the AlertRespone = 1 then this dialog is not shown but I still get the error. There are a lot of possible sources for this error but up to know all possible solutions did not work for me. Like re-registering .dlls, deleting specific values in the registry, activating additional references in the VBA editor... 

Up to know I get everything from the macro recorder in Visio but some actions are not recorded (like changing the timeline properties). But as you can see I work a lot with absolute values for positions and fixed shape IDs and its a lot of trial and error to drop everything at the correct position. I am not feeling well doing it this way but I am not able to find information about how to address shapes correctly. For example the first swimlane has the ID7. I guess this is the ID of the group since the swimlane consists of several shapes. Am I able to get this ID from within the code? Like "put the second swimlane underneath the recently created swimlane.ID"? Or "put the timeline inside the shape with a certain ID"? I cannot get a grip on how to address shapes without knowing their respective ID.

The goal is to get a macro that creates a Visio document with a dynamic number of swimlanes (with different heights), with one or two timelines per swimlane (depending on the height) and of course with some intervals and mile stones (with adjusted appearance).

I am grateful for an hint

Best regards,
Legion