Extraire données de plusieurs fichiers excel vers un fichier pour mener une analyse

Version imprimable

Voir 40 message(s) de cette discussion en une page

15/12/2015, 11h15
YanouW

Extraire données de plusieurs fichiers excel vers un fichier pour mener une analyse

Bonjour,

J'ai beau chercher je ne trouve aucune aide à mon problème, de plus je ne sais pas comment bien le formuler pour me faire comprendre, alors je vais essayer d'etre le plus claire possible.

Je dois extraire des lignes de 10e voir de centaine de fichier pour les regrouper dans un seul fichier et mener une analyse dessus, seulement je me heurte à plusieurs problème.
en effet chaque fichier possède un nom différent: exemple: Ferrari1, ferrari2, adidas 24, etc.... = fichiervente

Mon objectif est plus ou moin de faire comme un TCD: c'est à dire de choisir mes variables (1 ou plusieurs) pour extraire mes données et mener une analyse dessus

seulement mes types de variables sont multiples:

1. nom du fichier vente (ex: ferrari1) et/ou tous les fichier commencant par "ferrari" peu importe le numéro
2. certaines sont dans des cellules fixe de chaque fichiervente (exemple: A1 la données qui me permet de dire que je veux extraire les lignes, mais que sa aille chercher pour tout les fichiervente)
3. une variable (par exemple le nom de l'opérateur) qui s'inscrit dans chacune des lignes que je voudrais extraire (peu importe le fichier vente et peu importe la variable présente dans mes cellule fixe)

je souhaiterais qu'à partir de mon fichier d'analyse, je puisse extraire seulement (tout ou partie) des lignes en fonction de mes différentes variables choisis

Je ne sais pas si cela est possible et encore moins si j'ai été claire, mais un petit voir un gros coup de main serais gentil de votre part

Merci d'avance

Cdt
15/12/2015, 11h24
Menhir

A quoi ressemble le contenu de tes fichiers sources ?
Est-ce que la structure de ces contenus est identique ?
15/12/2015, 11h34
YanouW

1 pièce(s) jointe(s)

Tous mes fichiers source sont identiques, sur le même format
Pièce jointe 195323
15/12/2015, 11h43
Menhir

Quelles sont les données que tu veux en extraire ?
Est-ce que tu souhaites conserver une référence au fichier ou bien seules les données contenues ont de l'intérêt ?
15/12/2015, 11h51
YanouW

Seule les données m'interesse, la référence du fichier m'importe peu

En gros je veux extraire la quasi totalité de la ligne pour faire simple et mener ensuite mon analyse :

en fonction de la ou des variables que je pourrais choisir :
du nom et/ou
du type de vente et/ou
du trieur et/ou
de la date et/ou
du type d'anomalie et/ou
de l'action corrective et/ou
de l'opérateur
15/12/2015, 12h10
Menhir

Citation:

Envoyé par YanouW

En gros je veux extraire la quasi totalité de la ligne

Quelle ligne ? Dans ton exemple il y a beaucoup de données différentes et un tableau aux lignes vides.

Citation:

en fonction de la ou des variables que je pourrais choisir :
du nom et/ou
du type de vente et/ou
du trieur et/ou
de la date et/ou
du type d'anomalie et/ou
de l'action corrective et/ou
de l'opérateur

Ca, ça pourra sans doute se faire avec un Tableau croisé dynamique.
15/12/2015, 12h18
YanouW

1 pièce(s) jointe(s)

oui le tableau est vide car la c'est le tableau vierge qui sert de base pour tout les nouveau fichier qui seront créer au fur est à mesure mais en gros il y aura 200 ou 300 ligne par tableau qui cPièce jointe 195328ommencerons à la ligne 6
15/12/2015, 12h35
Menhir

A mon avis, ça ne peut se résoudre qu'avec une macro.

Tu créés le fichier destination qui va recevoir tes données
Place tous tes fichiers source dans un même répertoire qui ne contienne rien d'autre.

Tu fais une boucle While avec une fonction Dir() qui va scruté tous les fichiers de ton répertoire. Ainsi, tu auras la liste de tous les fichiers.

Pour chacun, tu l'ouvres avec un Workbooks.Open.
Tu repères le numéro de la dernière ligne de ta zone à copier avec un

Code:

Cells(Rows.Count,1).End(xlUp).Row

Tu fais un Copy sur toute la zone à copier.

De la même façon, dans ton fichier destination, tu repères la première ligne vide et tu y colles avec un PasteSpecial ce que tu viens de copier.

Tout ça te permettra de mettre les données de tous tes fichiers en un seul.

Ensuite, pour faire tes analyse, utilise un tableau croisé dynamique.
15/12/2015, 12h40
YanouW

Merci menhir je vais essayer sa, seulement si j'ai 4oo fichiers avec 300 lignes, cela ne seras t il pas trop lourd ou trop long ?
15/12/2015, 13h00
Menhir

J'ai oublié de préciser dans mon processus que chaque fichier source est bien sûr à fermer après la copie et avant d'ouvrir le suivant.

300 x 400 ça fait 120 000 lignes.
Effectivement, ça risque de faire lourd à traiter.
Suivant ta version d'Excel, ça dépasse même le nombre de lignes acceptable.

Si c'est vraiment ta quantité de données et que tu ne peux pas la réduire, ta seule solution sera de passer par un gestionnaire de base de données.
16/12/2015, 14h38
YanouW

je reviens vers vous car j'ai résolue le problème des données qui se trouve uniquement dans les cellules fixe, en le reportant dans chaque ligne grâce à une macro,

cependant pour l'extraction de données sa me pose un peu plus de problème:
je défini mes variables
chemin: lieu ou se trouve tout mes fichiers
fichier : j'ai mis ".xls" pour que sa importe les données de tous les fichier excel présent dans ce repertoire
feuille: pour que sa importe les lignes de la feuille concerné uniquement

mais ensuite je suis bloqué
la fonction dir() je ne comprend pas comment elle fonctionne
ensuite je ne vois pas comment définir que les lignes à extraire se trouve à partir de la ligne 6 jusqu'a la derniere ligne remplie ou non vide
puis de dire que je veux extraire de la colonne B à Z

et de coller tous sa dans le fihier de destination

je sais que vous aller me prendre pour un neuneu mais je suis débutant en VBA et ne comprend pas toute les subtilité, généralement je me débrouille en passant par une macro et en adaptant le code, mais le je suis bloqué, j'ai vraiment besoin d'aide et d'une piste sur le code à taper

Merci

je viens de trouver un morceau de code que j'ai adapter:

mais le problème est que :
sa ne me copie que la première ligne du premier fichier du repertoire
et que sa m'ouvre le fichier concerné en mettant 2 messagemettre à jour les liaisons et de modifier les liaisons ou continuer

je vous met le code: que dois_je modifier pour que sa m'extrait toute les lignes non vides de tout les fichiers présent dans le repertoire ?

Code:

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
Private Sub Test()
 
    Dim sFichier As String 'chemin du fichier source
    Dim oWBSource As Workbook 'fichier source
    Dim oShSource As Worksheet 'onglet source
    Dim oShCible As Worksheet 'onglet où on récupère
 
    'chemin du fichier source
    '
    sFichier = "N:\LYON 2\Echange\Yann ASPERT\Fichier anomalie\*.xls"
    'vérif existe
    If Dir(sFichier) = "" Then
        MsgBox "Fichier absent : " & vbCrLf & sFichier, vbExclamation
        Exit Sub
    End If
 
    'cet onglet
    Set oShCible = Worksheets(1)
 
    'ouverture en lecture seule
    Set oWBSource = Workbooks.Open(sFichier, , True)
 
    'onglet
    Set oShSource = Worksheets(1)  'voir quel onglet on veut traiter
    'récupération d'une valeur (exemple)
    oShCible.Range("A4:AA4").Value = oShSource.Range("A6:AA6").Value
    'fermeture sans enregistrer
    oWBSource.Close False
 
    Set oShSource = Nothing
    Set oWBSource = Nothing
    Set oShCible = Nothing
 
End Sub

18/12/2015, 09h36
Menhir

Pour avoir de bonnes bases en VBA :
Un cours complet : http://bidou.developpez.com/article/VBA/
Une fiche plus synthétique sur les structures : http://mhubiche.developpez.com/vba/f...yntaxes/bases/
Plus spécifique sur l'utilisation des boucles : http://silkyroad.developpez.com/vba/boucles/

Pour l'utilisation de la commande Dir(), voir l'aide VBA intégrée à Excel. Elle explique la façon de l'utiliser et donne des exemples.
21/12/2015, 10h31
YanouW

Bonjour,

Je reviens vers vous, j'ai réussi à faire ma boucle mais seulement sa ne m'ouvre que le premier fihier present dans le repertoire et me copie du coup que les lignes de ce fichier q'une seule fois,

Je ne comprend pas comment faire pour que sa me copie toute les lignes présente de chaque fichier, en gros il faudrait:
-que sa me fasse une boucle sur chaque fichier
-que sa m'extrait toute les lignes remplie
-que s'a m'insere l'une en dessous de l'autre les lignes de chaque fichier

Merci de votre aide la je bloque vraiment et mes connaissance en VBA ne sont pas assez bonne
21/12/2015, 10h57
Menhir

Pour qu'on puisse comprendre où est ton erreur, il faudrait que tu présentes le code que tu as écrit.

Je te conseille aussi de regarder ce code source :
http://excel.developpez.com/sources/...ersDecroissant
Tu ne pas besoin de la partie "tri par date" mais la première partie (la première boucle Do Loop) te permettra de comprendre comment lister les fichiers d'un répertoire.

Code:

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
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
Private Sub Test()
'''''''''''''''''''''''''''''''
'''Compte nombre de fichier pour la boucle
 
 
Dim Chemin As String
Dim rep As String
Dim nbfichier As Integer
Chemin = "N:\LYON 2\Echange\Fichier anomalie\"
rep = Dir(Chemin & "*.xls")
While Not rep = ""
nbfichier = nbfichier + 1
rep = Dir
 
Wend
 
Dim x As Integer
For x = 1 To nbfichier
''''''''''''''''''''''''''''''''''''''
    Dim sFichier As String 'chemin du fichier source
    Dim oWBSource As Workbook 'fichier source
    Dim oShSource As Worksheet 'onglet source
    Dim oShCible As Worksheet 'onglet où on récupère
 
    'chemin du fichier source
 
    sFichier = "N:\LYON 2\Echange\Fichier anomalie\*.xls"
 
    'vérif existe
 
    If Dir(sFichier) = "" Then
        MsgBox "Fichier absent : " & vbCrLf & sFichier, vbExclamation
        Exit Sub
    End If
 
    'cet onglet
    Set oShCible = Worksheets(1)
 
    'ouverture en lecture seule
    Set oWBSource = Workbooks.Open(sFichier, , True)
 
    'onglet
    Set oShSource = Worksheets(1)  'voir quel onglet on veut traiter
    'récupération d'une valeur (exemple)
    oShCible.Range("A2:Z150").Value = oShSource.Range("B6:AA150").Value
 
 
    'fermeture sans enregistrer
    oWBSource.Close False
 
    Set oShSource = Nothing
    Set oWBSource = Nothing
    Set oShCible = Nothing
 
  Next x
End Sub

21/12/2015, 11h31
Menhir
Je ne comprends pas dans ton code pourquoi tu as mis Set oShCible = Worksheets(1) à l'intérieur de la boucle.
A priori, ta cible de change pas en fonction de la source. Je te conseille donc de mettre cette ligne avant le For to.

A mon avis, ton problème vient de :

Code:

oShCible.Range("A2:Z150").Value = oShSource.Range("B6:AA150").Value

Tu recopies les valeurs de chaque fichier source au même endroit dans le fichier cible.
Donc, bien sûr, les données d'un fichier effacent (ou plutôt écrivent dessus) les données du fichier précédent.
Il faudrait que tu les écrivent à la suite.
Par exemple comme ça :
Code:

1 2 oShSource.Range("B6:AA150").Copy oShCible.Cells(Cells(Rows.Count),1).End(xlUp).Row,1).PasteSpecial(xlPasteValues)
Personnellement, quand il faut transférer les valeur de toute une zone, je préfère passer par un Copy/PasteSpecial plutôt que par un Value=Value.
21/12/2015, 11h35
YanouW

Merci

Quand je met le code suivant sa me met erreur de syntaxe ??

Code:

oShCible.Cells(Cells(Rows.Count),1).End(xlUp).Row,1).PasteSpecial(xlPasteValues)
21/12/2015, 11h41
Menhir

J'ai mis une parenthèse de trop.

Code:

oShCible.Cells(Cells(Rows.Count,1).End(xlUp).Row,1).PasteSpecial(xlPasteValues)
21/12/2015, 11h48
Marc-L

Bonjour, bonjour !

Menhir, ne manque-t-il pas la feuille devant le second Cells ?

Et puis en respectant le modèle objet, il n'y en aurait besoin que d'un :

oShCible.Cells(Rows.Count,1).End(xlUp).PasteSpecial(xlPasteValues) …

______________________________________________________________________________________________________
Je suis Paris, …

Voir 40 message(s) de cette discussion en une page