PDA

View Full Version : Solved: Listing all files in directory including zip files



mnementh
08-13-2010, 12:00 PM
My name's Sandy and I'd like to introduce myself to the forum.

I'm 58 years old and into electronics and Karaoke, among other things.

I work in the MIcrowave industry, dealing with Microstrip and Radar equipment.

It's on the subject of Karaoke that I've joined the forum as I have a problem it seems can only be solved through VBA and Excel.

I have 50,000+ Karaoke tracks in .ZIP format as follows;

SF001-01 - Stewart, Rod - Maggie Mae.ZIP

This is DISC ID (separator) ARTIST (separator) Song.ZIP

What I'm trying to do is eliminate duplicate songs from my list and to get things into perspective, My Way by Frank Sinatra is repeated over 200 times by various Karaoke disc manufacturers.

If that wasn't bad enough, minor filename errors preclude the use of simple duplicate file finders.

My initial, apparently simple problem, is to get a list of all my files onto a spreadsheet as complete paths.

To this end, I tried this Excel code from this site;

Unfortunately, as my post count is <5, I can't post a direct link to the web article but if you Google for "Create File List Excel VBA" (no "", obviously) virtually the first hit will bring you to VBA Express.

Now, as all my files are in .ZIP format, anybody like to have a guess at what files don't list correctly?

The program code from above, happily lists everything in my selected folder/subfolders EXCEPT .ZIP files. My test folder has over 1,000 files in it but only the non .ZIP files show.

Can anyone shed some light on why this seems to happen?

The .ZIP files are NOT hidden or system files and should show up but don't.

I'm stumped and any assistance would be much appreciated.

Sandy

Kenneth Hobs
08-13-2010, 12:31 PM
Welcome to the forum! Please use descriptive text for your title for new posts.

Listing of files can depend on your version of Excel. In 2007, FileSearch() was removed. So, any macro that uses it would not work in the newer versions.

As for listing the files using other methods like FSO, change the typical wildcard search of *.xls to *.zip.

If you are wanting to look for filenames in the zip file, that is another matter. See Ron's website for tips. Look for zip as not all zips are created equal. http://www.rondebruin.nl/tips.htm

mnementh
08-13-2010, 12:53 PM
Hi Kenneth.
Thanks for the quick reply.

I'm using Excel 2002, so FileSearch() should still be O.K., I hope.

Here is a small section of the code;

With Application.FileSearch
.LookIn = strSourceFolder 'look in the folder browsed to
.FileType = msoFileTypeAllFiles 'get all files
.SearchSubFolders = True 'search sub directories
.Execute 'run the search


It seems to be looking for all files by default but for some reason, it simply will not show .ZIP files???

Sandy

GTO
08-13-2010, 02:45 PM
Greetings Sandy,

Welcome to vbaexpress!

When posting code, please use the ...your code here tags, as this lays out the code nicely and makes it much easier to read. You can insert the tags by pressing the green/white VBA button atop the quick reply box.

Certainly a laymen coder's opinion at best, but for what it's worth, I would look at FSO rather than spend time learning and developing solutions with methods that are no longer supported. Maybe just my pea brain, but why spend time learning and developing stuff that you won't be able to later use?

That said, I tried this and it worked fine...


Option Explicit

Sub oldFS()
Dim fs As FileSearch
Dim i As Long

Set fs = Application.FileSearch
With fs

.FileType = msoFileTypeAllFiles

.LookIn = ThisWorkbook.Path & "\"
.Filename = "*.zip"
If .Execute(SortBy:=msoSortByFileName, _
SortOrder:=msoSortOrderAscending) > 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
Cells(i + 1, "A").Value = .FoundFiles(i)
Next i

Columns(1).AutoFit
Else
MsgBox "There were no files found."
End If
End With
End Sub

...or using FileSystemObject...


Option Explicit

Sub exa()
Dim FSO As Object 'FileSystemObject
Dim InitialFolder As Object 'Folder
Dim fsoFile As File
Dim lRow As Long

