I was recently asked if it is possible to use SharePoint calendars with Visio Timeline Shapes automatically. My first reaction was “not without code”, but a closer inspection reveals that you can use Visio 2007 Professional Link Data to Shapes quite easily….
Firstly, I found the WSS Demo site http://www.wssdemo.com/application/default.aspx to be useful. [This is no longer available]
In Visio 2007 Professional, you can use Data / Link Data to Shapes … to get data from Microsoft Windows SharePoint Services:
Then enter http://www.WSSDemo.com (or your own) as the Site.
And login of course, in this case the password is pass@word1.
Then select the Calendar list:
Now, you need to rename the Title, Start Time and End Time columns by selecting Column Settings … on the right mouse menu of the External Data window…
.. to Task Name, Start and Finish . This is important, because this will be used to match the values from the calendar to the shape properties.
Drag and drop a Timeline shape, and set the Start and Finish to your requirements….
Then, select an Interval shape in the stencil, and drag a row from the External Data window, and drop it on the page (not over the Timeline itself). The Configure Interval dialogue will popup, which you can just OK.
In addition to the three columns that we previously mapped, all of the other columns will be automatically added as extra Shape Data rows on each shape.
Of course, you can use Milestones or Intervals….
Q.E.D.
Post script:
The sample data happens to include hyperlinks as text, but you can ensure that Visio interprets them as real hyperlinks by checking the Hyperlink box on the Types and Units dialogue opened from the Data Type… button on the Column Settings dialogue.
You will then get hyperlinks on your shapes! And you can have multiple ones per shape!
Ye gads … Visio is cool!
Works fine but if you change a date in SharePoint calendar, the new date will not be displayed in visio drawing.
Perhaps you have an idea how to fix it?
Hmm, I see what you mean. Try this macro after you refresh from the wss:
Public Sub UpdateStartEnd()
Const startProp As String = “Prop.visIntervalBegin”
Const endProp As String = “Prop.visIntervalEnd”
Dim shp As Visio.Shape
Dim pag As Visio.Page
Dim iRecord As Integer
Dim dataRecordsetIDs() As Long
Dim dataRecordset As Visio.dataRecordset
Dim customPropertyIndices() As Long
Dim dataColumn As Visio.dataColumn
Dim dataRow As Long
On Error GoTo errHandler
Visio.Application.EventsEnabled = False
For Each pag In Visio.ActiveDocument.Pages
For Each shp In pag.Shapes
If shp.CellExistsU(startProp, Visio.visExistsAnywhere) _
And shp.CellExistsU(endProp, Visio.visExistsAnywhere) Then
shp.GetLinkedDataRecordsetIDs dataRecordsetIDs
For iRecord = 0 To UBound(dataRecordsetIDs)
If shp.IsCustomPropertyLinked(dataRecordsetIDs(iRecord), _
shp.Cells(startProp).Row) Then
Set dataRecordset = Visio.ActiveDocument.DataRecordsets( _
dataRecordsetIDs(iRecord))
Set dataColumn = dataRecordset.DataColumns( _
shp.GetCustomPropertyLinkedColumn(dataRecordsetIDs(iRecord), _
shp.Cells(startProp).Row))
dataRow = shp.GetLinkedDataRow(dataRecordset.id)
If dataRecordset.GetRowData(dataRow)(shp.Cells(startProp).Row) _
shp.Cells(startProp).ResultStr(“”) Then
shp.Cells(startProp).Formula = _
“=DATETIME(“”” & dataRecordset.GetRowData(dataRow)(shp.Cells(startProp).Row) & “””)”
End If
End If
Next iRecord
End If
Next shp
Next pag
errHandler:
Visio.Application.EventsEnabled = True
End Sub
David, thanks for your reply. The problem is not in Visio 2010 itself – it refreshes start and finish without macro help. The drawing within SharePoint site (.vdw) doesn’t “see” the changes to Start and Finish columns. Macro will not work in this case because as far as I know macros are not supported.
Can you advise how to achieve this on a timeline segment in a value stream map?
Thanks
That is an interesting question, but I don’t think the MS supplied shapes are particularly suited to it because the Process and Inventory shapes should be coupled with a Timeline segment below it, and then the TimeLine Total should aggregate the values in the preceding Timeline Segments.
It sounds like a little custom development would be required