Macro pour création de fichier

[geoffberlin]

Bonjour à tous,

Je suis étudiant, actuellement en stage en business intelligence pour une start-up à Berlin. J'ai appris Excel au fil du temps, j'arrive normalement à me débrouiller avec vlookup et ses confrères. Sauf que aujourd'hui, je me heurte à un mur...

Je dispose d'une liste excel d'environ 50 colonnes et 1 million de lignes qui répertorie des infos clients. Il me faut grouper toutes les infos par adresse, donc filtrer pour combinaison Rue + Numero + Ville, puis placer chaque groupe, donc toutes les infos pour cette combinaison, dans un nouveau fichier. J'ai pu arriver à mettre en place le filtre pour classer les signes par combinaison d'adresse, mais il y en a plus de 2500... j'ai donc pensé pouvoir créer une macro qui s'occupe de cette tâche et me fasse économiser quelques jours de travail.

Seulement, je n'y connais rien, je n'ai jamais utilisé de macros. Je suis passé sur le net, faire des tutoriels basiques, le problème est que je me retrouve un peu perdu dans toutes les possibilités, je ne sais pas dans quelle direction creuser... d'où mon poste ici.

Je mets en pièce jointe un exemple, dont j'ai effacé les données pour raison de confidentialité.

Je vous remercie d'avance pour tout éclairement

Cordialement,

Geoff

 

[Efgé]

Re : Macro pour création de fichier

Bonsoir geoffberlin, le fil, le forum
Juste par curiosité je fais une proposition, mais sur 1 000 000 de lignes et 2 500 classeurs à créer je n'ai aucune idée des temps de traitement... J'utilise une fonction personelle pour être sûr que les adresses sont valides en nom de classeur.
Pour essayer la macro, il faut enregistrer l'exemple dans un dossier de test. Les classeurs seront créer dans le même dossier.
Si cela te vas et que tu à besoin, je commenterai le code (certainement demain soir...).

Sub test()
Dim i&
Dim D As Object, Wkb As Workbook, Liste As Variant, C As Variant
Set D = CreateObject("Scripting.dictionary")
With Application
    .ScreenUpdating = False
    .DisplayAlerts = False
    .Calculation = xlCalculationManual
End With

Sheets("ex").Copy
Set Wkb = ActiveWorkbook

With Wkb.ActiveSheet
    Liste = .Range(.Cells(2, 34), .Cells(Rows.Count, 34).End(3)(2))
    For i = LBound(Liste, 1) To UBound(Liste, 1)
        If Liste(i, 1) <> "" Then D(Liste(i, 1)) = 0
    Next i
    
    For Each C In D.Keys
        Workbooks.Add 1
        .Rows(1).Copy ActiveWorkbook.ActiveSheet.Cells(1, 1)
        For i = .Cells(Rows.Count, 34).End(3).Row To 2 Step -1
            If .Cells(i, 34) = C Then
                .Rows(i).Copy ActiveWorkbook.ActiveSheet.Cells(Rows.Count, 34).End(3)(2, -32)
                .Rows(i).Delete
            End If
        Next i
        ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & SheetName(C)
        ActiveWorkbook.Close
    Next C

    Wkb.Close False
End With

With Application
    .ScreenUpdating = True
    .DisplayAlerts = True
    .Calculation = xlCalculationAutomatic
End With
End Sub

Cordialement

 

[geoffberlin]

Re : Macro pour création de fichier

Bonjour Efgé,


Tout d'abord merci beaucoup ! J'ai pu tester avec mon exemple et aussi avec une liste raccourcie, c'est vraiment super, je suis bluffé !

Pour ce qui est des temps de traitement, j'ai pris environ 100 adresses et tout a été crée en moins de 2 minutes, je vais tester en fin de journée avec la liste complète.

J'ai une question au sujet de la fonction personelle pour la validité des adresses: dans le dossier de destination des classeurs, certains sont au format Excel, d'autres ne comportent pas d'extension et sont de type, selon windows, "adresse-datei". Mais un simple glissé déposé dans Excel permet malgré tout d'ouvrir la table normalement. J'utilise Excel en Allemand, penses tu que cela puisse influer ?

Quant au commentaire du code, je prends volontier, si tu peux.

Bonne soirée,

Geoff

 

[Efgé]

Re : Macro pour création de fichier

Bonjour geoffberlin,
Content que ma proposition te convienne
Pour le problème de l'extension du fichier, c'est une erreur de ma part.
Remplace
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & SheetName(C)
par
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & SheetName(C) & ".xlsx"
désolé...
Pour les commentaires, je vais m'y mettre, je te les promets pour demain soir (enfin soir pour moi...) au plus tard.
Je suis curieux de connaitre les temps de traitements sur ton vrai fichier
A demain
Cordialement

 

[geoffberlin]

Re : Macro pour création de fichier

Bonjour Efgé,

J'ai corrigé pour l'extension de fichier, et je viens d'essayer sur le vrai fichier.

Alors quelques infos: taille 272mo, 42 colonnes sur exactement 1,048,576 lignes. Démarrage à 19h32 au chrono, et malheureusement, abandon après moins d'une minute, le debugger renvoit à la ligne dans la capture d'écran For i = LBound (Liste, 1) To UBound (Liste, 1)

Ne sachant pas à quoi c'est dû, j'ai ordonné par ordre alphabétique la colonne qui contient la clef adresse, et nouveau test démarré à 19h57 interrompu moins d'une minute après encore une fois, et cependant avec une nouvelle erreur, en allemand Laufzeitfehler '1004' - Run-time error '1004', je viens de trouver la solution sur l'assitance de MS, je teste demain.

Merci en tous cas de ton aide,
Bonne soirée,

Geoff

 

[Efgé]

Re : Macro pour création de fichier

Re
J'ai peur que l'on se confronte à une limite du VBA
La liste est en fait un tableau et, peut être, ne peut on pas mettre autant d'éléments...
Une autre proposition en pièce jointe sans tableau.
J'utilise aussi un dictionnaire, si celui-çi n'accepte pas le nombre d'adresse nécessaire, je ne vois pas comment faire.
J'ai ajouté un message dans la barre de statut (en bas, a gauche, de la fenetre Excel) pour visualiser l'avancement de la macro.
Tiens moi au courant des nouvelles aventures.
Cordialement

 

[geoffberlin]

Re : Macro pour création de fichier

Salut Efgé, le forum,

Après de petits tatonnements, j'ai réussi à exécuter la macro parfaitement ! Je pense que le problème venait du fait que il y avait beaucoup trop de lignes avec un champ adresse vide... je les ai donc supprimées.

Au final, j'ai réussi avec cette macro en divisant mon fichier premier en trois, d'environ 90mo chacun, et environ 280,000 lignes.
Ce qui a donné au final 2660 tables environ, et l'ordinateur a mis environ 2h, au final bien moins que ce a quoi je m'attendais.

En tous cas, ça a marché, merci de ton aide !
Cordialement,

Geoff