• 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...

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

Folder indexing

sms2luv

Member
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
 
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
 

Attachments

  • GetFolderListing_1.1.xlsm
    41.7 KB · Views: 24
Just one word to say.
You guys are G-E-N-I-U-S, no not genius

Bigger bigger and bigger then that...

Thanks
 
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.
 

Attachments

  • PicsArt_08-07-02.19.20.jpg
    PicsArt_08-07-02.19.20.jpg
    237.2 KB · Views: 8
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.
 
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
 
Can we also change read write access settings using VBA, in case you are not an admin
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.
 

Attachments

  • GetFolderListing_1.2.xlsm
    272.1 KB · Views: 29
Really master piece code..

Can you modify to see it pick up only excel files from folder and sub folders
Code:
'----------------------------------------------------------------------------------------------------------------------
'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
.
 
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
 
Really master piece code..

Can you modify to see it pick up only excel files from folder and sub folders
Code:
'----------------------------------------------------------------------------------------------------------------------
'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
.
There's a clue in the code itself if you want such modification ;)

See if you can locate and update.
 
Didn't find any such option to display only excel files
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:
  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:
  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,
 

Attachments

  • GetFolderListing_1.3.xlsm
    281.1 KB · Views: 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.
 
You can always filter the file results in Excel (Column B).
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
 
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
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.
 
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.
 
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.
I will ask the same question again:
Did you put value "new" in cell C9 and tried to see what results you get?
 
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
 
Back
Top