Note: The other languages of the website are Google-translated. Back to English
Se connecter  \/ 
x
or
x
INSCRIPTION  \/ 
x

or

Comment créer rapidement des dossiers en fonction des valeurs de cellule dans Excel?

Supposons que vous ayez une liste de noms de personnel dans une plage d'une feuille de calcul et que vous souhaitiez maintenant créer des dossiers pour chacun d'eux dans les cellules afin d'enregistrer leurs informations. Si vous créez les dossiers un par un, cela vous fera perdre beaucoup de temps. Mais comment pouvez-vous créer rapidement ces dossiers? Aujourd'hui, je vais vous présenter quelques astuces rapides:

Créer des dossiers basés sur les valeurs de cellule avec le code VBA

Créez rapidement des dossiers basés sur les valeurs de cellule avec Kutools for Excelbonne idée3


Créer des dossiers basés sur les valeurs de cellule avec le code VBA

Par exemple, j'ai une plage de noms dans une feuille de calcul, je veux créer des dossiers pour chacun d'eux et les enregistrer dans un chemin spécifié, avec le code VBA, je peux terminer cette tâche.

1. Placez le classeur actif dans un répertoire spécifique dans lequel vous placerez les dossiers créés. Ouvrez le classeur et sélectionnez la plage de cellules que vous souhaitez utiliser.

doc-créer-dossiers1

2. Cliquez Promoteurs > Visual Basic, Un nouveau Microsoft Visual Basic pour les applications la fenêtre s'affichera, cliquez sur insérer > Moduleet entrez le code suivant dans le module:

Code VBA: créer des dossiers en fonction des valeurs de cellule

