Champ mémo et Export Excel, la galère !?

Si vous avez un jour tenté de lier un source de données MS ACCESS, requête ou table, à Excel, vous avez sûrement été confronté à l’interprétation aléatoire des champs mémo.

Aléatoire ? Pas tant que ça. Il faut savoir que pour réaliser un export à partir du menu DONNEES EXTERNES / Exporter Excel, MS ACCESS ne se base non pas sur la structure du champ mais sur le contenu des premières lignes. Si dans ces premières lignes il rencontre un contenu de plus de 255 caractères il considère ce champ comme mémo (texte long)… et là tout va bien.

Dans le cas contraire, il tronquera tout le reste à 255 caractères qui est la limite du format Texte Court, anciennement Texte. Adieu vos 32 000 caractère suivant !

Info : Un champ Texte Long, anciennement  Mémo, de la base de données 
Jet (Ms ACCESS) et d'une capacité de 65 535 caractères exactement. 
Une cellule MS EXCEL en contient tout au plus 32 767. 
Il en manquera toujours un peu.

On peut donc oublier l’export CSV ou depuis la commande MS ACCESS, car à part faire un tri sur le nombre de caractère de votre champ mémo, si techniquement cela ne pose aucun problème fonctionnellement cela peut ne pas convenir, on ne sera jamais sûr d’avoir la totalité du contenu.

Heureusement, il existe un méthode, certes un peu plus complexe mais également plus efficace. Il s’agit de l’automation. Autrement dit, piloter Excel depuis ACCESS et plus particulièrement une méthode de copie de recordset.

CopyFromRecordset

Cette méthode est disponible dans Excel sans ajout de bibliothèque (Références) car il faut le savoir, EXCEL est un consommateur de Recordset à ces heures.

Voici le code commenté :

Function fInsertInSheet(ByVal strPath As String, 
                        ByVal strFeuille As String, 
                        rst As Recordset2) As Boolean
      '-----------------------------------------------------------------
      ' Procedure : fInsertInSheet
      ' Author    : Fabrice CONSTANS (MVP)
      ' Date      : 21/01/2014
      ' Purpose   : Insère un ou plusieurs enregistrements issu de RST
      '             dans la feuille excel strFeuille du fichier strPath
      '             utilise fFieldFormated() et ADODB
      ' Parameters: strPath = chemin+nom du fichier Xls au format 12.0
      '             strFeuille = la feuille dans laquelle insérer l'enrg
      '             rst = le recordset contenant les données à insérer
      ' Return    : Boolean renvoi vrai si insertion réussie
      '-----------------------------------------------------------------
      '
      'Ecrit le recordset transmis dans la feuille indiquée
          Dim strSql As String
          Dim i As Long
          Dim l As Long
          ' Late Binding (cf mon article)
          Dim oExcel As Object         ' Excel application
          Dim oFeuille As Object       ' la feuille
          Dim oWork As Object          ' le workbook
          Dim boolStateDisplayAlerts As Boolean
          Dim boolStateAskToUpdateLinks As Boolean
10        On Error GoTo Errsub
          
20        Set oExcel = CreateObject("Excel.Application")
30        oExcel.Visible = False
          'enregistre l'état
40        boolStateDisplayAlerts = oExcel.DisplayAlerts
50        boolStateAskToUpdateLinks = oExcel.AskToUpdateLinks
          'met en mode silentieux
60        oExcel.AskToUpdateLinks = False
70        oExcel.DisplayAlerts = False
          
80        Set oWork = oExcel.Workbooks.Open(strPath) ' ouvre le classeur
          
90        Set oFeuille = oWork.Sheets(strFeuille)    ' active la feuille
          
          'xlByRows, xlPrevious
100       l = 1                     'insertion en ligne 1
110       oFeuille.Cells(l, 1).CopyFromRecordset rst 'copie recordset
          
120       oExcel.Windows(1).Visible = True      
130       oWork.Save                            'on le sauve
          'remet à l'état d'origine
140       oExcel.DisplayAlerts = boolStateDisplayAlerts
150       oExcel.AskToUpdateLinks = boolStateAskToUpdateLinks
          
160       oExcel.Visible = True
170       oExcel.Quit
          
180       Set oFeuille = Nothing ' ferme les objet xls
190       Set oWork = Nothing
200       Set oExcel = Nothing
210       fInsertInSheet = True  'ça c'est bien passé !
Exitsub:
220      On Error GoTo 0
230      Exit Function
              
Errsub:
240       fInsertInSheet = False  'il y a un problème

250       'ici une gestion d'erreur ou un msg

End Function

Donc voilà un code pas si mystérieux. On ouvre un recrodset coté ACCESS et on le copie coté EXCEL.

Vous pouvez utiliser ce code dans le cadre personnel ou professionnel. Toute vente directe ou indirecte est bien entendu prohibée sans mon accord.

4 réflexions au sujet de « Champ mémo et Export Excel, la galère !? »

  1. Bonjour,

    Je ne suis pas très connaissante dans le langage VB, mais j’ai toujours su me débrouiller avec les suggestions comme la votre. C’est extraordinaire que des personnes comme vous partagent leurs connaissances sur le Net. Je vous en remercie.

    Dans ce cas bien précis, j’essaie d’exporter un rapport Access (tabulaire) dans Excel. Ce dernier, bien évidemment, n’importe que les 255 premiers caractères des champs . »Mémo ». J’aimerais utiliser votre code pour exporter mes données dans un NOUVEAU fichier Excel. Est-ce que je peux l’adapter facilement pour ne pas avoir à indiquer les noms d’un classeur et d’une feuille ?

    Merci beaucoup
    Martine

  2. Bonjour et merci,
    Il est obligatoire d’indiquer un nom de fichier et son chemin ainsi que le nom de la feuille, sinon Excel ne saura ni quel fichier est concerné, ni quelle feuille du classeur.
    Par contre on peut remplacer le nom de la feuille par son N° d’index. 1, 2…
    Dans ce cas la syntaxe pour ma procédure est :
    oWork.Sheets(1)

    Si vous souhaitez créer le fichier Excel utilisez cette syntaxe :
    oWork.Add

    Si vous souhaitez créer une feuille :
    oWork.Sheets.Add type:=xlSheet, count:=1, after:=Sheets(1)

    Voilà pour les pistes à explorer.
    N’hésitez pas à vous rapprocher du modèle Excel sur le MSDN :
    https://msdn.microsoft.com/fr-fr/library/office/ff841074.aspx

    Bonne continuation,

  3. Bonjour Fabrice,

    Merci pour cette solution qui pourrais grandement me dépanner.
    En effet je suis bloqué au moment de lancer la fonction avec les arguments depuis une macro : ExecuterCode / Fonction , je reçois un message d’erreur « Incompatibilité de Type »
    En regardant sur le net je vois que des gens s’en sont sortis en mettant DAO.recordset2 dans la déclaration de rst mais pour moi va ne fonctionne pas.

    Peut -être aurais tu une idée ?

    Merci

  4. Bonjour et désolé pour ce retard,

    Il faut mettre un point d’arrêt en début de fonction pour connaitre la ligne qui pose problème.
    Une fois la ligne identifier et le programme stoppé sur la ligne, vérifier chaque paramètre passé pour savoir si son type correspond à celui attendu par l’instruction.
    Cordialement,

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *