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 :
- 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 deNumé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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
' --- ' IMPORTATION DE PLUSIEURS FEUILLES EXCEL ' --- Sub ImportExcel( _ ByVal strChemin As String, _ ByVal varFeuilles As Variant, _ ByVal blnNoms As Boolean, _ ByVal strTable As String _ ) ' Déclaration des variables Dim strFeuille As Variant ' Est-ce que le classeur Excel existe ? If Dir(strChemin) = "" Then MsgBox "Le classeur ['" & strChemin & "] est introuvable.", vbExclamation Exit Sub End If ' Vidage de la table si nécessaire On Error GoTo ImportExcelErr If MsgBox("Souhaitez-vous vider la table [" & strTable & "] avant l'importation ?", _ vbQuestion + vbYesNo) = vbYes Then CurrentDb.Execute "DELETE * FROM [" & strTable & "];" End If ' Procédure d'importation For Each strFeuille In varFeuilles DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, _ strTable, strChemin, blnNoms, strFeuille & "!" Next ' Un p'tit message pour terminer :-) MsgBox "Opération terminée !", vbInformation Exit Sub ImportExcelErr: MsgBox "Erreur d'importation : " & Err.Description, vbExclamation Exit Sub End Sub |
La procédure ci-dessus doit recevoir 4 paramètres pour fonctionner :
- Le chemin complet du classeur Excel à importer.
- La liste des feuilles à importer (au cas où certaines feuilles devraient être ignorées).
- Le nom de la table Access de destination.
- 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 :
1 2 3 4 5 6 7 8 9 10 11 |
Sub TestImportExcel() Dim varFeuilles As Variant ' Liste des feuilles Excel à importer varFeuilles = Array("Acteurs1", "Acteurs2", _ "Acteurs3", "Acteurs4", "Acteurs5") ' Importation proprement dite ImportExcel "C:UsersHervéDocumentsActeurs.xlsx", _ varFeuilles, True, "tbl Acteurs - Import" End Sub |
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é !
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
Il peut y avoir plusieurs raisons pour lesquelles ça ne marche pas. 😉
A quel endroit l’erreur se produit-elle ?
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 :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 ????
Julie > L’article est en ligne à cette adresse.
Julie > Je vois le scénario. 🙂 Le temps d’écrire un autre article, la réponse sera en ligne demain. Stay tuned!
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
newbie > Reposte effectivement plutôt sur le forum. Ce sera plus clair, et ça s’éloigne de l’article du blog. 😉
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
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é).
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.
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 ?
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.
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 ?
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!
epaule_tatoo > L’article sur l’importation de plusieurs classeurs Excel est en ligne ici.
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…
epaule_tatoo > Je prépare un petit article sur le sujet, je reviendrai poster ici quand il sera prêt. Stay tuned!
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.
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