Sub MakeFolders()
Dim Rng As Range
Dim maxRows, maxCols, r, c As Integer
Set Rng = Selection
maxRows = Rng.Rows.Count
maxCols = Rng.Columns.Count
For c = 1 To maxCols
r = 1
Do While r <= maxRows
If Len(Dir(ActiveWorkbook.Path & "\" & Rng(r, c), vbDirectory)) = 0 Then
MkDir (ActiveWorkbook.Path & "\" & Rng(r, c))
On Error Resume Next
End If
r = r + 1
Loop
Next c
End Sub

3. Cliquez doc-multiplier-calcul-3bouton pour exécuter le code. Toutes les cellules sélectionnées ont été créées dans des dossiers avec leurs valeurs. Et les dossiers sont placés dans le chemin d'accès comme le classeur actif. Voir la capture d'écran:

doc-créer-dossiers2


Créez rapidement des dossiers basés sur les valeurs de cellule avec Kutools for Excel

Il est facile et pratique pour vous de créer des dossiers basés sur les valeurs de cellule si vous utilisez l'outil Kutools for Excel.

Kutools pour Excel, avec plus de 300 fonctions pratiques, rend vos travaux plus faciles. 

Après l'installation de Kutools for Excel, procédez comme suit:(Téléchargez gratuitement Kutools pour Excel maintenant!)

1. Sélectionnez la plage dans laquelle vous souhaitez créer des dossiers.

2. Cliquez Kutools Plus > Import / Export > Créer des dossiers à partir du contenu des cellules…, Voir capture d'écran:

doc créer le dossier 1

3. Dans le Créer des dossiers à partir du contenu des cellules boîte de dialogue, cliquez sur doc-créer-dossiers3pour spécifier le chemin dans lequel vous souhaitez enregistrer les dossiers. Capture d'écran vue:

doc créer le dossier 2

4. Cliquez OK. Et une boîte de dialogue vous rappellera combien de dossiers ont été créés. Voir la capture d'écran:

doc créer le dossier 3

5. Cliquez OK. Et toutes les valeurs de la plage sélectionnée ont été créées dans des dossiers dans le dossier spécifié.
doc créer le dossier 4


Répertorier tous les noms de fichiers d'un dossier dans une feuille

pour en savoir plus sur cette fonction.

Voici un utilitaire dans Kutools pour Excel - Liste des noms de fichiers peut lister tous les noms de fichiers d'un dossier dans une feuille, si cela vous intéresse, continuez à lire.

Après l'installation de Kutools for Excel, procédez comme suit:(Téléchargez gratuitement Kutools pour Excel maintenant!)

1. Cliquez Kutools Plus > Import / Export > Liste des noms de fichiers.
doc créer le dossier 5

2. dans le Liste des noms de fichiers boîte de dialogue, sélectionnez un dossier dont vous souhaitez lister ses fichiers, la coche Tous les fichiers or Spécifier option selon vos besoins Type de fichiers section.
img src = "https: // cdn.extendoffice.com / images / stories / doc-excel / create-folders / doc-create-folder-6.png "alt =" doc create folder 6 "/>

3. Cliquez Ok, une nouvelle feuille est créée avec tous les noms de fichiers et certaines informations relatives.
doc créer le dossier 7


Les meilleurs outils de productivité de bureau

Kutools for Excel résout la plupart de vos problèmes et augmente votre productivité de 80%

  • Réutilisation: Insérer rapidement formules complexes, graphiques et tout ce que vous avez utilisé auparavant; Crypter les cellules avec mot de passe; Créer une liste de diffusion et envoyer des e-mails ...
  • Barre Super Formula (modifiez facilement plusieurs lignes de texte et de formule); Disposition de lecture (lire et modifier facilement un grand nombre de cellules); Coller dans la plage filtrée...
  • Fusionner les cellules / lignes / colonnes sans perdre de données; Contenu des cellules divisées; Combiner des lignes / colonnes en double... Empêcher les cellules en double; Comparer les gammes...
  • Sélectionnez Dupliquer ou Unique Lignes; Sélectionnez les lignes vides (toutes les cellules sont vides); Super Find et Fuzzy Find dans de nombreux classeurs; Sélection aléatoire ...
  • Copie exacte Plusieurs cellules sans changer la référence de formule; Créer automatiquement des références à plusieurs feuilles; Insérer des puces, Cases à cocher et plus encore ...
  • Extrait du texte, Ajouter du texte, Supprimer par position, Supprimer l'espace; Créer et imprimer des sous-totaux de pagination; Conversion entre le contenu et les commentaires des cellules...
  • Super filtre (enregistrer et appliquer des schémas de filtrage à d'autres feuilles); Tri avancé par mois / semaine / jour, fréquence et plus; Filtre spécial par gras, italique ...
  • Combiner des classeurs et des feuilles de travail; Fusionner les tableaux en fonction des colonnes clés; Diviser les données en plusieurs feuilles; Conversion par lots xls, xlsx et PDF...
  • Plus de 300 fonctionnalités puissantes. Prend en charge Office / Excel 2007-2019 et 365. Prend en charge toutes les langues. Déploiement facile dans votre entreprise ou organisation. Essai gratuit de 30 jours. Garantie de remboursement de 60 jours.
onglet kte 201905

Office Tab apporte une interface à onglets à Office et simplifie considérablement votre travail

  • Activer l'édition et la lecture par onglets dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
  • Ouvrez et créez plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
  • Augmente votre productivité de 50% et réduit des centaines de clics de souris chaque jour!
bas de cabine
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    pi · 2 months ago
    Thanks a lot! Your VBA code is really super
  • To post as a guest, your comment is unpublished.
    Iulia · 2 months ago
    Is it possible to import data from a word to excel on colors algorythme? So, I spell the cities with red and countries with blue in a word, and the to import only these to excel. I don’t know if I made myself clear. Thanks
  • To post as a guest, your comment is unpublished.
    David Wilkinson · 11 months ago
    Thank you, this has saved me literally days of work.
  • To post as a guest, your comment is unpublished.
    Gomez · 1 years ago
    Hello,


    For the following code it shows error in
    MkDir (ActiveWorkbook.Path & "\" & Rng(r, c))

    It says Runtime error 76 path not found

    Can someone please help me with this?

    There are no unsupported characters in the file path.
    Not sure what could be the problem

    Thanks for the help!


  • To post as a guest, your comment is unpublished.
    deep · 1 years ago
    thank you , time saved
  • To post as a guest, your comment is unpublished.
    Kv7 · 1 years ago
    Thanks you. Tried the first method. Works perfectly.
  • To post as a guest, your comment is unpublished.
    KC · 1 years ago
    Hi,
    For the following code it shows error in
    MkDir (ActiveWorkbook.Path & "\" & Rng(r, c))

    It says Runtime error 76 path not found

    Can someone please help me with this?

    • To post as a guest, your comment is unpublished.
      Kv7 · 1 years ago
      Hi, make sure the folder name doesn't contain unsupported characters.
  • To post as a guest, your comment is unpublished.
    Dave · 1 years ago
    Could someone edit the VBA code to display a message if the folder already exists and prevent from recreating it:


    Sub MakeFolders()
    Dim Rng As Range
    Dim maxRows, maxCols, r, c As Integer
    Set Rng = Selection
    maxRows = Rng.Rows.Count
    maxCols = Rng.Columns.Count
    For c = 1 To maxCols
    r = 1
    Do While r <= maxRows
    If Len(Dir(ActiveWorkbook.Path & "\" & Rng(r, c), vbDirectory)) = 0 Then
    MkDir (ActiveWorkbook.Path & "\" & Rng(r, c))
    On Error Resume Next
    End If
    r = r + 1
    Loop
    Next c
    End Sub
  • To post as a guest, your comment is unpublished.
    Jerem · 1 years ago
    Hi, I copied the code above, but get an error "Bad file name or number (Error 52)" on this code line:
    If Len(Dir(ThisWorkbook.Path & "\" & Rng(r, c), vbDirectory)) = 0 Then

    Any idea, what the issue could be?
  • To post as a guest, your comment is unpublished.
    arey.qyu@gmail.com · 2 years ago
    GREAT..!!

    Really really help me bro..

    God Bless You All..
  • To post as a guest, your comment is unpublished.
    Martz · 2 years ago
    It works great,Thank you
  • To post as a guest, your comment is unpublished.
    Dakota · 2 years ago
    Website looked a bit sketchy but this worked and saved me a lot of frustration. Thank you!
  • To post as a guest, your comment is unpublished.
    martin · 2 years ago
    it worked exactly as told.
    thanks!
  • To post as a guest, your comment is unpublished.
    stefan kosa · 3 years ago
    I am trying to use this file in a sharepoint folder to create folders. But I get the path error. Is it possible to change the code to work in a sharepoint folder the file is located ?
  • To post as a guest, your comment is unpublished.
    Mike · 3 years ago
    Is it possible to create a folder>subfolder import structure? Allowing me to have all the top level folders and their sub folders import all at once? They will be all the same.
    • To post as a guest, your comment is unpublished.
      Sunny · 3 years ago
      Sorry I do not get your point. Do you mean to create folder and subfolder based on cell values or to import folder and subfolder name in Excel? If the last one, yuo can apply Kutools for Excel's Create Filename List.
  • To post as a guest, your comment is unpublished.
    Jacob · 3 years ago
    Hi All, I'm a super newb, like some of you I don't know how to change the destination of where these files are created. The files are currently being created on my Desktop, is there a way I could change the destination to a SharePoint?
  • To post as a guest, your comment is unpublished.
    Genious fox · 4 years ago
    If you dont have enterprise licence of ms excel then its bit difficult, try the trick in the video and share like or subscribe



    https://youtu.be/1WAQWm7UPBY
  • To post as a guest, your comment is unpublished.
    Nelson Barbisa · 4 years ago
    Thank you very very much! you just made me save like 3 hours of work!
  • To post as a guest, your comment is unpublished.
    Rahul Upadhyay · 4 years ago
    How to make sub-folders also in this VBA?
  • To post as a guest, your comment is unpublished.
    Jw · 4 years ago
    Not working using Excel for Mac (v15.31)... run-time error '53': File not found.
    Is the "\" format not applicable on Mac?

    Bounces error on this line of code:
    If Len(Dir(ActiveWorkbook.Path & "\" & Rng(r, c), vbDirectory)) = 0
    • To post as a guest, your comment is unpublished.
      zach · 3 months ago
      Did you ever get this figured out?
  • To post as a guest, your comment is unpublished.
    Ted · 4 years ago
    Just saved me a few hours of work. I would fax you a beer if I knew your number!!!
  • To post as a guest, your comment is unpublished.
    Randy Marsh · 4 years ago
    Dude, you rock!! Thanks a million
  • To post as a guest, your comment is unpublished.
    Randy Marsh · 4 years ago
    Dude, you are awesome! Thank you ever so much!
  • To post as a guest, your comment is unpublished.
    Randy · 5 years ago
    Worked like a charm, but is there a way to create subfolders?
  • To post as a guest, your comment is unpublished.
    bhuppi · 5 years ago
    hi ,
    i m a newbie
    excel shoe error 52 while debugging this plz help
  • To post as a guest, your comment is unpublished.
    worker · 5 years ago
    Thanks for this. works fine
  • To post as a guest, your comment is unpublished.
    MKontul · 5 years ago
    Cheers! Great function!
  • To post as a guest, your comment is unpublished.
    Manikkavasagan R · 5 years ago
    Thanks a ton Dude,,, its working...
  • To post as a guest, your comment is unpublished.
    Manuel · 5 years ago
    you just saved me a lot of time pal
  • To post as a guest, your comment is unpublished.
    Vinay · 5 years ago
    Thanks a lot for this KUTOOLS & Enterprise tabs ......time saving tool..
  • To post as a guest, your comment is unpublished.
    srikar · 6 years ago
    You are awesome. It worked. Thanks.
    • To post as a guest, your comment is unpublished.
      Sanjeev · 5 years ago
      Nice Job.....Excellent
  • To post as a guest, your comment is unpublished.
    Denny · 6 years ago
    I'm trying to add multiple columns together when creating a folder.

    Any suggestions on how to do this?

    e.g.


    A B C

    1 A1 A2 A3

    2 B1 B2 B3

    3 C1 C2 C3

    End product once folders are created should be:
    Folder 1 (A1 + A2 + A3)
    Folder 2 (B1 + B2 + B3)
    Etc...


    I found multiple formulas on creating folders from single cells but nothing for multiple. I've also tried modifying a few things with no success.

    Any ideas?

    Thanks
  • To post as a guest, your comment is unpublished.
    Vincent · 6 years ago
    Thanks you so much for this helpful post, saved my some time. I love nerds:) What would we do without them, seriously!
  • To post as a guest, your comment is unpublished.
    juliekadas · 7 years ago
    Worked like a charm, but is there a way to create subfolders?
    • To post as a guest, your comment is unpublished.
      Jaakko · 6 years ago
      I'd like to know this too. I nee to make sub folders with given name already added.
      Example:
      Excelnamedfolder\P\E
      I need two folders inside P folder named E and K.
    • To post as a guest, your comment is unpublished.
      ktrazz · 7 years ago
      Trying this on a Mac and I get "Run-time error '68': Device unavailable", and pressing debug points out the following line of code as the culprit:

      If Len(Dir(ActiveWorkbook.Path & "\" & Rng(r, c), vbDirectory)) = 0 Then
      MkDir (ActiveWorkbook.Path & "\" & Rng(r, c))

      Please help.
  • To post as a guest, your comment is unpublished.
    Pete Lewis · 7 years ago
    Thanks for this - Excellent time saver
  • To post as a guest, your comment is unpublished.
    Collin@Cooper · 7 years ago
    For some reason the active workbook is having problems with the path. HELPPPP :cry:
    • To post as a guest, your comment is unpublished.
      VC · 5 years ago
      I had a similar issue , at the end I figured it out that program does not work if the file name have slashes / or \ , I replaces / with _, it worked
  • To post as a guest, your comment is unpublished.
    Collin@Cooper · 7 years ago
    You are a lifesaver!!!!! [b]Thank you![b]

    Note: Don't put a space between MakeFolder and ().
  • To post as a guest, your comment is unpublished.
    Collin@Cooper · 7 years ago
    [b]HELP!!![/b] I Have over 7500 folders to make but the program is having a compiling error highlighting [b]Sub MakeFolders ()[b] as if it is an invalid name or something!
    I'm operating Excel 2010.
    • To post as a guest, your comment is unpublished.
      BJA101 · 7 years ago
      I had the same problem, but when I looked at the folder name closer there was a / and once I removed it, the code worked like a charm.
  • To post as a guest, your comment is unpublished.
    Yogesh · 7 years ago
    How to do this in LibreOffice or OpenOffice

    Please reply.
  • To post as a guest, your comment is unpublished.
    Charan · 7 years ago
    :-) Thanks for providing this data.... saved my lot of time
  • To post as a guest, your comment is unpublished.
    mike69 · 7 years ago
    Thanks a lot!!! This is great and most effective and easiest solution I've found on the internet for this! :lol:
  • To post as a guest, your comment is unpublished.
    SASAR · 7 years ago
    Thanks this save me a lot of time. But how to create sub folders?
  • To post as a guest, your comment is unpublished.
    karthiha devi · 7 years ago
    Good... :) Thank you.. It makes me to work simple.
  • To post as a guest, your comment is unpublished.
    Ahmed ElGammal · 7 years ago
    please, i need that same macro but instead of saving them as folders, i need it to save notepads with only the cell names in a column range, appreciate the support.
  • To post as a guest, your comment is unpublished.
    Jozef · 8 years ago
    What is the fault in following VBA instruction?

    Do While r
    • To post as a guest, your comment is unpublished.
      skyyang · 8 years ago
      Thanks for your reply, the VBA code have little questions when I edit it, so here I have modified it, and it applys successfully, please try it. Good luck!
      • To post as a guest, your comment is unpublished.
        HappyTang · 5 years ago
        SkyYang, [quote name="skyyang"]Thanks for your reply, the VBA code have little questions when I edit it, so here I have modified it, and it applys successfully, please try it. Good luck![/quote]
        YOU ARE AWESOME !! I was able to be more productive at work because of the program (VBA code in Developer).
        • To post as a guest, your comment is unpublished.
          Mel · 11 months ago
          Who ever wrote the first example - You are awesome!!