Importer plusieurs feuilles Excel dans une table Access en VBA

Je dispose de plusieurs feuilles Excel (de même structure), et je souhaiterais les importer toutes dans une seule table Access. Comment faire ?

Le scénario

Dans un classeur Excel (Acteurs.xlsx), je dispose de 5 feuilles qui ont cette forme :

CAPTURE

  • Les feuilles ont toutes la même structure (sinon, on ne va pas s’en sortir !).
  • Les feuilles peuvent avoir ou non des titres sur leur 1ère ligne (dans mon exemple, toutes les feuilles ont des titres).
  • La structure des feuilles correspond à la structure de ma table Access : les champs sont dans le même ordre, et les types de données sont compatibles.
  • Dans mon scénario, la 1ère colonne (Numéro Acteur) est clef primaire dans la table Access. Ça veut dire qu’il faut que les valeurs de Numéro Acteur, dans Excel, soient toutes différentes. Sinon, il y a aura des erreurs – normales – à l’importation.

La table Access a donc cette forme :


Bien sûr, par Copier/Coller, je pourrais rassembler ces feuilles en 1 seule, et importer la feuille résultante dans Access. Mais ce n’est pas pratique…

  • si le nombre de feuilles est important ;
  • si l’opération doit être faite régulièrement et manuellement (voir cet autre article du blog pour l’approche manuelle) ;
  • si le nombre de lignes total dépasse le nombre de lignes d’Excel. Dans Excel 2007 et 2010, ça devrait aller, mais on est limité à 65536 dans Excel 97 à 2003.

Le code

Voici une procédure VBA à recopier intégralement dans un module standard de votre base de données.

La procédure ci-dessus doit recevoir 4 paramètres pour fonctionner :

  1. Le chemin complet du classeur Excel à importer.
  2. La liste des feuilles à importer (au cas où certaines feuilles devraient être ignorées).
  3. Le nom de la table Access de destination.
  4. Une valeur booléenne (True / False) qui indique si les feuilles Excel ont des titres en première ligne.

Tester le code

Le code ci-dessus prend en charge la procédure d’importation. Il ne reste plus qu’à le tester : écrivez cette autre procédure (pourquoi pas dans le même module), qui va appeler le code d’importation :

Bien sûr, vous adaptez les valeurs en fonction de votre configuration.
Exécutez ce deuxième bloc de code. Il vous sera demandé si vous souhaitez vider la table Access avant d’importer toutes les données.
Ensuite… vous êtes arrivé !

Vous aimerez aussi...

20 réponses

  1. Carpentier dit :

    Bonjour, je souhaite faire exactement ca, prendre tous les fihciers excel de mon répertoire et les mettre dans un access, mais je tombe toujours sur le dernier message d’erreur du programme « erreur 5  » blabla
    merci d’avance

  2. Hervé Inisan dit :

    TiKinnou > Avec la méthode TransferSpreadSheet qui est utilisée ici, il n’y a pas vraiment moyen de régler finement l’importation. Tu peux faire autrement :

    • Importer le tout dans une table temporaire, puis transférer seulement les colonnes utiles dans la « vraie » table cible à l’aide d’une requête Ajout. Dans ce type de requête, on peut préciser les champs à transférer.
    • Passer par un fichier CSV et consulter les articles consacrés à ce sujet sur le blog : Export CSV personnalisé.
  3. TiKinnou dit :

    Bonjour,

    Merci bcp pour ce tuto, il m’aide déjà beaucoup

    1 petite question :

    est il possible de ne transférer dans access qu’un type de donnée précis, par exemple ici, que les acteurs du pays France (colonne E) du fichier excel ????

  4. Hervé Inisan dit :

    Julie > L’article est en ligne à cette adresse.

  5. Hervé Inisan dit :

    Julie > Je vois le scénario. 🙂 Le temps d’écrire un autre article, la réponse sera en ligne demain. Stay tuned!

  6. Julie dit :

    Bonjour,

    Merci pour ce code qui m’a beaucoup aidé.

    Par contre, j’aimerais que mon code importe toutes les feuilles du classeur Excel sans que je rentre le nom de chaque feuille. Est ce possible?

    A cette étape du code :

    ‘ Liste des feuilles Excel à importer
    varFeuilles = Array(« Acteurs1 », « Acteurs2 », _
    « Acteurs3 », « Acteurs4 », « Acteurs5 »)

    Contexte: Mon classeur Excel est alimenté par d’autre fichier Excel. Lors de mon importation vers Access je ne connais pas son nombre de feuilles ni leurs noms.

    J’espere que j’ai ete assez claire dans mes explications et espere que vous saurez aider.

    Merci d’avance.

    Julie

  7. Hervé Inisan dit :

    newbie > Reposte effectivement plutôt sur le forum. Ce sera plus clair, et ça s’éloigne de l’article du blog. 😉

  8. newbie dit :

    J’ai essayé de poster sur le forum mais un message d’erreur apparait. Je reessaierai plus tard. En attendant voici le code. (je n’ai inséré que la partie du code qui permet de faire l’importation et de creer les feuilles)

    Private Sub Import_Click()
    On Error GoTo Err_Import_Click:

    Dim strMonFichierExcel As String
    Dim Message, Title, Default, MyValue, Extent

    Extent = Format(Date, « dd_mm_yy ») & « .xls »

    ‘ Définit le message.
    Message = « Entrez le chemin d’accès du fichier excel à importer « 
    Title = « Répertoire  » ‘ Définit le titre.
    Default = Application.CurrentProject.Path & « Toutes Revues_ » & Extent ‘enregistrement _
    par défaut du fichier
    ‘ Affiche le message, le titre et la valeur par défaut.

    strMonFichierExcel = InputBox(Message, Title, Default, 100, 100)

    If StrPtr(strMonFichierExcel) = 0 Then ‘si on clique sur le bouton annuler
    MsgBox  » Opération annulée. « 
    Exit Sub
    Else

    Set appExcel = CreateObject(« Excel.Application »)

    With appExcel

    .Workbooks.Open strMonFichierExcel

    .Run (MiseEnForme_Excel_Import)

    End With

    ‘OPERATION D’IMPORTATION. NE PAS MODIFIER

    Dim varFeuilles As Variant

    ‘ Liste des feuilles Excel à importer
    varFeuilles = Tableau

    ‘ Importation proprement dite
    ImportExcel strMonFichierExcel, _
    varFeuilles, True, « ALL Revues »

    appExcel.Visible = True

    Set appExcel = Nothing

    End If

    Exit_Import_Click:
    Exit Sub

    Err_Import_Click:
    MsgBox Err.Description
    Resume Exit_Import_Click

    End Sub

    Function MiseEnForme_Excel_Import()
    ‘Tableau contenant le nom des feuilles à créer
    Tableau = Array(« HI Cl », « HI Ag », « HI IL », « HI De », « HI Re »)

    Dim CompTableau: CompTableau = 0

    For CompTableau = 0 To UBound(Tableau)
    On Error Resume Next
    appExcel.Sheets.Add , appExcel.Sheets(appExcel.Sheets.Count)
    appExcel.Sheets(appExcel.Sheets.Count).Name = Tableau(CompTableau)
    Next

    end function

  9. Hervé Inisan dit :

    newbie > Ok. Mais si j’ai suivi, tu essaies d’importer dans Access des noms de feuilles qui n’existent pas dans le classeur Excel ? C’est ça qui génère l’erreur. Si tu as un bout de code, tu peux le poster ici, éventuellement (ou mieux : sur le forum Access, ce sera plus simple pour continuer la discussion avec du code formaté).

  10. newbie dit :

    Je ne les génère pas.
    Je fais une extraction BO sous format excel. Et je dois importer cette extraction dans ma base access. Il faut que je mette en forme des feuilles qui ont la même structure que ma table (90 colonnes).
    C’est pourquoi je crée ces feuilles.

  11. Hervé Inisan dit :

    newbie > Si la feuille n’existe pas, c’est normal que le code du dessus ne fonctionne pas. Maintenant, ta procédure me semble compliquée : pourquoi générer des données/feuilles dans Excel pour les importer ensuite ? N’est-il pas possible de les générer directement dans Access, dans ce cas ?

  12. newbie dit :

    Dsl de répondre si tard. Non ma feuille n’existe pas.
    Le code que j’ai rédigé :
    – crée les feuilles qui ont pour nom le contenu de varFeuilles
    – insère des données dans ces feuilles
    – et importe les feuilles créées.

  13. Hervé Inisan dit :

    newbie > Est-ce que la feuille existe vraiment sous ce nom dans le classeur ? Du genre : est-ce qu’il pourrait y avoir un espace à gauche ou à droite ?

  14. newbie dit :

    Bonjour,
    et merci pour ce code. J’ai cependant un souci. Une fois copié et adapté il me génère l’erreur suivante lors de l’exécution de cette partie du code:

    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, _
    strTable, strChemin, blnNoms, strFeuille & « ! »

    Erreur : « Le moteur de base de données ne peut pas trouver « HI Classic$ ». Assurez vous que le nom de paramètre ou d’alias est valide, qu’il ne comprend pas de caractère ou de ponctuation incorrect et qu’il n’est pas trop long »

    Voici la déclaration de varfeuilles.
    varFeuilles = Array(« HI Classic », « HI Aggregated », « HI ILDV validated and created », « HI Deleted », « HI Restricted »)

    Je ne comprends pas pourquoi le DoCmd.TransferSpreadsheet recherche une feuille « HI Classic$ » au lieu de « HI Classic ».

    Merci d’avance pour votre aide!

  15. Hervé Inisan dit :

    epaule_tatoo > L’article sur l’importation de plusieurs classeurs Excel est en ligne ici.

  16. epaule_tatoo dit :

    extra, j’adore ce que l’on trouve sur ce site, je m’en suis bcp inspiré, mais j’avoue que je sèche un peu, j’ai trouvé un truc avec DAO, mais hélas j’ai constamment une erreur de type ou une erreur de « pilote ISAM introuvable ».

    si à l’occasion dans le code tu peux rajouter une petite ligne pour virer les lignes vides et les lignes doublonnées…

  17. Hervé Inisan dit :

    epaule_tatoo > Je prépare un petit article sur le sujet, je reviendrai poster ici quand il sera prêt. Stay tuned!

  18. epaule_tatoo dit :

    je voudrais faire de même mais avec plusieurs onglets (portant le même nom, même structure) qui se trouvent dans plusieurs fichiers (différents noms de fichiers)
    Un onglet par fichier.

    je voudrais pouvoir importer mes données dans une seule table access.

    l’idéal serait que la manipulation recherches tous les fichiers excel d’un dossier 😉 mais un code me permettant de faire une selection multiple serait suffisant.
    j’ai déja écrit cette partie du code, mais mon importation ne fonctionne pas, chaque table ecrase la precedente…

    merci d’avance.

  19. epaule_tatoo dit :

    Du très bon travail, je cherchais justement qlq chose comme ça, par contre moi je souhaite pouvoir importer plusieurs feuilles portant le même nom contenu dans différents fichiers excel vers une seule table.

    si par hasard il existait un code ou une modification de ce code qui collerait avec mon besoin je serai preneur.

    merci
    cedric

Laisser un commentaire

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