Il y quelques temps déjà, je vous indiquais qu’on avait, avec Excel 2013, accès au DataModel par programmation. Etrangement, personne n’en a parlé dans la communauté, française ou internationale.

La documentation du modèle objet d’Excel contient bien les entrées avec un objet Model.

http://msdn.microsoft.com/en-us/library/office/jj230227.aspx

Je me suis donc mis en tête de creuser ce sujet…

Disclaimer

Malgré ma ténacité (et une bonne partie de mon dimanche), je ne suis pas super satisfait de mes trouvailles car il y a encore beaucoup à creuser dans l’API pour avoir une solution totalement fonctionnelle. Et l’API n’est pas beaucoup documentée.

Cet article vous donne les pointeurs pour démarrer et évidemment, si par la suite je trouve plus de choses, je vous en ferai part. Je compte sur vous pour en faire de même dans les commentaires de cet article.

 

Mode DataModel Excel

La manipulation du DataModel (Model) directement dans le modèle objet Excel (en VBA) est en ReadOnly uniquement. Elle est même carrément absente des API en VSTO.

Il y a toutefois une méthode intéressante :

Workbook.Connections.Add2

Elle permet d’ajouter une connexion au DataModel grâce à un paramètre booléen qui pourrait passer inaperçu dans la doc : CreateModelConnection.

http://msdn.microsoft.com/en-us/library/office/ff195949.aspx

Ce code ajoute la table Faits provenant de mon SQL Server local dans le modèle :

ThisWorkbook.Connections.Add2 _
	"VBA Connection", "A Description", _
	"OLEDB;Provider=SQLNCLI11;Data Source=localhost;initial catalog=Lab;integrated security=SSPI", _
	"Faits", 6, _
	True, True

Je n’ai pas poussé à récupérer un modèle complet mais il suffit d’indiquer plusieurs tables dans la CommandText.

Il y a aussi tout un tas de spécificités en fonction de votre source. Avec une source OData, ça donne ça comme ConnectionString (source Azure DataMarket)

"OLEDB;Data Source="Inline Service Document";Include Atom Elements=Auto;Include Expanded Entities=False;Persist Security Info=false;Time Out=600;Schema Sample Size=25;Retry Count=5;Retry Sleep=100;Keep Alive=False;Refresh Token=xxxxx

Mode Table Liée dans Excel

Dans la lignée de la première méthode, il est tout à fait possible par code de remplir une feuille Excel avec toutes les données (limité à 1M de lignes).

Ensuite, un appel à la méthode Add2 avec d’autres paramètres que ceux présentés ci-dessus permet de l’ajouter au Model.

Par contre, dans ce cas, le Data Refresh ne fonctionnera pas.

ThisWorkbook.Connections.Add2 _
	"LinkedTable_TableClasseur", "", _
	"WORKSHEET;C:\Users\Jean-Pierre\Desktop\Book1.xlsm", _
	"Book1.xlsm!TableClasseur",	7, _
	True, False

Mode OpenXML

Il y a également une autre piste, c’est de passer par le SDK OpenXML qui permet de manipuler le fichier Excel sans ouvrir Excel.

Télécharger le SDK OpenXML 2.5

Le contenu du fichier Excel est un ZIP qui contient des fichiers XML et d’autres données (images, etc.).

Dans le fichier workbook.xml, il y a un nœud <dataModel>
documentation MSDN

Pour le modèle en lui-même, c’est en fait un backup d’un cube SSAS Tabulaire.
Vous pouvez en construire un avec l’API AMO, le sauvegarder et le ré-injecter dans le fichier Excel.

Un projet CodePlex contient toutes les routines nécessaires : http://tabularamo2012.codeplex.com/

Notez que je n’ai pas investigué cette méthode donc impossible de vous dire si cela fonctionne bien.

 

Conclusion

L’API n’est pas documentée, il n’y a aucun exemple sur Internet mais les méthodes sont bien présentes. Je n’arrive pas à la conclusion que c’est impossible mais évidemment, il y a encore du travail pour suivre ces pointeurs et avoir un PoC totalement fonctionnel.

Attention : Point important

De ce que j’ai pu trouver, toutes ces méthodes ne sont pas forcément supportées. L’impact est que, si une macro ou le code qui manipule le OpenXML endommage le classeur Excel, le support Microsoft se désengagera.

 

PS : quand je disais que la communauté n’avait rien écrit sur ces API, j’aimerai toutefois remonter 2 pointeurs :

• Marco Russo qui a fait un article sur l’automatisation du Data Refresh en 2011
• Goban Saor qui donne un code complet (et complexe)

Notez que ces articles sont vieux et donc ne tiennent pas compte de l’extension de l’API en 2013.