CEO Exec Forum – Office 365 and Power BI

Recently I gave 2 presentations to the CEO Executive Forum at their semi-annual meeting; this time located in Detroit. The first was on Connectivity and Collaboration, the 2nd on Metrics, Reports and Dashboards. These focus on Office 365 and Power BI, including SharePoint and middleware.

Connectivity and Collaboration

For Connectivity and Collaboration, we focused on how as humans we need to make connections between things, and between things and people. I then described some ways this is possible with Office 365, SharePoint, OneNote and Teams and middleware.

We worked on answering questions like:

Can we automate our business processes in Office 365?
What is the best way to manage documents?
How do I reduce data entry?

Metrics, Reports and Dashboards

During Metrics, Reports and Dashboards I took time to focus on the difference types of numbers to pay attention to, compared metrics to KPI’s and talked about what a dashboard should contain. There was some demonstrations including Power BI.

We worked on these questions:

What business metrics do I care about?
What numbers should I pay attention to?

I want to make the PowerPoint presentations available to the attendees of the conference and anyone else that finds the topics interesting.

Below are the 2 PowerPoint presentations you can download:

Connectivity and Collaboration

Metrics, Reports and Dashboards

Feel free to email or comment with questions or feedback.

SharePoint Adoption Kit – Show Me How Videos

Here is the collection of Show Me How videos from Microsoft for SharePoint 2010. These videos are part of the Microsoft SharePoint Adoption Kit and include some great examples. My favorites are video 1 – phonetic search, video 2 – co-authoring Word documents, video 6 – the Group Work Site and video 9 – workflow and document approval.

Sharepoint Adoption Kit Continue reading “SharePoint Adoption Kit – Show Me How Videos”

The Perfect SharePoint Document Template

SharePoint is a good Document Management System until you need to embed properties within a document. Once properties are embedded they won’t update until you print, you manually update the fields or you add a macro to update them every time the document is opened.

SharePoint as a Document Management System also falls down when you want to update the template used by documents and have that update affect all existing documents.

The Perfect Document Template

The perfect template will have a macro that updates embedded SharePoint properties whenever a document is opened. And the template will allow me to change the header and footer for all the documents whenever I want. To do this, I need a template that I can swap out whenever I want.

Because of the default behaviour of SharePoint and Word, the template is downloaded once when you open a document. The template is never pulled again from SharePoint. See this reference. This means any updates you make to the template do not pass down to you.

The article refered to from Microsoft above discusses building an XML Expansion Pack, which is overkill for what I need. And in fact doesn’t work with MOSS only WSS.

The simpler solution is to build 2 templates: a driver template and an actual template.

The Driver Template

This is the template that is bound to the Content Type and is the one that gets downloaded and never updated. What this template does is binds the actual template you want to associate with the actual document.

The Actual Template

This template contains code to dynamically build the header and footer and update all the embedded properties.

To Build This Solution

  1. Define your Document Library
  2. Define your Content Type
  3. Create a Driver Template in the Forms directory of your Document Library
  4. Create an Actual Template in the Forms directory as well
  5. Bind the Driver Template to the Content Type
  6. Test

I won’t bother showing you how to create a Document Library or defining a Content Type, but I will show you steps 3 and 4.

Create a Driver Template

Open MS Word.
Alt + F8 will take you to macros
Create a macro called UpdateTemplate
Once you are in Visual Basic, paste this code in the code window:

Private Sub Document_New()

‘temporarily unlink this file from the template
ActiveDocument.AttachedTemplate = “”

‘and update

End Sub

Sub UpdateTemplate()

Dim strTemplatePath As String
Dim doc As Document

‘ Get the path of the template file.
strTemplatePath = “http://sharepoint-test/Docs/MyLibrary/Forms/ActualTemplate.dotm”

‘ Open the document template and save it to the local machine
Set doc = Application.Documents.Open(strTemplatePath, Visible:=False)
doc.SaveAs Environ(“Temp”) & “ActualTemplate.dotm”

‘ Add the template as an Add-in
Application.AddIns.Add Environ(“Temp”) & “ActualTemplate.dotm”

‘ Run the macro in the ActualTemplate template
Application.Run “UpdateThisDocument”

‘ Rest of the lines do not execute.
Application.AddIns.Unload True
On Error Resume Next ‘In case another document is using the template
Kill Environ(“Temp”) & “ActualTemplate.dotm”

ActiveDocument.AttachedTemplate = strTemplatePath

End Sub

Private Sub Document_Open()

