Importer 100 fichiers excel dans 1 table Access

[jbm]

Hello à tous,

j'ai besoin d'aide pour le boulot....

j'ai des rapports de vente pour chaque mois et chaque région: 12 régions, et 9 mois pour l'instant soit une bonne centaine de fichiers, les fichiers sont tous construits exactement de la meme facon (13 colonnes), seul le nombre de lignes varie (entre 200 et 500 par fichier).

je voudrais créer une seule grosse base access pour pouvoir analyser mes ventes (mix, saisonnalité...)

il y a surement une maccro simple à faire pour fusionner ces 100 fichiers dans une meme table access... le probleme... je n'y connais rien en maccro...

Voilaaa, si quelqu'un a un peu de temps pour moi....

Un grand Merci

JB

 

[nat54]

Re : Importer 100 fichiers excel dans 1 table Access

Bonjour,

Ouh la ca me parait complexe...

Il y a un seul onglet par fichier ?

Si oui le plus "simple" :
-créer un seul fichier excel avec une macro automatique
- créer la table dans access, macro automatique (déjà présente dans Access via le module macro) : importer fichier excel

 

[nat54]

Re : Importer 100 fichiers excel dans 1 table Access

Pour créer le 1er fichier

A ADAPTER

§ Macro 6 : consolider différents fichiers


Sub Consolider()

Application.ScreenUpdating = False 'ne pas voir ce qui se passe à l'écran
Application.DisplayAlerts = False


For lgn = 2 To 65 'pour boucler sur les lignes 2 à 65

Application.StatusBar = "Nombre de fichiers traités " & lgn

Sheets("Ref").Select
regate = Cells(lgn, 1).Value 'on variabilise, regate = cellule ligne de la boucle, colonne 1

Application.AskToUpdateLinks = False 'pour éviter qu’Excel demande à chaque ouverture de fichier si on veut la MAJ des liaisons
Workbooks.Open ("U:\PUBLIC\DOTC\DFI\CGC\Nathalie\Trajectoire\" & regate & ".xls")
‘on ouvre le fichier où se trouve les données du premier CC
Sheets("données").Select
Range("A2:Q35").Select
Selection.Copy
Workbooks("" & regate & ".xls").Close 'fermer le fichier

Windows("Consolidation.xls").Activate ‘on se place sur le fichier consolidation, qui est déjà ouvert d’où windows
Sheets("Base").Select

If [a2] = "" Then Range("a1").Activate Else Selection.End(xlDown).Select
ActiveCell.Activate
ActiveCell.Offset(1, 0).Activate 'pour se placer sur la prochaine ligne vierge

ActiveSheet.Paste
Range("a1").Select

Workbooks(regate & ".xls").Activate
Workbooks(regate & ".xls").Close 'fermer le fichier

Next ‘on boucle

End Sub

 

[jbm]

Re : Importer 100 fichiers excel dans 1 table Access

Yes, il n'y a qu'un seul onglet par fichier.

Quelle est la maccro à faire pour fusionner tous les .xls en 1 seul?
Le seul risque est de dépasser le nbre de ligne maxi d'un onglet, mais bon ca devrait passer.

jb

 

[nat54]

Re : Importer 100 fichiers excel dans 1 table Access

Tu as dû oublier de rafraichir la page.
J'en ai mise une au-dessus mais c'est à adapter.

 

[jbm]

Re : Importer 100 fichiers excel dans 1 table Access

En effet,
Merci pour ton aide
Reste à savoir ce que je dois faire de ton charabia...
j'avais prevenu, je suis un gros debutant de la macro (la preuve, je sais meme pas l'ecrire..)

D'après ce que j'ai compris, ta macro il faut que je la réécrive 100 fois en mettant à jour le chemin d'acces au fichier? c'est ca?

jb

 

[nat54]

Re : Importer 100 fichiers excel dans 1 table Access

Non non, heureusement !!

Une question avant d'approfondir :
as-tu un répertoire où tu as tous tes fichiers ?!
ou sont-ils "dispersés" ? si oui, comment ?



Si je prends mon exemple, les fichiers étaient tous sous
U:\PUBLIC\DOTC\DFI\CGC\Nathalie\Trajectoire\