Set FSO = CreateObject("Scripting.FileSystemObject") 'New FileSystemObject
Set InitialFolder = FSO.GetFolder(ThisWorkbook.Path & "\")

lRow = 1

For Each fsoFile In InitialFolder.Files
If LCase(Right(fsoFile.Name, 4)) = ".zip" Then
lRow = lRow + 1
Cells(lRow, "A").Value = fsoFile.Path
End If
Next

Columns(1).AutoFit
End Sub

Hope that helps,

Mark

mnementh
08-13-2010, 03:07 PM
Hi Mark,
thanks for the reply and the information, Re. inserting code.

I didn't want to post a huge swathe of code, particularly since the code I'm referring to isn't mine but has been posted on VBA Exprass by a member called XLGIBBS.

However, I'll see if your posting info works here;

EDIT
O.K. tried to insert the code between the ........ tags but even that is blocked by the link filter.

Very frustrating.

Sandy

Aussiebear
08-13-2010, 05:01 PM
Sandy, the VBA button should not be blocked by a link filter. Please have another go.

GTO
08-13-2010, 07:46 PM
Is this the KB entry you were referring to?

http://www.vbaexpress.com/kb/getarticle.php?kb_id=837

mnementh
08-14-2010, 04:08 AM
Hi GTO.

That's the very one.

Well spotted.

Sandy

mnementh
08-14-2010, 05:33 AM
HI Mark.
I tried the two code sample you posted.

The first one seemed to work O.K. but when I changed the filepath to the folder containing my .ZIP files, it too failed to show any of them.

I then changed the path to a folder that had nothing in it EXCEPT .ZIP files and it came back with a "No files found" message.

The 2nd piece of code wouldn't run, halting at the line;

Dim fsoFile as File

I really do not understand this as when I do a simple search of the main folder, with subfolders selected, I find all 1000+ .ZIP files no problem.

Sandy

GTO
08-14-2010, 06:12 AM
...The program code from above, happily lists everything in my selected folder/subfolders EXCEPT .ZIP files. My test folder has over 1,000 files in it but only the non .ZIP files show.

Can anyone shed some light on why this seems to happen?

The .ZIP files are NOT hidden or system files and should show up but don't...

From the link at #7, I downloaded both the code and the sample workbook provided by XLGibbs. W/O any modification, it returned the few .zip files I had in the chosen folder.

Presuming you made no alterations to the code, here would be an easy try, just in case you may have a file search 'saved' in the .Filename area.

In the below snippet, add the line shown:


With Application.FileSearch
.LookIn = strSourceFolder 'look in the folder browsed to
.FileType = msoFileTypeAllFiles 'get all files

'// to return all; clear any previous search//
.Filename = ""
'//to get just the zip files returned //
'//.Filename = "*.zip"

.SearchSubFolders = True 'search sub directories
.Execute 'run the search

'...remainder of code in PopulateDirectoryList()

If that does not work, maybe provide (attach) one of the zip files to test against.

If it does work, I respectfully/humbly believe there is one harmless error in the same procedure. If you are looking to return the size of ea file, find the line and try:

'.Offset(i, 1) = Format(objFile.Size, "0,000") & " KB"
.Offset(i, 1) = Format(Application.Max(objFile.Size \ 1024, 1), "#,###") & " KB"
.Offset(i, 2) = objFile.DateLastModified

Hope that helps,

Mark

GTO
08-14-2010, 06:21 AM
The 2nd piece of code wouldn't run, halting at the line;

Dim fsoFile as File


Sorry for the delayed response - my last was sent before I saw the above.

Change that to:

Dim fsoFile As Object

Try re-running the second bit of code again. If it fails to return the zip files, I would suspect that they are not true .zip files.

Mark

mnementh
08-14-2010, 10:18 AM
Sorry for the delayed response - my last was sent before I saw the above.

Change that to:

Dim fsoFile As Object

Try re-running the second bit of code again. If it fails to return the zip files, I would suspect that they are not true .zip files.

Mark
Hi Mark,
tried the 2nd code with your Mod and it worked.:friends:

Certainly for the folder I selected as below

Set InitialFolder = FSO.GetFolder("C:\Karaoke_Editor\OkayDokay\sf193\")

All 18 .ZIP files showed up perfectly.

Now all I have to do is get the search to include subfolders and I'm a happy man.

Can a SearchSubFolders = True be incorporated into your code?

Sandy

GTO
08-15-2010, 03:32 AM
Hi Sandy,

I am still mystified as to why we're having such issues with Appllication.FileSearch, but irregarless of that...

I'm certainly not 'all that' on recursive procedures, but this seems to be working safely. Please test in a junk copy of your wb.


Option Explicit

Sub StartUp()
Dim wksData As Worksheet

With ThisWorkbook
Set wksData = .Worksheets.Add(After:=.Worksheets(.Worksheets.Count), _
Type:=xlWorksheet)
End With
wksData.Cells(1).Value = "FullName"
wksData.Cells(1).Font.Bold = True
AllFilesBasic "D:\2010\2010-08-13\", wksData
End Sub

Sub AllFilesBasic(TopDir As String, wks As Worksheet)
Dim _
fsoFol As Object, _
fsoSubFol As Object, _
fsoFil As Object, _
rngStartPoint As Range, _
lOffset As Long

Static FSO As Object
If FSO Is Nothing Then Set FSO = CreateObject("Scripting.FileSystemObject")
Set fsoFol = FSO.GetFolder(TopDir)
Set rngStartPoint = wks.Cells(wks.Rows.Count, 1).End(xlUp).Offset(1)

For Each fsoFil In fsoFol.Files
If LCase(Right(fsoFil.Name, 4)) = ".zip" Then
rngStartPoint.Offset(lOffset).Value = fsoFil.Path
lOffset = lOffset + 1
End If
Next

For Each fsoSubFol In fsoFol.SubFolders
AllFilesBasic fsoSubFol.Path, wks
Next
wks.Range("A1").EntireColumn.AutoFit
End Sub

This should give all the .zip filenames, including those located in sub-directories of the folder started in.

Mark

mnementh
08-15-2010, 07:14 AM
Hi Mark.
My, you must have been up with the Lark this morning.

I've just tried the code above and it seems to work well.

I'm a happy chappie and I can now get on with the rest of the fiddly bits.

Many thanks for your effort.

Much appreciated.

Sandy

GTO
08-15-2010, 08:04 AM
In the top sub StartUp(), place the path here:

AllFilesBasic "D:\2010\2010-08-13\", wksData

We are passing two arguments (arg), the first being the initial (closest to root) folder's path as a string, and the second arg passes the created worksheet as an object.

Mark

mnementh
08-15-2010, 11:57 AM
Hi Mark,
I did figure out the path insertion point, O.K. and I'm pleased to say your code works perfectly.

All 1000+ files in my test folder displayed correctly.

Your efforts much appreciated.

Sandy

GTO
08-15-2010, 03:47 PM
I am glad that worked Sandy. :friends:

Out of curiousity and if you do not mind, given that we are currently displaying the fullname of ea file, I do not see a way of sorting sensibly.

Again, just curious, but are you planning on reading through the list and deleting or movining duplicates manually - or are you adding more code to do this?

Just a note of courtesy, if this thread is solved to your satisfaction, there is an option to indicate Solved - located under Thread Tools, atop your first post. This saves 'answerers' time in checking threads.

Glad to 'meet' and and again, welcome to the forum. There's some mighty decent folk here, I'm sure you'll be glad you joined:thumb .

Mark

mnementh
08-16-2010, 11:01 AM
I am glad that worked Sandy. :friends:

Out of curiousity and if you do not mind, given that we are currently displaying the fullname of ea file, I do not see a way of sorting sensibly.

Again, just curious, but are you planning on reading through the list and deleting or movining duplicates manually - or are you adding more code to do this?

Just a note of courtesy, if this thread is solved to your satisfaction, there is an option to indicate Solved - located under Thread Tools, atop your first post. This saves 'answerers' time in checking threads.

Glad to 'meet' and and again, welcome to the forum. There's some mighty decent folk here, I'm sure you'll be glad you joined:thumb .

Mark
Hi Mark,
I'm so glad you asked that question.

In post #1, I showed the kind of filename I'm looking at, in this format;

SF001-01 - Stewart, Rod - Maggie Mae.ZIP

This is DISC ID (separator) ARTIST (separator) Song.ZIP

I have 50,000+ karaoke tracks in similar format but by different manufacturers, resulting in different DISC ID's.

Also, while the DISC ID tends to be O.K. the artist name or song title can have very minor differences that prevent a normal Dupe finder flagging them.

Obviously, any tracks that have the same DISC ID are dupes and can be deleted.

What I'm hoping to do is to split the full pathname into DISC ID, ARTIST & SONG in separate Excel columns.

Then first up is to sort on DISC ID, flag any dupes and "kill" them, using the full path information.

Second, sort on ARTIST, then get Excel to go down the list and flag each separate artist, possibly on a different sheet. Then the fun starts as this must be a manual exercise!

If, for example I have a column with;

Rod Stewart
Stewart, Rod
Stewart ROD

and so on, I'd like to flag Rod Stewart as the master and get Excel to rename all the other "wrong" names to Rod Stewart

Third, sort on SONG and do as above.

Hopefully, at this point, I'll have a list where ARTIST & SONG are consistent.

Finally, a routine that checks ARTIST AND SONG and as above, a manual trawl through to basically "kill" any dupes.

Ideally, I only want ONE copy of any given SONG by a given ARTIST.

To give you a clue as to the magnitude of the problem, My Way by Frank Sinatra is available 200_ times on various Karaoke manufacturers discs.

Let's face it, How many copies of a track does one need???:cool:

Still a fair way to go.

Sandy.
P.S. thanks for the "Solved" info. I'll leave it to give you a chance to see this reply, Then I'll flag it as "Solved".
P.P.S. I've seen the class of replies on here and I am, indeed, glad I've joined. Your help has been invaluable.

mnementh
08-16-2010, 01:30 PM
Hi again Mark.
I thought I'd have a little Google about this problem and apparently, it's a known issue;

With Application.FileSearch simply will NOT return anything to do with .ZIP files.

I Googled this;

with application.filesearch does not return zip files

There are loads of hits with people having this same problem.

Sandy

GTO
08-17-2010, 09:35 AM
Sorry for the slow response Sandy; just amidst a hectic week...

It sounds like you have it well planned, if you run into issues, certainly do not hesitate to post. More at a "thinking aloud" than well thought out observations, I would think possible issues to be:

The split or text to columns part. If there is always a leading/trailing space in the 'seperators', and never spaces around the hyphen in DISC ID, then probably okay. Another possible issue might be hyphenated names..
As you mention, the names will be 'fun'. There are several members here who are awfully good at Regular Expressions; which I would think might be a way to go.If you do run into a wall so-to-speak, I would suggest posting a fairly good sized sampling of what is returned for the fullnames, so that we can see the variations.

Have a great day:thumb

Mark