Créer des fichiers Excel à partir d'une BDD Excel

Le 16/11/2023 à 10:17

Bonjour,

j'ai un tableau (ci joint) à partir duquel je souhaite créer un fichier Excel pur chaque changement N°INSEE ou chaque fichier reprend la ligne d'entête et les lignes associées au N°INSEE.

Chaque fichier portera le nom de la commune. l'ensemble des fichiers pourra être rangé dans le répertoire \david\communes.

je vous remercie par avance de votre aide

22fichier-communes-light.zip (1.14 Mo)
Le 16/11/2023 à 14:08

Bonjour,

La méthode ci-dessous comporte 3 étapes :

- 1 Création d'un tableau structuré correspondant à votre tableau dans Stban

- 2 Création d'un tableau structuré des communes à partir du premier tableau.

- 3 Génération des fichiers.

Les programmes doivent s'enchainer dans l'ordre.

Sub P01_CreationTableauStructureStBan()

    With Sheets("STBAN")
         If .ListObjects.Count > 0 Then
            If .ListObjects(1).Name = "t_Stban" Then
               MsgBox "Le tableau structuré existe déjà !", vbCritical
               Exit Sub
            End If
         End If
    End With

    With Sheets("STBAN")
         If .ListObjects.Count = 0 Then
            .ListObjects.Add(xlSrcRange, .Range("$A$1").CurrentRegion, , xlYes).Name = "t_Stban"
         End If
    End With

End Sub
Sub P02_CreationTableDesCommunes()