seul le nom du fichier changeait en fonction du regate (= identifiant d'une société)
et ce nom était recherché via une liste de noms, dans l'onglet 'ref'

 

[jbm]

Re : Importer 100 fichiers excel dans 1 table Access

désolé pour la question bete...

OUI un repertoire unique (P:\departement\Savthale\OPP\recap extracts\recap 2006\Districts) et le nom des fichiers est construit comme suit:
District_06_reports_September_2006.xls
District_06_reports_August_2006.xls
...
et ainsi de suite pour les 12 districts et les 9 mois = 108 fichiers.

Donc j'ouvre un nouveau fichier excel, je clique sur outil/macro/visual basic editor, je fais un copier/coller de ta macro, je supprime tes commentaires en vert, je change le "U:\..."... et ca roule? j'ai bon?

Reste le probleme du nombre de ligne, dois je prendre le max et supprimer ensuite les lignes vides?


JB

 

[nat54]

Re : Importer 100 fichiers excel dans 1 table Access

Ce sera un peu plus compliqué que ca...

1/ Tu peux laisser les commentaires, ca ne gêne pas puisqu'il y a le ' qui dit que c'est un commentaire et non du code

2/Oui il faut changer le répertoire

3/ A mon avis va falloir faire en étape vu que les fichiers change de début de nom (de district) à ce que j'ai compris

4/ Pour le nb de lignes
il faut changer le Range("A2:Q35")
en Range(Selection, Selection.End(xlDown)).Select

Se placer sur la première ligne
ca signifie qu'il prend toutes les lignes de dessous jusque la dernière s'il n'y a pas de ligne vierge

5/ toi ce n'est pas regate, il faut adapter

par contre là je n'ai plus trop de temps (c'est l'heure...)
donc je laisse la main

 

[jbm]

Re : Importer 100 fichiers excel dans 1 table Access

OK bonne soirée
et merci beaucoup

 

[MichelXld]

Re : Importer 100 fichiers excel dans 1 table Access

bonsoir

Juste pour le Fun, voici une procédure qui boucle sur tous les classeurs d'un répertoire sans les ouvrir et transfert les données de la Feuil1 vers une table Access (Table1).

L'exemple suppose que
Tous les onglets portent le même nom.
Les classeurs sont structurés comme une vraie base (pas de données éparpillées) et de structure identique à la base Access (nombre de colonne et type de données adaptés:date,texte,numerique...etc...)

la procédure nécessite d'activer la référence Microsoft ActiveX Data Objects x.x Library

Sub tranfertFeuilleClasseursFermes_VersAccess()
Dim Cn As New ADODB.Connection
Dim oProdRS As New ADODB.Recordset, oRS As ADODB.Recordset
Dim oConn As ADODB.Connection
Dim j As Integer
Dim Fichier As String, Repertoire As String
 
'------------------------------------------------------
'Connection à la Base Access
Set oConn = New ADODB.Connection
oConn.Open "Provider='Microsoft.Jet.OLEDB.4.0';" & _
"Data Source= 'C:\maBase.mdb';"

'les données seront placés dans Table1
Set oRS = New ADODB.Recordset
oRS.Open "Select * from Table1", oConn, adOpenKeyset, adLockOptimistic
'------------------------------------------------------
 
'Boucle sur les classeurs Excel du répertoire cible
Repertoire = "C:\Documents and Settings\mimi\dossier\general\excel\Nomdossier"
Fichier = Dir(Repertoire & "\*.xls")

Do While Fichier <> ""
    'Connection au classeur Excel
    Cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & Repertoire & "\" & Fichier & ";" & _
    "Extended Properties=""Excel 8.0;"""
    
    'requête pour extraire les données de la Feuil1
    oProdRS.Open "SELECT * FROM [Feuil1$]", Cn, adOpenStatic
    
    ' --- Transfert des données dans la base ---
    Do While Not (oProdRS.EOF)
        oRS.addNew
            For j = 0 To oRS.Fields.Count - 1
            oRS.Fields(j) = oProdRS.Fields(j).Value
            Next j
        oRS.Update
        oProdRS.moveNext
    Loop
    '-------------------------------------------
    
    oProdRS.Close
    'Fermeture de la connection au classeur Excel
    Cn.Close
Fichier = Dir
Loop

oRS.Close
Set oRS = Nothing
'Fermeture de la connection Access
oConn.Close
Set oConn = Nothing
End Sub

bonne soirée
MichelXld

 

[JJ1]

Re : Importer 100 fichiers excel dans 1 table Access

Bonsoir, Michel m'avait fait une macro pour fusionner dans un même repertoire tous les fichiers Excel, je la retrouve et la met sur ce sujet
je m'en suis beaucoup servi et elle fonctionne super !
a+

 

[Hervé]

Re : Importer 100 fichiers excel dans 1 table Access

bonsoir

Juste pour le Fun, qu'il ose nous dire le bougre !!!

moi j'appelle ca du super pro.

une cinquantaine de lignes et tu nous balances 100 fichiers dans une table access.

merci michel de fournir de telles exemples.

je vais finir par plus osé poster du code moi.

salut

 

[jbm]

Re : Importer 100 fichiers excel dans 1 table Access

Merci beaucoup

Le seul hic, c'est que justement le nom des onglets n'est pas toujours le meme, c'est en fait le nom du fichier... sauf qu'apparemment étant limité en nombre de caractères, ca donne:
District_06_reports_September_2006.xls --> onglet: District_06_reports_September_2

Les fichiers sont "presque" comme une vraie base, il y a juste un peu de blabla de temps en temps (par ex des sous totaux que je comptais supprimer une fois dans access) mais a part ca, 13 colonnes bien comme il faut et toujours la meme premiere ligne avec les bons entetes.

donc qq questions:
- est ce que ca peut marcher quand meme en changeant 1 ligne ou 2 de codes
- et la question qui tue... Qu'est ce que je dois faire du code? macro dans ma base access????

JB

 

[MichelXld]

Re : Importer 100 fichiers excel dans 1 table Access

Rebonsoir à vous

Si n'y a qu'un seul onglet par fichier Excel (et aucune cellule nommée), il est possible de retrouver facilement le nom de la feuille sans ouvrir le classeur, en utilisant le modèle ADOX.

Voici une adaptation:

Sub tranfertFeuilleClasseursFermes_VersAccess_V02()
'Nécessite d'activer la référence Microsoft ActiveX Data Objects x.x Library
'Nécessite d'activer la référence Microsoft ADO ext x.x for DLL and Security
'
Dim Cn As New ADODB.Connection
Dim oProdRS As New ADODB.Recordset, oRS As ADODB.Recordset
Dim oConn As ADODB.Connection
Dim j As Integer
Dim Fichier As String, Repertoire As String, Feuille As String
Dim oCat As ADOX.Catalog

'------------------------------------------------------
'Connection à la Base Access
Set oConn = New ADODB.Connection
oConn.Open "Provider='Microsoft.Jet.OLEDB.4.0';" & _
"Data Source= 'C:\maBase.mdb';"

'les données seront placés dans Table1
Set oRS = New ADODB.Recordset
oRS.Open "Select * from Table1", oConn, adOpenKeyset, adLockOptimistic
'------------------------------------------------------
 
'Boucle sur les classeurs Excel du répertoire cible
Repertoire = "C:\Documents and Settings\mimi\dossier\general\excel\Nomdossier"
Fichier = Dir(Repertoire & "\*.xls")

Do While Fichier <> ""
    'Connection au classeur Excel
    Cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & Repertoire & "\" & Fichier & ";" & _
    "Extended Properties=""Excel 8.0;"""
    
    
    '-------------------------
    Set oCat = New ADOX.Catalog
    Set oCat.ActiveConnection = Cn
    'Récupére le nom de la Feuille:
       'Attention: l'index correspond à un ordre alphabétique croissant
       'et les plages de cellules nommées sont intégrées.
    Feuille = oCat.Tables(0).Name
    '-------------------------
    
    
    'requête pour extraire les données de la Feuil1
    oProdRS.Open "SELECT * FROM [" & Feuille & "]", Cn, adOpenStatic
    
    ' --- Transfert les données dans la base ---
    Do While Not (oProdRS.EOF)
        oRS.addNew
            For j = 0 To oRS.Fields.Count - 1
            oRS.Fields(j) = oProdRS.Fields(j).Value
            Next j
        oRS.Update
        oProdRS.moveNext
    Loop
    '-------------------------------------------
    
    
    Set oCat = Nothing
    oProdRS.Close
    'Fermeture de la connection au classeur Excel
    Cn.Close
Fichier = Dir
Loop

oRS.Close
Set oRS = Nothing
'Fermeture de la connection Access
oConn.Close
Set oConn = Nothing
End Sub

Qu'est ce que je dois faire du code?

Tu places la procédure dans le module standard d'un nouveau classeur Excel.

Tu dois aussi activer les références
Microsoft ActiveX Data Objects x.x Library
et
Microsoft ADO ext x.x for DLL and Security

Dans l'éditeur de macros:
Menu Outils
Références
Coche les lignes "Microsoft ActiveX Data Objects x.x Library" et "Microsoft ADO ext x.x for DLL and Security"
Clique sur le bouton OK pour valider.
x.x dépend de la version installée sur votre poste.


Par contre tes sous totaux risquent de mettre le bazar et je ne garantie plus le résultat (non testé dans ce cas de figure).



Ps:
Allez ami Hervé, Revient, ne fait pas ton timide. ..;o)


bonne soirée
MichelXld