提示:其他語言是Google翻譯的。 你可以訪問 English 版本。
登入
x
or
x
x
註冊
x

or

如何根據Excel中的單元格值快速創建文件夾?

假設您在工作表範圍內有工作人員姓名列表,現在您想為單元格中的每個人創建一些文件夾來記錄他們的信息。 如果您逐個創建文件夾,將浪費大量時間。 但是,你怎麼能夠快速創建這些文件夾? 今天,我會向你介紹一些快速技巧:

使用VBA代碼基於單元格值創建文件夾

使用Kutools for Excel快速創建基於單元格值的文件夾好idea3

使用Excel中的超鏈接快速列出所有工作表名稱

Kutools for Excel's 創建圖紙名稱列表 實用程序可以快速創建如下圖所示的可點擊表單名稱列表。 單擊可獲得全功能的30天免費試用!
文檔列表表單名稱
Kutools for Excel:擁有超過300個方便的Excel加載項,可以在30天內免費試用,沒有限制。

使用VBA代碼基於單元格值創建文件夾

選項卡式瀏覽和編輯多個Excel工作簿/ Word文檔,如Firefox,Chrome,Internet瀏覽10!

您可能熟悉在Firefox / Chrome / IE中查看多個網頁,並通過輕鬆單擊相應的選項卡在它們之間切換。 此處,Office選項卡支持類似的處理,允許您在一個Excel窗口或Word窗口中瀏覽多個Excel工作簿或Word文檔,並通過單擊其選項卡輕鬆切換它們。 單擊免費獲得Office Tab的30天試用!

擅長

例如,我在工作表中有一系列名稱,我想為它們中的每一個創建文件夾,並使用VBA代碼將它們保存到指定的路徑,我可以完成此任務。

1。 將活動工作簿放入一個特定的目錄中,您將放置創建的文件夾。 打開工作簿並選擇您要使用的單元格範圍。

文檔創建,folders1

2。 點擊 開發人員 > Visual Basic中,一個新的 Microsoft Visual Basic的應用程序 窗口將會顯示,點擊 插入 > 模塊,並將以下代碼輸入到模塊中:

VBA代碼:基於單元格值創建文件夾

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。 點擊 DOC-乘法計算-3 按鈕來執行代碼。 所有選定的單元格都已創建到具有其值的文件夾中。 這些文件夾與活動工作簿一樣放置在路徑中。 看截圖:

文檔創建,folders2


使用Kutools for Excel快速創建基於單元格值的文件夾

如果您使用Kutools for Excel工具,則基於單元格值創建文件夾非常簡單方便。

Kutools for Excel, 與以上 300 方便的功能,讓您的工作更輕鬆。

安裝後 Kutools for Excel,請按照以下步驟操作:(免費下載Kutools for Excel!)

1。 選擇您想要創建文件夾的範圍。

2。 點擊 Kutools Plus > 導入/導出 > 從單元格內容創建文件夾...,看截圖:

doc創建文件夾1

3。 在 從單元格內容創建文件夾 對話框,單擊 文檔創建,folders3按鈕指定要保存文件夾的路徑。 截圖:

doc創建文件夾2

4。 點擊 OK。 提示框會提醒你已經創建了多少個文件夾。 看截圖:

doc創建文件夾3

5。 點擊 OK。 並且所選範圍中的所有值都已經在指定文件夾中創建了文件夾。
doc創建文件夾4


列出文件夾中的所有文件名稱

了解更多關於這個功能。

這裡是Excel的Kutools實用程序 - 文件名列表 可以列出表格中文件夾的所有文件名,如果您對此感興趣,請繼續閱讀。

安裝後 Kutools for Excel,請按照以下步驟操作:(免費下載Kutools for Excel!)

1。 點擊 Kutools Plus > 進出口 > 文件名列表.
doc創建文件夾5

2。 在裡面 文件名列表 對話框中,選擇一個要列出文件的文件夾,檢查 所有文件 or 指定 您需要的選項 文件類型為s撓度。
img src =“https://cdn.extendoffice.com/images/stories/doc-excel/create-folders/doc-create-folder-6.png”alt =“doc create folder 6”/>

3。 點擊 Ok,會創建一個包含所有文件名和一些相關信息的新工作表。
doc創建文件夾7


