Preskoči na glavno vsebino

Kako našteti vse mape in podmape v Excelu?

Ste že kdaj trpeli zaradi te težave, ki vsebuje vse mape in podmape iz določenega imenika na delovni list? V Excelu ni na hiter in priročen način, da naenkrat dobite ime vseh map v določenem imeniku. Ta članek vam bo morda v pomoč pri reševanju naloge.

Seznam vseh map in podmap s kodo VBA


puščica modri desni mehurček Seznam vseh map in podmap s kodo VBA

Če želite iz določenega imenika dobiti vsa imena map, vam bo morda v pomoč naslednja koda VBA, naredite naslednje:

1. Držite tipko ALT + F11 in odpre tipko Okno Microsoft Visual Basic for Applications.

2. Kliknite Vstavi > Moduliin prilepite naslednjo kodo v Okno modula.

Koda VBA: navedite imena vseh map in podmap

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. Nato pritisnite F5 tipko za zagon te kode in a Izberite mapo se bo odprlo okno, nato morate izbrati imenik, v katerega želite navesti imena map in podmap, glejte posnetek zaslona:

doc-list-ime-mape-1

4. Kliknite OK, pot do mape in podmap, imenik, ime, datum ustvarjanja in datum zadnje spremembe pa boste dobili v novem delovnem zvezku, glejte posnetek zaslona:

doc-list-ime-mape-1


Povezani članek:

Kako našteti datoteke v imeniku na delovnem listu v Excelu?

Najboljša pisarniška orodja za produktivnost

Priljubljene funkcije: Poiščite, označite ali identificirajte dvojnike   |  Izbriši prazne vrstice   |  Združite stolpce ali celice brez izgube podatkov   |   Krog brez formule ...
Super iskanje: Več kriterijev VLookup    Multiple Value VLookup  |   VLookup na več listih   |   Nejasno iskanje ....
Napredni spustni seznam: Hitro ustvarite spustni seznam   |  Odvisni spustni seznam   |  Večkrat izberite spustni seznam ....
Upravitelj stolpcev: Dodajte določeno število stolpcev  |  Premakni stolpce  |  Preklop stanja vidnosti skritih stolpcev  |  Primerjaj obsege in stolpce ...
Predstavljene funkcije: Mrežni fokus   |  Pogled oblikovanja   |   Velika vrstica formule    Upravitelj delovnih zvezkov in listov   |  Knjižnica virov (Samodejno besedilo)   |  Izbirnik datuma   |  Združite delovne liste   |  Šifriranje/dešifriranje celic    Pošljite e-pošto po seznamu   |  Super filter   |   Poseben filter (filter krepko/ležeče/prečrtano ...) ...
15 najboljših kompletov orodij12 Besedilo Orodja (dodajanje besedila, Odstrani znake,...)   |   50 + Graf Vrste (Gantt Chart,...)   |   40+ Praktično Formule (Izračunajte starost glede na rojstni dan,...)   |   19 vstavljanje Orodja (Vstavite kodo QR, Vstavi sliko s poti,...)   |   12 Pretvorba Orodja (Številke v besede, Pretvorba valut,...)   |   7 Spoji in razdeli Orodja (Napredne kombinirane vrstice, Razdeljene celice,...)   |   ... in več

Napolnite svoje Excelove spretnosti s Kutools za Excel in izkusite učinkovitost kot še nikoli prej. Kutools za Excel ponuja več kot 300 naprednih funkcij za povečanje produktivnosti in prihranek časa.  Kliknite tukaj, če želite pridobiti funkcijo, ki jo najbolj potrebujete...

zavihek kte 201905


Kartica Office prinaša vmesnik z zavihki v Office in poenostavi vaše delo

  • Omogočite urejanje in branje z zavihki v Wordu, Excelu, PowerPointu, Publisher, Access, Visio in Project.
  • Odprite in ustvarite več dokumentov v novih zavihkih istega okna in ne v novih oknih.
  • Poveča vašo produktivnost za 50%in vsak dan zmanjša na stotine klikov miške za vas!
Comments (19)
No ratings yet. Be the first to rate!
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).
This comment was minimized by the moderator on the site
Great work... It's very helpful for me, thanks very much
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.
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 ?
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.
This comment was minimized by the moderator on the site
Thank you so much!, very useful.
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
This comment was minimized by the moderator on the site
Great Tool! After long research found this accurate toy :)
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.
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
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?
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