Dim I As Integer
Dim ShCommunes As Worksheet
Dim AireCommunes As Range

    For I = 1 To Sheets.Count
        With Sheets(I)
             If .ListObjects.Count > 0 Then
                If .ListObjects(1).Name = "t_Communes" Then
                    MsgBox "Le tableau structuré existe déjà !", vbCritical
                    Exit Sub
                End If
             End If
        End With
    Next I

    Set ShCommunes = Sheets.Add(after:=Sheets(Sheets.Count))
    Range("t_Stban[[#All],[C_INSEE]:[LIB_COM]]").Copy Destination:=ShCommunes.Range("A1")

    With ShCommunes
            Set AireCommunes = .Range("$A$1").CurrentRegion
            AireCommunes.CurrentRegion.RemoveDuplicates Columns:=1, Header:=xlYes
            .Sort.SortFields.Clear
            .Sort.SortFields.Add2 Key:=AireCommunes.Columns(1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers
            With .Sort
                .SetRange AireCommunes
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
            .ListObjects.Add(xlSrcRange, Range("$A$1").CurrentRegion, , xlYes).Name = "t_Communes"
    End With

End Sub
Sub P03_GenererLesFichiers()

Dim I As Integer
Dim TabStban As ListObject
Dim AireCommunes As Range

    Set AireCommunes = Range("t_Communes[C_INSEE]")
    Set TabStban = Sheets("STBAN").ListObjects("t_Stban")
    For I = 1 To AireCommunes.Count
        CreerUnFichier TabStban, AireCommunes(I), AireCommunes(I).Offset(0, 1)
    Next I
    Set AireCommunes = Nothing: Set TabStban = Nothing

End Sub

Sub CreerUnFichier(ByVal TableData As ListObject, ByVal CodeInsee As Variant, ByVal NomCommune As String)

Dim I As Integer
Dim WbCommune As Workbook
Dim NomTableau As String
Dim AireACopier As Range, AireTitre As Range

    NomTableau = "t_" & CodeInsee

    With TableData
         .Range.AutoFilter Field:=5, Criteria1:=CodeInsee
    End With

    Set AireTitre = Range("t_Stban[#Headers]")
    Set AireACopier = Range("t_Stban").SpecialCells(xlCellTypeVisible)
    Set WbCommune = Workbooks.Add

    With WbCommune.Sheets(1)

         AireTitre.Copy Destination:=.Range("A1")
         AireACopier.Copy Destination:=.Range("A2")
         With .Range("A1").CurrentRegion
              With .Interior
                   .Pattern = xlNone
                   .TintAndShade = 0
                   .PatternTintAndShade = 0
              End With
         End With

         .ListObjects.Add(xlSrcRange, .Range("$A$1").CurrentRegion, , xlYes).Name = NomTableau
         .ListObjects(NomTableau).TableStyle = "TableStyleMedium3"
         .Cells.EntireColumn.AutoFit

         Application.PrintCommunication = False
         With .PageSetup
            .PrintTitleRows = "$1:$1"
            .PrintTitleColumns = ""
            .Orientation = xlLandscape
            .FitToPagesWide = 1
            .FitToPagesTall = False
        End With
        Application.PrintCommunication = True

    End With

    Application.DisplayAlerts = False
    With WbCommune
         .SaveAs Filename:="C:\Users\XXXXXXXX\C " & Format(CodeInsee, "00000") & " " & NomCommune & ".xlsm", FileFormat:= _
                      xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False  ' A adpater
         .Close
    End With
    Application.DisplayAlerts = True

    TableData.Range.AutoFilter Field:=5

    Set WbCommune = Nothing:  Set AireACopier = Nothing:  Set AireTitre = Nothing

End Sub
Le 16/11/2023 à 14:39

Bonjour,

Une seconde proposition qui ne nécessite pas de modifier les données au cas où elles proviendraient d'un téléchargement. J'ai ajouté un bouton de lancement sur la feuille STBAN et il faut au préalable modifier le chemin dans le module "Module1" constante "PATH_ENREG" au début du module (il ne contient qu'une seule macro qui est lancée par le bouton).

12fichier-communes-light-2.zip (1.15 Mo)

Cdlt,

Cylfo

Le 16/11/2023 à 17:16
10dale-fichier-communes-light-v1.xlsm (1.35 Mo)

Bonjour à tous,

Une autre proposition :
J'ai pris le partie d'une méthode qui va construire les feuilles des villes d'abord dans le classeur source puis, dans une deuxième étape, de sauvegarder les feuilles de chaque commune dans leur classeur. Ça permet d'avoir les données ventilées dans un classeur unique - au cas où -.

Étape 1 :

  • Sur la feuille "Menu", cliquer sur le bouton "Ventiler..."
  • la macro vous demandera de sélectionner une cellule de la feuille source (c'est savoir où est la base de données). Sélectionner la feuille des données sources, sélectionnez-y n'importe quelle cellule puis cliquer sur OK dans la boite de dialogue.

Étape 2 :

  • Sur la feuille "Menu", indiquez dans la cellule A20 indiquez le nom du répertoire de sauvegarde (on a pris l'hypothèse que c'est un sous-répertoire du répertoire du classeur)
  • Puis cliquez sur le bouton "Créer..."
Le 16/11/2023 à 18:54

je vous remercie pour ces deux solutions. la solution n°2 de Cylfo répond parfaitement à ma demande un grand merci à Cylfo.

La solution de Mafraise me semble une bonne alternative surtout l'étape de sauvegarde. malheureusement je ne passe pas l'étape 2 => l'emplacement que je donne n'est pas reconnu?! quelle est le format a mettre en place mon répertoire est C:\Users\DavidL\Downloads\test?

Encore merci pour votre aide.

Le 17/11/2023 à 09:18

Bonjour dale,

Est-ce une opération ponctuelle ou allez-vous la répéter ?

Si oui, il serait préférable d'externaliser la macro dans un classeur indépendant et en modifiant la macro pour demander le fichier à traiter et le dossier de sauvegarde. Concernant le fichier source à traiter c'est le résultat d'un téléchargement ? si oui, à quel format txt, csv et ce fichier pourrait-il être traité directement ?

Pour info, dans le nom des classeurs, j'ai ajouté entre parenthèses le n° insee de la commune au cas où il y aurait des homonymies.

Bonjour mafraise,

Je me posais également une question sur le fichier fourni, est-ce le fichier réel ou un jeu d'essai car ce dernier comporte déjà 64 communes et si ce n'est qu'un jeu d'essai, en fonction du nombre réel de communes cela pourrait vite faire "exploser" le classeur si les données de chaque commune sont dupliquées dans le classeur ...

Cdlt,

Cylfo

Le 17/11/2023 à 09:58

Bonjour Cylfo,

le fichier source est téléchargé au format csv mais nécessite quelques transformations avant d'appliquer la macro mais cela est facilement gérable. Cette opération se répétera.

bat,

Dal

Le 17/11/2023 à 11:15

Ok mais sa volumétrie réelle ? celle du fichier déjà fourni ou plus ? Si oui, une idée du nombre de communes ?

Le 17/11/2023 à 14:26

Bonjour Dale

@Cylfo

Bonjour mafraise,

Je me posais également une question sur le fichier fourni, est-ce le fichier réel ou un jeu d'essai car ce dernier comporte déjà 64 communes et si ce n'est qu'un jeu d'essai, en fonction du nombre réel de communes cela pourrait vite faire "exploser" le classeur si les données de chaque commune sont dupliquées dans le classeur ...

Oui je me suis posé la question mais bon j'ai tenté.

Évidemment si le fichier comporte beaucoup de communes tu as raison => le fichier va exploser comme une baudruche.

Effectivement, demander le classeur source et le répertoire destination dans un fichier autonome serait un plus.

image
Le 17/11/2023 à 16:32

Bonjour dale,

Une version n'incluant que le programme avec la sélection du fichier à traiter et du dossier d'enregistrement au lancement de la macro (bouton sur le formulaire).

  • Si des fichiers de destination existent déjà, ils sont écrasés par la nouvelle version.
  • La feuille de lancement est verrouillée sans mot de passe, juste pour éviter une modif involontaire.
  • Le code est commenté mais si besoin d'infos complémentaires pas de souci.
8dispatch-communes-1.xlsm (30.22 Ko)

Cdlt,

Cylfo

Le 17/11/2023 à 18:28

Bonjour Cylfo,

Le fichier source comporter +3k communes pour environ +1M de lignes. Je n'extraie qu'un certain nombre de commune -> dans un autre fichier et c'est a partir de ce fichier que je vais travailler avec ta macro.

J'apprécie les commentaires:). encore merci pour ton aide et ton travail,

Cdlt,

Dal

Rechercher des sujets similaires à "creer fichiers partir bdd"