Kutools for Excel解決了您的大多數問題,並使您的生產率提高了80%

  • 重用: 快速插入 複雜的公式,圖表 以及你以前用過的任何東西; 加密單元格 密碼; 創建郵件列表 並發送電子郵件...
  • 超級方程式酒吧 (輕鬆編輯多行文字和公式); 閱讀佈局 (輕鬆讀取和編輯大量單元格); 粘貼到過濾範圍...
  • 合併單元格/行/列 不丟失數據; 分裂細胞含量; 組合重複的行/列...防止重複的細胞; 比較範圍...
  • 選擇複製或唯一 行; 選擇空行 (所有細胞都是空的); 超級查找和模糊查找 在許多工作簿中; 隨機選擇......
  • 精確複製 多個單元格而不更改公式參考; 自動創建參考 多張表; 插入項目符號,複選框等等......
  • 提取文本,添加文本,按位置刪除, 刪除空間; 創建和打印分頁小計; 在單元格內容和註釋之間轉換...
  • 超級過濾器 (將過濾方案保存並應用到其他工作表); 高級排序 按月/週/日,頻率等; 特殊過濾器 用粗體,斜體......
  • 結合工作簿和工作表; 根據鍵列合併表; 將數據拆分為多個表格; 批量轉換xls,xlsx和PDF...
  • 超過300強大的功能。 支持Office / Excel 2007-2019和365。 支持所有語言。 在您的企業或組織中輕鬆部署。 全功能30天免費試用。
kte tab 201905

Office選項卡為Office提供選項卡式界面,使您的工作更輕鬆

  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,Publisher,Access,Visio和Project。
  • 在同一窗口的新選項卡中打開並創建多個文檔,而不是在新窗口中。
  • 通過50%提高您的工作效率,每天為您減少數百次鼠標點擊!
官方底部
Say something here...
symbols left.
You are guest ( Sign Up? )
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.
    Adelino · 4 months ago
    It works good with english characters; but how about other characters like cyrillic? Thank you in advance.
  • To post as a guest, your comment is unpublished.
    A. Qusyairi · 6 months ago
    GREAT..!!

    Really really help me bro..

    God Bless You All..
  • To post as a guest, your comment is unpublished.
    Martz · 11 months ago
    It works great,Thank you
  • To post as a guest, your comment is unpublished.
    Dakota · 11 months 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 · 11 months ago
    it worked exactly as told.
    thanks!
  • To post as a guest, your comment is unpublished.
    stefan kosa · 1 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 · 1 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 · 1 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 · 1 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 · 2 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 · 2 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 · 2 years ago
    How to make sub-folders also in this VBA?
  • To post as a guest, your comment is unpublished.
    Jw · 2 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.
    Ted · 2 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 · 3 years ago
    Dude, you rock!! Thanks a million
  • To post as a guest, your comment is unpublished.
    Randy Marsh · 3 years ago
    Dude, you are awesome! Thank you ever so much!
  • To post as a guest, your comment is unpublished.
    Randy · 3 years ago
    Worked like a charm, but is there a way to create subfolders?
  • To post as a guest, your comment is unpublished.
    bhuppi · 3 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 · 3 years ago
    Thanks for this. works fine
  • To post as a guest, your comment is unpublished.
    MKontul · 3 years ago
    Cheers! Great function!
  • To post as a guest, your comment is unpublished.
    Manikkavasagan R · 3 years ago
    Thanks a ton Dude,,, its working...
  • To post as a guest, your comment is unpublished.
    Manuel · 3 years ago
    you just saved me a lot of time pal
  • To post as a guest, your comment is unpublished.
    Vinay · 3 years ago
    Thanks a lot for this KUTOOLS & Enterprise tabs ......time saving tool..
  • To post as a guest, your comment is unpublished.
    srikar · 4 years ago
    You are awesome. It worked. Thanks.
  • To post as a guest, your comment is unpublished.
    Denny · 4 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 · 4 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 · 5 years ago
    Worked like a charm, but is there a way to create subfolders?
    • To post as a guest, your comment is unpublished.
      Jaakko · 4 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 · 5 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 · 5 years ago
    Thanks for this - Excellent time saver
  • To post as a guest, your comment is unpublished.
    Collin@Cooper · 5 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 · 3 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 · 5 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 · 5 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 · 5 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 · 5 years ago
    How to do this in LibreOffice or OpenOffice

    Please reply.
  • To post as a guest, your comment is unpublished.
    Charan · 5 years ago
    :-) Thanks for providing this data.... saved my lot of time
  • To post as a guest, your comment is unpublished.
    mike69 · 5 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 · 5 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 · 5 years ago
    Good... :) Thank you.. It makes me to work simple.
  • To post as a guest, your comment is unpublished.
    Ahmed ElGammal · 5 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 · 6 years ago
    What is the fault in following VBA instruction?

    Do While r
    • To post as a guest, your comment is unpublished.
      skyyang · 6 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 · 3 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).