1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Folder indexing

Discussion in 'VBA Macros' started by sms2luv, Aug 5, 2017.

  1. sms2luv

    sms2luv Member

    Messages:
    284
    Want to create code for Indexing all files and folder on Shared drive.
    I mean I want all files and folder names to be in excel cells, so that I can open it direcrly from excel
  2. shrivallabha

    shrivallabha Excel Ninja

    Messages:
    1,750
    You can try to use attached file to do this.

    - You need to put valid path in cell A9.
    - If you need subfolders to be listed then choose "TRUE" else you should choose "FALSE" via a validation drop down in cell B9.

    Press "Click Me". It will list all files and folders with hyperlinks.

    Original discussion for this macro happened on another forum.
    http://www.vbaexpress.com/forum/sho...List-Generator&p=270189&viewfull=1#post270189

    Attached Files:

  3. sms2luv

    sms2luv Member

    Messages:
    284
    Just one word to say.
    You guys are G-E-N-I-U-S, no not genius

    Bigger bigger and bigger then that...

    Thanks
  4. sms2luv

    sms2luv Member

    Messages:
    284
    This has worked better then expected.
    I was wondering how to filter it.
    Lets my server name is \\delindxxx\.
    In this directory there are several folders and inside folder there are sub folders and this could be a long chain.
    When I run the code.
    I get all folders, Sub Folders..... Example
    \\delindxxx\comcast\
    \\delindxxx\comcast\Daniel\New Folder
    \\delindxxx\comcast\Anwar\newtext.txt
    \\delindxxx\comcast\Suman\Test\Example\newfile.xlsx
    \\delindxxx\comcast\Enterprise\......
    \\delindxxx\comcast\......

    Can we break it in Parts to make filtering easy?
    Also can we change folder Read/Write access(if you are not an admin)
    Screenshot attached.

    One more way could be like advance filtering
    If I double click on 1 column cell(where actual location of the folder is mention), all relevant files or folder in that location should come in column 2, and so on, on column 3 and 4.
    I guess it would be a better way to navigate folders.

    Attached Files:

  5. shrivallabha

    shrivallabha Excel Ninja

    Messages:
    1,750
    The level of nesting for folders may vary and that in itself is tricky. This is the reason why the folder path is always listed in just one cell.

    Using built in auto-filter you can achieve most of the things.

    As to Windows permissions editing, I wouldn't look for handling them through Excel as it may become too complicated.
  6. sms2luv

    sms2luv Member

    Messages:
    284
    I understand the criticality.
    I think we can tet for text to column making "/" as separater.

    So after indexing is complete we can run a macro for Text to column for atleast first 3 paths
  7. shrivallabha

    shrivallabha Excel Ninja

    Messages:
    1,750
    That could be done. Do you want me to assist or you want to try yourself?
    Chirag R Raval likes this.
  8. sms2luv

    sms2luv Member

    Messages:
    284
    Please help with that, appreciated.
  9. sms2luv

    sms2luv Member

    Messages:
    284
    Can we also change read write access settings using VBA, in case you are not an admin
  10. shrivallabha

    shrivallabha Excel Ninja

    Messages:
    1,750
    I have responded to this already in post #5.
    "As to Windows permissions editing, I wouldn't look for handling them through Excel as it may become too complicated."

    I have revised code to include 3 levels of filter in column D,E&F and the workbook is attached herewith. It is lightly tested so if you get any bugs or errors then report back.

    Attached Files:

    Thomas Kuriakose and sms2luv like this.
  11. sms2luv

    sms2luv Member

    Messages:
    284
    Wowwwww, you did what I wanted.
    Thanks
  12. Monty

    Monty Well-Known Member

    Messages:
    836
    Really master piece code..

    Can you modify to see it pick up only excel files from folder and sub folders
    Code (vb):
    '----------------------------------------------------------------------------------------------------------------------
    'If you are copying and changing this code then do not forget to add:
    'Tools | References | Microsoft Shell Controls and Automation
    '----------------------------------------------------------------------------------------------------------------------
    Option Explicit
    Option Compare Text 'We might come across mixtures of uppercase lowercase letters sometimes
    Public objShApp As Shell
    Public i As Long
    Public Sub RunFileFolderList()
    Dim strPath As String

    '----------------------------------------------------------------------------------------------------------------------
    'Setting the worksheet to list results from row 11 and performing cleanup to remove previous listings
    '----------------------------------------------------------------------------------------------------------------------
    i = 11
    If Range("A" & Rows.Count).End(xlUp).Row > i Then Range("A11:C" & Range("A" & Rows.Count).End(xlUp).Row).ClearContents

    With Application
        .ScreenUpdating = False
       
        ListItemsInFolder Range("A9").Value, Range("B9").Value
       
        .ScreenUpdating = True
    End With

    Set objShApp = Nothing

    End Sub
    Public Sub ListItemsInFolder(strPath As String, boolSubFolder As Boolean)
    Dim fldItem As FolderItem

    If objShApp Is Nothing Then Set objShApp = New Shell

    '----------------------------------------------------------------------------------------------------------------------
    'Shell's Namespace object holds onto many different and useful properties that can used to extract information
    'In this code we have used its FileSystemObject equivalents
    '----------------------------------------------------------------------------------------------------------------------
    With objShApp.Namespace(strPath)
        For Each fldItem In .Items

    '----------------------------------------------------------------------------------------------------------------------
    'The code tends to error when it comes across a zip file which in turn may contain a folder. The code then gives you
    'an RTE so to bypass this possibility we use following check of verifying .zip
    '----------------------------------------------------------------------------------------------------------------------
       If InStr(fldItem.Parent, ".zip") = 0 Then
            If fldItem.IsFolder Then
                If InStr(fldItem.Path, ".zip") = 0 Then
                    Cells(i, 1).Value = fldItem.Path
                    ActiveSheet.Hyperlinks.Add Anchor:=Cells(i, 3), Address:=fldItem.Path, TextToDisplay:="Click Here"
                    i = i + 1
                Else
                    Cells(i, 1).Value = Left(fldItem.Path, InStrRev(fldItem.Path, fldItem.Name) - 2)
                    Cells(i, 2).Value = fldItem.Name
                    ActiveSheet.Hyperlinks.Add Anchor:=Cells(i, 3), Address:=fldItem.Path, TextToDisplay:="Click Here"
                    i = i + 1
                End If
            Else
                Cells(i, 1).Value = Left(fldItem.Path, InStrRev(fldItem.Path, fldItem.Name) - 2)
                Cells(i, 2).Value = fldItem.Name
                ActiveSheet.Hyperlinks.Add Anchor:=Cells(i, 3), Address:=fldItem.Path, TextToDisplay:="Click Here"
                i = i + 1
            End If
            If fldItem.IsFolder And boolSubFolder Then ListItemsInFolder fldItem.Path, boolSubFolder
        End If
        Next fldItem
    End With
       
    End Sub
     
    .
    sms2luv likes this.
  13. sms2luv

    sms2luv Member

    Messages:
    284
    Idea was really brilliant, can we make an option to show only excel files.
    Example.
    We have to give a path which is a mandate.
    We have a True, False option to display Sub folders.
    Similarly can we make a true, false option to display only excel file
  14. sms2luv

    sms2luv Member

    Messages:
    284
  15. shrivallabha

    shrivallabha Excel Ninja

    Messages:
    1,750
    There's a clue in the code itself if you want such modification ;)

    See if you can locate and update.
    AliGW likes this.
  16. sms2luv

    sms2luv Member

    Messages:
    284
    Didn't find any such option to display only excel files
  17. shrivallabha

    shrivallabha Excel Ninja

    Messages:
    1,750
    I didn't say that the option was readily available but it is there as a clue.

    All that was required to change was here:
    Code (vb):
      Else
                Cells(i, 1).Value = Left(fldItem.Path, InStrRev(fldItem.Path, fldItem.Name) - 2)
                Cells(i, 2).Value = fldItem.Name
                ActiveSheet.Hyperlinks.Add Anchor:=Cells(i, 3), Address:=fldItem.Path, TextToDisplay:="Click Here"
                i = i + 1
            End If
    As with INSTR function which checks for substring and it is used in the code multiple times:
    Code (vb):

      Else
      If InStr(fldItem.Name, Range("C9").Value) > 0 Then 'INSTR IF LOOP
     Cells(i, 1).Value = Left(fldItem.Path, InStrRev(fldItem.Path, fldItem.Name) - 2)
      Cells(i, 2).Value = fldItem.Name
      ActiveSheet.Hyperlinks.Add Anchor:=Cells(i, 3), Address:=fldItem.Path, TextToDisplay:="Click Here"
      CreateThreeFilters Cells(i, 3), fldItem.Path
      i = i + 1
      End If 'INSTR IF LOOP
     End If
     
    I am attaching the revised file for reference. You need to define search substring in Cell C9. If you want all then leave it blank.

    Hth,

    Attached Files:

    sms2luv likes this.
  18. Svmaxcel

    Svmaxcel New Member

    Messages:
    14
    Just amazing.
    Can you also add option to search a file from excel.
    I mean a option where you can write the name of the file in a cell and press search button.
  19. shrivallabha

    shrivallabha Excel Ninja

    Messages:
    1,750
    You can always filter the file results in Excel (Column B).
  20. Svmaxcel

    Svmaxcel New Member

    Messages:
    14
    Agreed, I can filter it.
    What I mean is as below.
    Let's say location is c:/New folder.
    Add "Newfile.xls" in top rows and hit the search button.
    Excel should now search for the file name in the directory mentioned.

    You have given an option to display file with specific extention like .xlsx...
    But I guess the search option would be helpful
  21. shrivallabha

    shrivallabha Excel Ninja

    Messages:
    1,750
    Did you try putting the search value in cell C9 and test?

    The code should return partial or full matches for the criteria you are mentioning. It will not handle any wildcards though.
  22. Svmaxcel

    Svmaxcel New Member

    Messages:
    14
    Wanted partial searching.
    For example if there is a file named "newfile.xlsx", and if I search for the word " new," this file should populate.
    If possible can we also use Search as you type like google search
    It just came out of my imagination.
  23. shrivallabha

    shrivallabha Excel Ninja

    Messages:
    1,750
    I will ask the same question again:
    Did you put value "new" in cell C9 and tried to see what results you get?
  24. Svmaxcel

    Svmaxcel New Member

    Messages:
    14
    No, didn't work.
    It was blank.

    Can we incorporate search feature and a option to delete file from excel itself rather then going to the folder
  25. Chirag R Raval

    Chirag R Raval Member

    Messages:
    333
    Dear Sir @shrivallabha

    Just amazing code...Amazing presentation & solution for this requirement..
    sorry to linking you from this thread..with my below problem but....in reference with my thread ...below,

    https://chandoo.org/forum/threads/h...erated-file-that-contain-some-fix-word.34854/

    can it possible that our post no 17's attached file's structure present to user as a user form? to work.. OR
    can it possible to use can get bthis structure with all formatting & buttons
    in active sheet? through call macro? so user not need to find or get shortcut to this file.... or not to depend on this file for work...

    if this available , there are get amazing help to users..

    hope there are some way there...

    Regards,
    Chirag Raval

Share This Page