跳到主要内容

如何基于Excel中的单元格值快速创建文件夹?

假设您在工作表范围内有一个职员名称列表,现在您要在单元格中为每个职员创建一些文件夹以记录其信息。 如果您一个接一个地创建文件夹,将浪费大量时间。 但是如何快速创建这些文件夹? 今天,我将向您介绍一些快速技巧:

使用VBA代码根据单元格值创建文件夹

使用 Kutools for Excel 根据单元格值快速创建文件夹好主意3


使用VBA代码根据单元格值创建文件夹

例如,我在工作表中有一系列名称,我想为每个文件夹创建文件夹,并将它们保存到指定的路径,使用VBA代码,我可以完成此任务。

1。 将活动工作簿放入特定目录,您将在其中放置创建的文件夹。 打开工作簿,然后选择要使用的单元格范围。

文档创建文件夹 1

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。 点击 文档乘法计算 3 按钮执行代码。 所有选定的单元格及其值都已创建到文件夹中。 并将文件夹放置在与活动工作簿相同的路径中。 看截图:

文档创建文件夹 2


使用 Kutools for Excel 根据单元格值快速创建文件夹

如果您使用工具Kutools for Excel,您可以轻松方便地创建基于单元格值的文件夹。

Kutools for Excel, 与超过 300 方便的功能,使您的工作更加轻松。 

安装后 Kutools for Excel,请执行以下操作:(立即免费下载Kutools for Excel!)

1。 选择您要创建文件夹的范围。

2。 点击 Kutools 加 > 导入/导出 > 从单元格内容创建文件夹…,请参见屏幕截图:

doc建立资料夹1

3。 在 从单元格内容创建文件夹 对话框,单击 文档创建文件夹 3按钮指定您要保存文件夹的路径。 看到的屏幕截图:

doc建立资料夹2

4。 点击 OK。 一个提示框将提醒您已创建了多少个文件夹。 看截图:

doc建立资料夹3

5。 点击 OK。 所选范围内的所有值均已在指定文件夹中创建。
doc建立资料夹4


将文件夹中的所有文件名列出到工作表中

进一步了解此功能。

这是 Kutools for Excel 中的一个实用程序 – 文件名列表 可以在工作表中列出该文件夹的所有文件名,如果对此感兴趣,请继续阅读。

安装后 Kutools for Excel,请执行以下操作:(立即免费下载Kutools for Excel!)

1。 点击 Kutools 加 > 进出口 > 文件名列表.
doc建立资料夹5

2.在 文件名列表 对话框中,选择要列出其文件的文件夹,然后选中 所有文件 or 指定 您需要的选项 文件类型s选择。
img src="//cdn.extendoffice.com / images / stories / doc-excel / create-folders / doc-create-folder-6.png“ alt =” doc创建文件夹6“ />

3。 点击 Ok,将创建一个包含所有文件名和一些相关信息的新工作表。
doc建立资料夹7

最佳办公生产力工具

热门特色: 查找、突出显示或识别重复项   |  删除空白行   |  合并列或单元格而不丢失数据   |   不使用公式进行四舍五入 ...
超级查询: 多条件VLookup    多值VLookup  |   跨多个工作表的 VLookup   |   模糊查询 ....
高级下拉列表: 快速创建下拉列表   |  依赖下拉列表   |  多选下拉列表 ....
列管理器: 添加特定数量的列  |  移动列  |  切换隐藏列的可见性状态  |  比较范围和列 ...
特色功能: 网格焦点   |  设计图   |   大方程式酒吧    工作簿和工作表管理器   |  资源库 (自动文本)   |  日期选择器   |  合并工作表   |  加密/解密单元格    按列表发送电子邮件   |  超级筛选   |   特殊过滤器 (过滤粗体/斜体/删除线...)...
前 15 个工具集12 文本 工具 (添加文本, 删除字符,...)   |   50+ 图表 类型 (甘特图,...)   |   40+ 实用 公式 (根据生日计算年龄,...)   |   19 插入 工具 (插入二维码, 从路径插入图片,...)   |   12 转化 工具 (小写金额转大写, 货币兑换,...)   |   7 合并与拆分 工具 (高级组合行, 分裂细胞,...)   |   ... 和更多

使用 Kutools for Excel 增强您的 Excel 技能,体验前所未有的效率。 Kutools for Excel 提供了 300 多种高级功能来提高生产力并节省时间。  单击此处获取您最需要的功能...

kte选项卡201905


Office Tab 为 Office 带来选项卡式界面,让您的工作更加轻松

  • 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,发布者,Access,Visio和Project。
  • 在同一窗口的新选项卡中而不是在新窗口中打开并创建多个文档。
  • 每天将您的工作效率提高50%,并减少数百次鼠标单击!
Comments (62)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
This worked really well, even for someone with zero experience with VBA :-)
Would it be possible to adapt the macro or extend the macro to also create hyperlinks to the folders in the selected cells?
So for instance, Cell A3 is selected and you run the macro and the folder is created. Would it be possible to make cell A3 a hyperlink to the folder by expanding on the macro instead of doing that manually?
This comment was minimized by the moderator on the site
Hello, Marloes
To create hyperlinks for the cell values, the following vba code may help you:

First, please select the cell values, and then run this code, and select a folder for outputting the folders.

Sub MakeFoldersAndAddHyperlinksWithFolderSelection()
    Dim Rng As Range
    Dim maxRows, maxCols, r, c As Integer
    Dim folderPath As String
    Dim baseFolderPath As String
    Dim fd As FileDialog
    Set fd = Application.FileDialog(msoFileDialogFolderPicker)
    
    With fd
        If .Show = -1 Then
            baseFolderPath = .SelectedItems(1) & "\"
        Else
            MsgBox "No folder selected. Operation Cancelled."
            Exit Sub
        End If
    End With
    
    Set Rng = Selection
    maxRows = Rng.Rows.Count
    maxCols = Rng.Columns.Count
    
    For c = 1 To maxCols
        For r = 1 To maxRows
            folderPath = baseFolderPath & Rng.Cells(r, c).Value
            If Len(Dir(folderPath, vbDirectory)) = 0 Then
                MkDir folderPath
                On Error Resume Next
                ActiveSheet.Hyperlinks.Add Anchor:=Rng.Cells(r, c), Address:=folderPath, TextToDisplay:=Rng.Cells(r, c).Value
                On Error GoTo 0
            End If
        Next r
    Next c
End Sub


Please have a try, thank you!
This comment was minimized by the moderator on the site
please, i need that same macro but instead of saving them as folders, i need it to save as Excels.
This comment was minimized by the moderator on the site
is it possible to introduce a condition where if that condition is met the module can create 2 folders (each using a different path)?
if the first list of folders is in the A column then the condition occurs in the U column. The conditional criteria is whether the cell is empty or not.
if the condition is not met the module only makes one folder based on the selection.
This comment was minimized by the moderator on the site
Hi, a_c, sorry I have not found a method can solve this job yet.
This comment was minimized by the moderator on the site
Thank you very much
This comment was minimized by the moderator on the site
Thanks a lot! Your VBA code is really super
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
Thank you, this has saved me literally days of work.
This comment was minimized by the moderator on the site
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!
This comment was minimized by the moderator on the site
thank you , time saved
This comment was minimized by the moderator on the site
Thanks you. Tried the first method. Works perfectly.
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations