Skip i'r prif gynnwys

Sut i restru'r holl ffolderau ac is-ffolderi yn Excel?

Awdur: Xiaoyang Wedi'i Addasu Diwethaf: 2014-11-05

A ydych erioed wedi dioddef gyda'r broblem hon sy'n rhestru'r holl ffolderau ac is-ffolderi o gyfeiriadur penodol i mewn i daflen waith? Yn Excel, nid oes ffordd gyflym a defnyddiol o gael enw'r holl ffolderau mewn cyfeiriadur penodol ar unwaith. I ddelio â'r dasg, gall yr erthygl hon eich helpu chi.

Rhestrwch bob ffolder ac is-ffolder gyda chod VBA

swigen dde glas saeth Rhestrwch bob ffolder ac is-ffolder gyda chod VBA

Os ydych chi am gael yr holl enwau ffolderi o gyfeiriadur penodol, gall y cod VBA canlynol eich helpu chi, gwnewch fel hyn:

1. Daliwch i lawr y ALT + F11 allweddi, ac mae'n agor y Ffenestr Microsoft Visual Basic for Applications.

2. Cliciwch Mewnosod > Modiwlau, a gludwch y cod canlynol i'r Ffenestr Modiwl.

Cod VBA: Rhestrwch yr holl ffolderau ac enwau is-ffolderi

Sub FolderNames()
'Update 20141027
Application.ScreenUpdating = False
Dim xPath As String
Dim xWs As Worksheet
Dim fso As Object, j As Long, folder1 As Object
With Application.FileDialog(msoFileDialogFolderPicker)
    .Title = "Choose the folder"
    .Show
