3

I am an avid movie collector, have a collection of 1000's of movies.

What I normally do is, I have a notepad with list of movies to download and watch. I keep all the downloaded movies in one folder named "Movies" and once I watch any movie, I move it to another folder named "Watched Movies".

Now I want an excel file with sheets that will show the list of movies in each of these folders. Also, I want the excel to update by itself, whenever I put a movie into the "Movies" folder, the excel sheet should show that file/folder name and similarly whenever I move it to the "Watched Movies" folder.

Am sure this will be a piece of cake for some of you out there.

Any help is appreciated. Thank you, Best, J.V

user2489233
  • 31
  • 1
  • 2
  • 1
    have you tried anything? if not search for `Dir function` (something like [this code in question](http://stackoverflow.com/questions/4282940/does-dir-make-any-guarantee-on-the-order-of-files-returned)) and you could come back here if you encounter any problems... – Kazimierz Jawor Jun 15 '13 at 18:16
  • did you try my new code? Did it work ? – isJustMe Jun 17 '13 at 19:31
  • 1
    @isJustMe has a great answer and looks like it worked for you. Marking it as the accepted answer will help others. Give it a try! – AntonioOtero Jun 26 '13 at 21:06

1 Answers1

2

Ok to do this you will have you use a VBA macro, what you actually looking for is not that hard but requires a bit of programming knowledge.

Step 1
You will have to add the developer ribbon on excel, if you are using 2010 here is how.

Step 2
On the developer tab click on Visual Basic and it will open the VB interface, I will provide you the script but you need to add the 'Microsoft Scripting Runtime' reference.

Step 3
• On the Visual Basic Select Tools - References from the drop-down menu
• A listbox of available references will be displayed
• Tick the check-box next to 'Microsoft Scripting Runtime'
• The full name and path of the scrrun.dll file will be displayed below the listbox
• Click on the OK button

Step 4

Selec ThisWorkbook and paste the following code

Sub ViewFiles()
    theRow = 3
    Call ShowFiles(Range("A1"), True)
End Sub

Sub ShowFiles(path, subfolders)
    Set obj = New Scripting.FileSystemObject
    Set Source = obj.GetFolder(path)
    On Error Resume Next
    For Each file In Source.Files
       theCol = 2
       Cells(theRow, theCol).Value = file.path
       theCol = theCol + 1
       Cells(theRow, theCol).Value = file.Name
       theCol = theCol + 1
       Cells(theRow, theCol).Value = file.Size
       theCol = theCol + 1
        theRow = theRow + 1
    Next
    If subfolders Then
    For Each subFolder In Source.subfolders
        Call ShowFiles(subFolder.path, True)
    Next
   End If
End Sub

Step 5
On the cell A1 paste the path you want to see, then press ALT +F8 and execute the macro called ViewFiles this will update the workbook with all the files.

It should look something like this: enter image description here

Let me know if it worked for you!

isJustMe
  • 5,452
  • 2
  • 31
  • 47
  • Hi, Sure will give a shot, but is the above steps same on excel 2007 as well? Because thats the version am using. – user2489233 Jun 16 '13 at 04:47
  • Hi, It did work on excel 2007 also. Thank you so much. Meanwhile, what is the code I should use to show the folder names? The above code shows the file names, but I have folders and files inside the same folder, that has to be listed. Thank you once again for the efforts and help. Cheers! – user2489233 Jun 16 '13 at 05:24
  • No problem :), see the updated code, it will show the files on the subfolders. Please if you find this answer useful consider upvoting and accepting this answer so it remains useful for other users :) – isJustMe Jun 16 '13 at 07:08