‘do not do anything in the case where the solution author opens the template directly
If (InStr(1, ActiveDocument.FullName, “.dotm”, vbTextCompare) = 0) Then

‘temporarily unlink this file from the template
ActiveDocument.AttachedTemplate = “”

‘update template

End If

End Sub

Close the VB environment and save your template.

NOTE: Make sure to save it as a .dotm (macro enabled template)

Create an Actual Template

Open MS Word.
Alt + F8 will take you to macros
Create a macro called UpdateThisDocument
Once you are in Visual Basic, paste this code in the code window:

Sub Document_New()

End Sub

Sub Document_Open()


End Sub

Public Sub UpdateThisDocument()

Application.ScreenUpdating = False




‘Update Fields

‘Display Document Normally
If ActiveDocument.ActiveWindow.View.SplitSpecial = wdPaneNone Then
ActiveDocument.ActiveWindow.ActivePane.View.Type = wdPrintView
ActiveDocument.ActiveWindow.ActivePane.View.SeekView = wdSeekMainDocument
ActiveDocument.ActiveWindow.ActivePane.View.Type = wdPrintView
ActiveDocument.ActiveWindow.View.Type = wdPrintView
ActiveDocument.ActiveWindow.View.SeekView = wdSeekMainDocument
ActiveDocument.ActiveWindow.View.Type = wdPrintView
End If

End Sub

Private Sub Margins()

With ActiveDocument.Styles(wdStyleNormal).Font
If .NameFarEast = .NameAscii Then
.NameAscii = “”
End If
.NameFarEast = “”
End With
With ActiveDocument.PageSetup
.LineNumbering.Active = False
.TopMargin = InchesToPoints(0.5)
.BottomMargin = InchesToPoints(0.7)
.LeftMargin = InchesToPoints(0.5)
.RightMargin = InchesToPoints(0.5)
.Gutter = InchesToPoints(0)
.HeaderDistance = InchesToPoints(0.4)
.FooterDistance = InchesToPoints(0.5)
.PageWidth = InchesToPoints(8.5)
.PageHeight = InchesToPoints(11)
.SectionStart = wdSectionNewPage
.OddAndEvenPagesHeaderFooter = False
.DifferentFirstPageHeaderFooter = False
.VerticalAlignment = wdAlignVerticalTop
.SuppressEndnotes = False
.MirrorMargins = False
.TwoPagesOnOne = False
.BookFoldPrinting = False
.BookFoldRevPrinting = False
.BookFoldPrintingSheets = 1
.GutterPos = wdGutterPosLeft
End With

End Sub

Private Sub ClearHeaders()
Dim hdr As HeaderFooter

For Each hdr In ActiveDocument.Sections(1).Headers
hdr.Range.Text = vbNullString
Next hdr

End Sub

Private Sub AddHeader()

‘TODO: Add your header code here

End Sub

Private Sub ClearFooters()
Dim hdr As HeaderFooter

For Each hdr In ActiveDocument.Sections(1).Footers
hdr.Range.Text = vbNullString
Next hdr

End Sub

Private Sub AddFooter()

‘TODO: Add your footer code here

End Sub

Private Sub UpdateFields()

With Options
.UpdateFieldsAtPrint = True
.UpdateLinksAtPrint = True
End With

If ActiveDocument.ActiveWindow.View.SplitSpecial <> wdPaneNone Then
End If

ActiveDocument.ActiveWindow.View = wdNormalView

‘Application.ScreenUpdating = False

If ActiveDocument.ActiveWindow.ActivePane.View.Type = wdNormalView Or ActiveDocument.ActiveWindow.ActivePane.View.Type = wdOutlineView Then
ActiveDocument.ActiveWindow.ActivePane.View.Type = wdPrintView
End If

ActiveDocument.ActiveWindow.ActivePane.View.SeekView = wdSeekPrimaryFooter

‘Main Document
ActiveDocument.ActiveWindow.ActivePane.View.SeekView = wdSeekMainDocument

ActiveDocument.ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageHeader

End Sub

Close the VB environment and again save your template as a .dotm file.

Bind the Driver Template to the Content Type

Go to your Document Library
Settings > Document Library Settings
Under Content Types, click on your Content Type
Click Advanced Settings
Enter the URL for the DriverTemplate.dotm (http://sharepoint-test/Docs/MyLibrary/Forms/DriverTemplate.dotm)
Click OK


In the Document Library, click New and select your Content Type.
At this point your macro code in ActualTemplate.dotm should have fired. Any header or footer creation should have occurred.