Discussion :

[DanRo1]

Bonjour,
Voici une macro que j’ai créée. L’idée est d’importer des données de plusieurs fichiers Excel.xlsx dans un fichier nommé « _Suivi.xlsm ». Le tout est sur un serveur de compagnie.
Voici mes requêtes.
A) Erreur d'exécution '1004': La méthode de la classe Range a échoué (voir la ligne 15).
B) J’aimerais que la macro soit plus performante, présentement elle dure 2h20 pour importer environ 800 fichiers et, dans un certain temps, il y aura environ 3700 fichiers à importer. Un fichier peut contenir une ligne ou plusieurs lignes à importer.
C) J’aimerais insérer une barre de défilement de 0% à 100% durant le temps d’importation.
D) A la ligne 20, j’indique de prendre le nom du fichier actif qui est importé à ce moment et de le mettre en colonne B à chaque ligne importée de ce fichier. Exemple, sIl y a 10 lignes du fichier « PPQAI-1001.xlsx » qui seront importées, alors il y aura 10 lignes à la colonne B du fichier « _Suivi.xlsm » qui portera le nom du fichier « PPQAI-1001.xlsx ». Ensuite j’enlève PPQAI et .xlsx. Mais j’aimerais mieux avoir le nom du client qui provient d’une plage de cellule fixe, mais fusionnée « G2 à N2 ».
Voici donc la macro.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
Sub CreationSynthese()
'Effacer la base de données du fichier _Suivi
  Range("B3:S1048576").Select
  Selection.ClearContents
'Blocage du recalcul automatique pour gagner du temps
  Application.Calculation = xlCalculationManual
'Désactiver le rafraîchissement de l'écran pour gagner du temps
  Application.ScreenUpdating = False
'Nomme le chemin d'accès, sur le serveur, le classeur où tous les fichiers PPQAI en .xlsx sont conservés
  Dim filen As String, filepath As String, myfile As String
  filen = "*.xlsx"
  filepath = "\\srvdanco\PPQAI\"
  myfile = Dir(filepath & filen)
'Avise que tant qu'il y a des noms de fichiers (nb de caractères > que zéro), de les prendre en compte
  While Len(myfile) > 0
  Workbooks.Open (filepath & myfile)
'Indique comment copier les PPQAI de E5 à V...
  AvantDerniereLigne = ActiveSheet.UsedRange.Rows.Count - 5
  Range("E5:V" & AvantDerniereLigne).Copy
'Ouvre le fichier _Suivi et colle les valeurs de tous les PPQAI un à la suite de l'autre à la position désirée
  Workbooks("_Suivi.xlsm").Activate
  DebutNomFichier = ActiveSheet.UsedRange.Rows.Count + 2
  Range("B" & DebutNomFichier).Select
  Selection.PasteSpecial Paste:=xlPasteValues
'Efface les lignes sans données
  For i = [C1048576].End(xlUp).Row To 2 Step -1
  If Len(Cells(i, 3)) = "" Or Cells(i, 3) = 0 Then
  Rows(i).Delete
  Range("B" & DebutNomFichier & ":B" & ActiveSheet.UsedRange.Rows.Count) = myfile
'Renommer correctement la colonne B avec le no du client
  Columns("B:B").Replace ".xlsx", ""
  Columns("B:B").Replace "PPQAI-", ""
  End If
  Next
'Désactive la bte de dialogue du presse papier à la fermeture de chaque fichier
  Application.CutCopyMode = False
  Workbooks(myfile).Close False
'Donne l'ordre d'ouvrir un après l'autre tous les fichiers du classeur et de répéter l'action
  myfile = Dir
  Wend
'Importation terminée, quelques ajustements finaux au fichier _Suivi
  Range("A1").Select
    'Remise en route du recalcul automatique
  Application.Calculation = xlCalculationAutomatic
'Réactive le rafraîchissement de l'écran pour gagner du temps
  Application.ScreenUpdating = True
  MsgBox "L'importation des PPQAI est terminé"
  End Sub

[mathspountz]

Bonjour,

Je pense que 90% du temps de traitement de ta macro est du à l'ouverture des fichiers Excel. Il faudrait donc trouver une solution alternative :
1) Si possible, dire au maillon de la chaîne en aval qui produit les 3700 fichiers de ne pas produire des fichiers Excel mais des fichiers textes

2) Utiliser l'objet ADODB qui te permettra de requêter les fichiers sans les ouvrir et en plus de filtrer les données en un claquement de doigt.

Tu peux également fusionner les deux options.


Pour la barre de défilement je te conseil un userform avec deux label les un par dessus les autres. Tu mets à jours la largeur du label1 à chaque fin de boucle : Label1.width = n/n_total * label2.width avec n = fichier courant et n_total nombre de fichiers total.



Surtout indente ton code car il est illisible

Évite tous les select et activate. Exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
Range("B3:S1048576").Select
Selection.ClearContents

Devient

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
Range("B3:S1048576").ClearContents

[T0n10]

Bonjour,

Je suis pas vraiment spécialiste, mais le While len(myfile) ne me convainc pas vraiment. Len renvoie une longueur d'une string et là ton string vaudra toujours quelque chose, en l'occurrence au moins filepath.

Pour parcourir les fichiers d'un repertoires j'aurai plutôt vu quelque chose du genre :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
Rep = "\\srvdanco\PPQAI\"
file = Dir(Rep)
Do While file <> ""
'code
Loop

Tonio

[Promethee25]

1) Si possible, dire au maillon de la chaîne en aval qui produit les 3700 fichiers de ne pas produire des fichiers Excel mais des fichiers textes

+1 avec mathspountz, le compromis pouvant être un csv ouvrable facilement dans Excel mais outillable en mode fichier texte par une macro qui serait alors très rapide.

[parmi]

Bonjour,

J'ajouterais que la suppression des lignes et le Replace de la colonne B pourrait se faire à la fin, une fois tous les fichiers importés.
Plutôt que la suppression en boucle, tu pourrais mettre un filtre et supprimer toutes les lignes vides d'un coup. Ou encore faire un tri qui repousserait les vides à la fin...

[Marc-L]

Bonjour,

la solution du tri puis la suppression d'un bloc unique de lignes contigües est à ma connaissance la plus rapide …

______________________________________________________________________________________________________

Je suis Paris, Charlie, Bruxelles, …

[DanRo1]

Avant de pouvoir améliorer ma macro, j'ai ce problème qui me bloque.
Erreur 1004 dû à Workbooks("_Suivi.xlsm").Activate.
Il y a eu plusieurs discussion à ce sujet, mais je trouve pas.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
Workbooks("_Suivi.xlsm").Activate
  DebutNomFichier = ActiveSheet.UsedRange.Rows.Count + 2
  Range("B" & DebutNomFichier).Select