Comment utiliser Automation avec Excel et VB.NET ?

Préparatifs

Pour pouvoir piloter Excel, vous devez ajouter à votre projet une référence à Microsoft Excel Object Library (dans les composants COM) ou référencer directement les Primary Interop Assemblies (PIA) : Microsoft.Office.Interop.Excel.dll, Microsoft.VBE.Interop.dll, et Office.dll

Dans cet exemple j’utiliserai la seconde solution.

A propos des Primary Interop Assemblies

Bien que Microsoft recommande d’utiliser une version des PIA correspondant à la version d’Office installée conjointement avec votre application, j’ai constaté qu’il existe une bonne compatibilité ascendante entre ces assemblies. J’ai donc pris pour habitude d’utiliser une vieille version de ces PIA, celles de Office XP. Elles ont l’avantage de permettre à mes applicatifs de fonctionner sur toutes les versions d’Office de XP à 2007.

Vous pouvez télécharger ces assemblies sur le site de Microsoft :
http://www.microsoft.com/downloads/details.aspx?FamilyId=C41BD61E-3060-4F71-A6B4-01FEBA508E52&displaylang=en

Si vous préférez utiliser les PIA installées avec Office sur votre poste, souvenez vous simplement qu’elles ne fonctionneront pas si vos utilisateurs utilisent une version plus ancienne.

Les bases de l’automation Excel

Création et assignation de l’objet Excel

Dim XlApp As Microsoft.Office.Interop.Excel.Application = New Microsoft.Office.Interop.Excel.Application()

Création d’un classeur

Dim XLDestBook As Microsoft.Office.Interop.Excel.Workbook
XLDestBook = XlApp.Workbooks.Add

Ouverture d’un classeur existant

XLDestBook = XlApp.Workbooks.Open("c:\monfichier.xls")

Ajouter une feuille

Dim xlSheet As Microsoft.Office.Interop.Excel.Worksheet
xlSheet = XLDestBook.Worksheets.Add

Ajouter du texte dans une cellule

xlSheet.Cells(1, 2).value = "Test écriture dans cellule B1"

Ajouter une formule dans une cellule

xlSheet.Cells(1, 3).formula = "=1+5"

Les formules doivent être écrites dans la langue d’Excel. Si vos utilisateurs sont susceptibles d’utiliser des versions d’Excel dans différentes langues vous devrez donc traduire vos formules. Pour cela vous pouvez utiliser cette référence http://cherbe.free.fr/fonctions_XL.ZIP

Recommandations

Prenez l’habitude avant tout traitement d’automation de désactiver le rafraichissement d’écran d’Excel, et d’empêcher les saisies utilisateur. Les performances en seront largement améliorées, et cela évitera que vos utilisateurs ne viennent perturber le traitement.

Pour cela utilisez les lignes suivantes avant tout traitement :

XlApp.ScreenUpdating = False
XlApp.Interactive = False

Repassez ces paramètres à True lorsque vos traitements sont achevés, sinon Excel sera inutilisable.

Pour plus d’infos vous pouvez vous référer à la base de connaissance Microsoft : http://support.microsoft.com/kb/301982/fr


Attention à la langue

Si vous avez des utilisateurs utilisant une version anglaise de Windows, il y a de fortes chances pour que votre application plante avec un message : Error: 0x80028018 (-2147647512) - Description: Old Format or Invalid Type Library

Ce cas de figure se produit lorsque l’utilisateur a personnalisé les paramètres régionaux de Windows. C’est un cas que j’ai régulièrement rencontré avec les expatriés. Ils sélectionnent dans les paramètres régionaux leur pays de résidence, et modifient le séparateur décimal et l’unité monétaire pour les faire correspondre à ceux de leur pays d’origine.

La base de connaissance Microsoft vous donnera plus de détails sur ce problème : http://support.microsoft.com/?scid=kb%3Ben-us%3B320369&x=19&y=18

Pour le contourner, 2 solutions :
• Demander à vos utilisateurs de ne pas personnaliser les paramètres régionaux. Pas toujours possible.
• Avant toute opération d’automation Excel modifier la culture du système en « en-US », et surtout restaurer les paramètres utilisateurs lorsque vos traitements sont terminés.

La base de connaissance peut une nouvelle fois vous aider : http://support.microsoft.com/kb/914356/fr

Personnellement j’utilise le code suivant (dérivé des exemples MS) :

'Sauvegarde le réglage de culture
Dim oldCI As System.Globalization.CultureInfo = System.Threading.Thread.CurrentThread.CurrentCulture

''' 

''' Modifie la culture en "en-US".
''' 
''' 
Sub ChangeCulture()
Dim LangueExcel As Integer 'code correspondant à la langue Excel
Dim RegionalString As String 'Equivalence entre la langue Excel et la culture système

LangueExcel = XlApp.LanguageSettings.LanguageID(Microsoft.Office.Core.MsoAppLanguageID.msoLanguageIDInstall) 'Récupére le code correspondant à la langue d'Excel
Select Case LangueExcel
Case 1033 : RegionalString = "en-US" 'Anglais USA
Case 1036 : RegionalString = "fr-FR" 'Français FRANCE
Case Else : RegionalString = "en-US" 'Anglais USA
End Select
System.Threading.Thread.CurrentThread.CurrentCulture = New System.Globalization.CultureInfo(RegionalString)
End Sub


''' 

''' Rétablit la culture d'origine du système
''' 
''' 
Sub RestoreCulture()
System.Threading.Thread.CurrentThread.CurrentCulture = oldCI
End Sub