End With
On Error Resume Next
xPath = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) & "\"
Application.Workbooks.Add
Set xWs = Application.ActiveSheet
xWs.Cells(1, 1).Value = xPath
xWs.Cells(2, 1).Resize(1, 5).Value = Array("Path", "Dir", "Name", "Date Created", "Date Last Modified")
Set fso = CreateObject("Scripting.FileSystemObject")
Set folder1 = fso.getFolder(xPath)
getSubFolder folder1
xWs.Cells(2, 1).Resize(1, 5).Interior.Color = 65535
xWs.Cells(2, 1).Resize(1, 5).EntireColumn.AutoFit
Application.ScreenUpdating = True
End Sub
Sub getSubFolder(ByRef prntfld As Object)
Dim SubFolder As Object
Dim subfld As Object
Dim xRow As Long
For Each SubFolder In prntfld.SubFolders
    xRow = Range("A1").End(xlDown).Row + 1
    Cells(xRow, 1).Resize(1, 5).Value = Array(SubFolder.Path, Left(SubFolder.Path, InStrRev(SubFolder.Path, "\")), SubFolder.Name, SubFolder.DateCreated, SubFolder.DateLastModified)
Next SubFolder
For Each subfld In prntfld.SubFolders
    getSubFolder subfld
Next subfld
End Sub

3. Yna pwyswch F5 allwedd i redeg y cod hwn, ac a Dewiswch y ffolder bydd y ffenestr yn popio allan, yna mae angen i chi ddewis y cyfeiriadur rydych chi am restru'r ffolder ac enwau is-ffolder, gweler y screenshot:

doc-list-folder-names-1

4. Cliciwch OK, a byddwch yn cael y ffolder a'r llwybr is-ffolderi, cyfeiriadur, enw, dyddiad wedi'i greu a'r dyddiad a addaswyd ddiwethaf mewn llyfr gwaith newydd, gweler y screenshot:

doc-list-folder-names-1

Erthygl gysylltiedig:

Sut i restru ffeiliau mewn cyfeiriadur i daflen waith yn Excel?

Offer Cynhyrchiant Swyddfa Gorau

🤖 Kutools AI Aide: Chwyldro dadansoddi data yn seiliedig ar: Cyflawniad Deallus   |  Cynhyrchu Cod  |  Creu Fformiwlâu Personol  |  Dadansoddi Data a Chynhyrchu Siartiau  |  Invoke Swyddogaethau Kutools...
Nodweddion Poblogaidd: Darganfod, Amlygu neu Adnabod Dyblygiadau   |  Dileu Rhesi Gwag   |  Cyfuno Colofnau neu Gelloedd heb Colli Data   |   Rownd heb Fformiwla ...
Super-edrych: VLookup Meini Prawf Lluosog  |   VLookup Gwerth Lluosog  |   VLookup Ar Draws Taflenni Lluosog   |   Edrych Niwlog ....
Rhestr gwympo Uwch: Creu Rhestr Gollwng yn Gyflym   |  Rhestr Gollwng Dibynnol   |  Rhestr Gollwng Aml-ddewis ....
Rheolwr Colofn: Ychwanegu Nifer Penodol o Golofnau  |  Symud Colofnau  |  Toglo Statws Gwelededd Colofnau Cudd  |  Cymharwch Ystodau a Cholofnau ...
Nodweddion dan Sylw: Ffocws ar y Grid   |  Golwg Dylunio   |   Bar Fformiwla Mawr   |  Rheolwr Llyfr Gwaith a Thaflen   |  Llyfrgell Adnoddau (Testun Auto)   |  Dewiswr Dyddiad   |  Cyfuno Taflenni Gwaith   |  Amgryptio/Dadgryptio Celloedd   |  Anfon E-byst trwy Restr   |  Hidlo Super   |   Hidlo Arbennig (hidlo mewn print trwm/italig/strikethrough...) ...
15 Set Offer Gorau:  12 Testun offer (Ychwanegu Testun, Dileu Cymeriadau,...)   |   50 + Siart Mathau (Siart Gantt,...)   |   40+ Ymarferol Fformiwlâu (Cyfrifwch oedran yn seiliedig ar ben-blwydd,...)   |   19 mewnosod offer (Mewnosod Cod QR, Mewnosod Llun o'r Llwybr,...)   |   12 Trosi offer (Rhifau i Eiriau, Trosi arian cyfred,...)   |   7 Uno a Hollti offer (Rhesi Cyfuno Uwch, Celloedd Hollt,...)   |   ... a mwy

Supercharge Eich Sgiliau Excel gyda Kutools ar gyfer Excel, a Phrofiad Effeithlonrwydd Fel Erioed Erioed. Kutools ar gyfer Excel Yn Cynnig Dros 300 o Nodweddion Uwch i Hybu Cynhyrchiant ac Arbed Amser.  Cliciwch Yma i Gael Y Nodwedd Sydd Ei Angen Y Mwyaf...

Disgrifiad

Mae Office Tab yn dod â rhyngwyneb Tabbed i Office, ac yn Gwneud Eich Gwaith yn Haws o lawer

  • Galluogi golygu a darllen tabbed yn Word, Excel, PowerPoint, Cyhoeddwr, Mynediad, Visio a Phrosiect.
  • Agor a chreu dogfennau lluosog mewn tabiau newydd o'r un ffenestr, yn hytrach nag mewn ffenestri newydd.
  • Yn cynyddu eich cynhyrchiant 50%, ac yn lleihau cannoedd o gliciau llygoden i chi bob dydd!
Comments (19)
No ratings yet. Be the first to rate!
OriginaLew
OriginaLew
This comment was minimized by the moderator on the site
Hello, I don't want or need it to show all that information nor the subfolders nor the filenames & I especially don't want it to create a new file altogether! What I need is simply just the top level folder names only in a non changing directory. Because unfortunately people at my work don't comprehend the need for uniformity in naming folders & also they move folders into an old job folder ruining any possible easy excel function links.
Example:
C:\temp\
>folder1
>folder2
>folder3
Etc. Etc. Etc...
Thats all I need.
The Way that apparently everyone on the internet wants; darn near crashes excel &/or my work PC trying to list every bit of over 15,000 instances of every single unwanted file & subfolder for this query.
There's only about 40 Give-Or-Take folders w/in the directory.
I already have a way of determining if files are w/in as example folder2 or not.
I am trying to automate this away from my current solution which was creating a folder w/ a text file for each, named the exact name of each folder. But if someone adds moves renames or deletes a folder this no longer fully works. AND I absolutely do not want to use command prompt.
I want & need either an excel function or vba option that I can use in conjunction w/ other excel functions i have &/or create w/in the same document (different sheets are fine).
Reply
0
0
altaee1984
altaee1984
This comment was minimized by the moderator on the site
Great work... It's very helpful for me, thanks very much
Reply
Report
0
0
Tim_Cooprider
Tim_Cooprider
This comment was minimized by the moderator on the site
Thank you, this is exactly what we needed to record the folders for our clients.
Reply
Report
0
0
Ezel K.
Ezel K.
This comment was minimized by the moderator on the site
Thank you very much for this usefull code. is that possible save result in same workbook not to new one ?
Reply
0
0
D Rodrigues
D Rodrigues
This comment was minimized by the moderator on the site
This worked and how... Thank you so much. Just one addition-- at Step 3, I had to click on Step Into and then only did the F5 key work for choosing the folder.
Reply
0
0
José
José
This comment was minimized by the moderator on the site
Thank you so much!, very useful.
Reply
0
0
David
David
This comment was minimized by the moderator on the site
Just what I needed, and perfectly clear instructions on how to make it work. Thanks a lot
Reply
0
0
jose
jose
This comment was minimized by the moderator on the site
Great Tool! After long research found this accurate toy :)
Reply
0
0
lloyd
lloyd
This comment was minimized by the moderator on the site
Hello. Can you please please help me on a code which I am struggling to find.

Below are the requirements for the code.



1. The VBA should go through all the folders and sub-folders
and check each and every type of file. The user should only give the path for
the top folder. The code should then check all the folders and sub folders
within the top folder.



2. After checking the files, the code should zip all files
which have not been accessed for more than 3 months. The accessed period is
something which I should be able to change in future if required. It should
allow me to change it to 1 month or 5 months if required.



3. After zipping the files, the code should delete the
original files which were zipped.



4. The zipped file should be saved in the same path as the
original file.
Reply
0
0
tom vincent
tom vincent
This comment was minimized by the moderator on the site
I modified it to add size:



Sub FolderNames()
'Update 20141027
Application.ScreenUpdating = False
Dim xPath As String
Dim xWs As Worksheet
Dim fso As Object, j As Long, folder1 As Object
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Choose the folder"
.Show
End With
On Error Resume Next
xPath = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) & "\"
Application.Workbooks.Add
Set xWs = Application.ActiveSheet
xWs.Cells(1, 1).Value = xPath
xWs.Cells(2, 1).Resize(1, 6).Value = Array("Path", "Dir", "Name", "Date Created", "Date Last Modified","Size")
Set fso = CreateObject("Scripting.FileSystemObject")
Set folder1 = fso.getFolder(xPath)
getSubFolder folder1
xWs.Cells(2, 1).Resize(1, 6).Interior.Color = 65535
xWs.Cells(2, 1).Resize(1, 6).EntireColumn.AutoFit
Application.ScreenUpdating = True
End Sub
Sub getSubFolder(ByRef prntfld As Object)
Dim SubFolder As Object
Dim subfld As Object
Dim xRow As Long
For Each SubFolder In prntfld.SubFolders
xRow = Range("A1").End(xlDown).Row + 1
Cells(xRow, 1).Resize(1, 6).Value = Array(SubFolder.Path, Left(SubFolder.Path, InStrRev(SubFolder.Path, "\")), SubFolder.Name, SubFolder.DateCreated, SubFolder.DateLastModified, SubFolder.Size)
Next SubFolder
For Each subfld In prntfld.SubFolders
getSubFolder subfld
Next subfld
End Sub
Reply
0
0
AL
AL     tom vincent
This comment was minimized by the moderator on the site
When you include the SubFolder.Size function the script no longer list all the subfolders, only the first level.
How can I include the size and get all subfolders listed?
Reply
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations