Le Blog (Vert) d'Arnaud JUND

Excel QuickPart – SharePoint

Quiconque a déjà utilisé SharePoint 2007/2010 connait certainement l'insertion de contenu automatique dans les documents Word (2007/2010) via l'utilisation des QuickParts.

Dernièrement une amie me posait la question de l'utilisation des QuickParts dans une feuille Excel, et je lui répondait que malheureusement les QuickParts ne sont pas disponibles sous Excel. Et qu'en effet cela était bien dommage.

Après quelques recherches, il existe un moyen d'arriver à utiliser cette technique d'insertion automatique de contenu dans les feuilles Excel.

En voici un exemple qui devrait vous intéresser.

Objectif : Publier des documents Excel dans une librairie SharePoint. Dans chaque devis on trouvera une cellule représentant le budget total du devis qui devra être reporté automatiquement dans une meta donnée de la librairie SharePoint.

Contexte :

• SharePoint 2010 Foundation
• Excel 2010

1ère étape : Création d'une librairie de document pour stocker les documents Excel

Nom de la liste : Devis
Modèle de document : Feuille de calcul Microsoft Excel

2ème étape : Création d'une colonne (méta donnée) budget qui sera un champ "nombre" avec deux décimales

A ce stade-ci, une petite explication de la suite s'impose.

Dans un document traditionnel de type Excel vous avez globalement deux types de propriétés, les "DocumentProperties" (Title, Size, Author, etc …) et les "Custom DocumentProperties" qui sont les propriétés que vous pouvez vous-même ajouter.

Dans la section Info de l'onglet "File", vous pouvez accéder aux "Advanced Properties"

Ensuite aux "Custom Properties"

Si nous créons une Custom Property nommée de la même manière que notre colonne SharePoint, au moment de l'ajout d'un document dans la librairie, SharePoint va utiliser cette valeur pour l'associer à notre colonne SharePoint.

3ème étape : Adaptation de ma feuille Excel pour récupérer la valeur de mon budget lors du transfert de mon document Excel dans la liste SharePoint.

Pour créer cette propriété Custom, je vais utiliser du code VBA, donc une macro. Il faudra donc aussi sauvegarder ce document Excel avec l'extension "xlsm"

Voici à quoi ressemble ma feuille de devis

Le montant de mon budget est en cellule B5 sur la feuille "Feuille1"

J'active l'environnement de Macro via "Alt-F11"

Dans le code associé au Workbook je vais ajouter quelques méthodes qui vont vérifier si la propriété custom est présente ou non, la créer, et la mettre à jour avec la valeur souhaitée.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

      SetCustomProperty "Budget", Application.ActiveWorkbook.Sheets("Feuille1").Range("B5")

End Sub

Private Sub SetCustomProperty(name As String, value As Variant)

  If CheckCustomPropertyType(name) = CheckType(value) Then

    Application.ActiveWorkbook.CustomDocumentProperties(name).Value = value

  Else

    DeleteCustomProperty name

    Application.ActiveWorkbook.CustomDocumentProperties.Add _

    Name:=name, _

    LinkToContent:=False, _

    Type:=CheckType(value), _

    Value:=value, _

    LinkSource:=False

  End If

End Sub

Private Function CheckCustomProperty(name As String)

  Dim objDocProp As DocumentProperty

  CheckCustomProperty= False

  For Each objDocProp In Application.ActiveWorkbook.CustomDocumentProperties

    If name = objDocProp.Name Then

      CheckCustomProperty= True

      Exit Function

    End If

  Next

End Function

Private Function CheckCustomPropertyType(name As String)

  If CheckCustomProperty(name) Then

    CheckCustomPropertyType= Application.ActiveWorkbook.CustomDocumentProperties(name).Type

  Else

    CheckCustomPropertyType= -1

  End If

End Function

Private Sub DeleteCustomProperty(name As String)

  If CheckCustomProperty(name) Then

    Application.ActiveWorkbook.CustomDocumentProperties(name).Delete

  End If

End Sub

Private Function CheckType(pVar_Val)

Dim lVar_X As Variant

''Extract from the Excel VBA Help file

''vbEmpty 0 Empty (uninitialized)

''vbNull 1 Null (no valid data)

''vbInteger 2 Integer

''vbLong 3 Long integer

''vbSingle 4 Single-precision floating-point number

''vbDouble 5 Double-precision floating-point number

''vbCurrency 6 Currency value

''vbDate 7 Date value

''vbString 8 String

''vbObject 9 Object

''vbError 10 Error value

''vbBoolean 11 Boolean value

''vbVariant 12 Variant (used only with arrays of variants)

''vbDataObject 13 A data access object

''vbDecimal 14 Decimal value

''vbByte 17 Byte value

''vbArray 8192 Array

Select Case VarType(pVar_Val)

Case 0, 1, 8, 10

lVar_X = msoPropertyTypeString

Case 2, 3

lVar_X = msoPropertyTypeNumber

Case 4, 5, 6, 14

lVar_X = msoPropertyTypeFloat

Case 7

lVar_X = msoPropertyTypeDate

Case 11

'' Boolean

lVar_X = msoPropertyTypeBoolean

Case Else ''Bucket

lVar_X = msoPropertyTypeString

End Select

CheckType = lVar_X

End Function

Une fois la feuille sauvegardée (.xlsm), si je l'ouvre à nouveau, je peux consulter ma propriété custom

Maintenant, publions ce document Excel sur notre liste SharePoint.

Lors de la publication, le formulaire d'encodage des meta données se présente comme ceci :

La colonne Budget est mise à jour via la valeur de ma custom Property.

4ème étape : Adaptation de ma feuille Excel pour que la colonne SharePoint "Budget" se mette à jour lorsque je vais éditer mon document Excel depuis SharePoint.

Remarque : Maintenant que mon document Excel est hébergé dans une librairie SharePoint, il existe dans ce document un nouvelle famille de propriétés, les ContentTypeProperties. Ce sont les propriétés issues de la libraire de document.

J'ouvre à nouveau ma feuille Excel, et je retourne dans l'environnement de développement (Alt-F11) pour y ajouter un peu de code.

Private Sub SetContentTypeProperty(name As String, value As Double)

  On error Resume Next

  Application.ActiveWorkbook.ContentTypeProperties(name).Value = value

End Sub

Je vais aussi modifier ma procédure "Workbook_BeforeSave" pour la faire correspondre à ceci

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

  SetCustomProperty "Budget", Application.ActiveWorkbook.Sheets("Feuille1").Range("B5")

  SetContentTypeProperty "Budget", Application.ActiveWorkbook.Sheets("Feuille1").Range("B5")

End Sub

Je sauvegarde ma feuille , et maintenant je peux la modifier à volonté, ma colonne SharePoint "Budget" reste synchrone avec le contenu de mon document Excel.

Voici mon document publié dans SharePoint.

Je click sur le document pour l'éditer dans Excel, j'autorise l'usage des macros au passage.

Je modifie les valeurs des cellules B2, B3 et B4 pour avoir un nouveau budget

Je sauvegarde et ferme le fichier ….

Et voici ce que je retrouve côté SharePoint.

Ma colonne Budget est à jour avec la bonne valeur calculée dans Excel.

Remarque : Cette solution ne fonctionne que dans un sens, elle est unidirectionnelle.

C'est-à-dire que si je modifie la meta donnée Budget depuis SharePoint, ma feuille Excel ne sera pas mise à jour automatiquement. Pour arriver à cette complète synchronisation, il faudra encore adapter les macros. Mais là je vous laisse implémenter la suite vous-même.

Fichier Excel avec les macros