Namig: drugi jeziki so prevedeni v Google. Lahko obiščete English različico te povezave.
Vpiši se
x
or
x
x
Registracija
x

or

Kako v seznamu Excel najdete vse mape in podmape?

Ali ste kdaj imeli težave s to težavo, na kateri so navedene vse mape in podmape iz določenega imenika v delovnem listu? V Excelu ni nobenega hitrega in priročnega načina za takojšnje ime vseh map v določenem imeniku. Za reševanje te naloge vam lahko ta članek pomaga.

Navedite vse mape in podmape s kodo VBA


puščica modra desno mehurček Navedite vse mape in podmape s kodo VBA


Če želite poiskati vsa imena map iz določenega imenika, vam lahko pomagajo naslednja koda VBA:

1. Drži dol ALT + F11 tipke in odpre Okno Microsoft Visual Basic za aplikacije.

2. Kliknite Vstavi > Moduli, in prilepite naslednjo kodo v Okno modula.

VBA koda: seznam vseh imen map in podmape

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 ključ za zagon te kode in a Izberite mapo se bo pojavilo okno, potem morate izbrati imenik, v katerega želite navesti imena mape in podmape, glejte sliko zaslona:

doc-list-map-names-1

4. Kliknite OK, v novo delovno knjigo pa dobite mapo in podmape pot, imenik, ime, datum ustvaritve in zadnji spremenjeni datum, si oglejte posnetek zaslona:

doc-list-map-names-1


Povezani članek:

Kako naj seznam datotek v imeniku dodam v delovni list v Excelu?


Priporočena orodja za produktivnost za Excel

zavihek kte 201905

Kutools za Excel vam pomaga, da vedno končate delo pred časom in izstopite iz množice

  • Več kot zmogljive napredne funkcije 300, zasnovane za 1500 delovne scenarije, ki povečujejo produktivnost z 70%, vam dajejo več časa za skrb za družino in uživanje v življenju.
  • Ne potrebujete več pomnilniških formul in VBA kod, od zdaj naprej pa dajate svojim možganom počitek.
  • Postanite strokovnjak za Excel v minutah 3, zapletene in ponavljajoče se operacije lahko opravite v nekaj sekundah,
  • Vsak dan zmanjšajte število operacij tipkovnice in miške, zdaj se poslovite od poklicnih bolezni.
  • 110,000 visoko učinkovite ljudi in 300 + svetovno priznanih podjetij izbiro.
  • Brezplačna preizkusna različica 60 dneva. 60-dnevno jamstvo vračila denarja. 2 let brezplačne nadgradnje in podpore.

Prinaša kartično brskanje in urejanje za Microsoft Office, veliko močnejši od zavihkov brskalnika

  • Office Tab je namenjen za Word, Excel, PowerPoint in druge Office aplikacije: Založnik, Dostop, Visio in Projekt.
  • Odprite in ustvarite več dokumentov v novih zavihkih istega okna in ne v novih oknih.
  • Z 50% poveča vašo produktivnost in vsak dan zmanjša na stotine klikov z miško!
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.
    jose · 1 months ago
    Great Tool! After long research found this accurate toy :)
  • To post as a guest, your comment is unpublished.
    lloyd · 7 months ago
    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.
  • To post as a guest, your comment is unpublished.
    tom vincent · 1 years ago
    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
    • To post as a guest, your comment is unpublished.
      AL · 1 years ago
      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?
  • To post as a guest, your comment is unpublished.
    jimbosmiles · 1 years ago
    I am with the others - it works up to a point.

    For me, that point is it creates the new s/s, details the folder I have shown (in Cells A1), the a yellow highlighted bar in row 2 with the headings followed by nothing else!

    The folder I am looking at is empty except for sub folders (i.e. no data files exist) and the sub folders do not appera at all.

    Can anyone help me list the sub folders and their files?
  • To post as a guest, your comment is unpublished.
    Paul J · 2 years ago
    When I run this code it works but it only shows the first folder in side the folder that I choose. For example,

    When I run the code I choose "C:\Users\Johnson\Music" (Note: I have 70 Folders inside my Music Folder)

    When the code runs it only shows the first folder and then list all the folders inside that folder.

    I would like it to list all the folders inside the Music folder.