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:
    270
    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,650
    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:
    270
    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:
    270
    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,650
    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:
    270
    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,650
    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:
    270
    Please help with that, appreciated.
  9. sms2luv

    sms2luv Member

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

    shrivallabha Excel Ninja

    Messages:
    1,650
    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:
    270
    Wowwwww, you did what I wanted.
    Thanks
  12. Monty

    Monty Well-Known Member

    Messages:
    821
    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:
    270
    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:
    270
  15. shrivallabha

    shrivallabha Excel Ninja

    Messages:
    1,650
    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:
    270
    Didn't find any such option to display only excel files
  17. shrivallabha

    shrivallabha Excel Ninja

    Messages:
    1,650
    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.

Share This Page