Solved: small macro code adjusting

[colibri]

I have a macro sample that create a list of all files in a specified folder (microsoft sample):

http://support.microsoft.com/?kbid=306248

I need just adjust this macro a little:

1. set file search for .mp3 and .M3U file types only.

2. 'File Date/Time' listing not required, file name and size only.

3. I want list only file name - without full path specified, i.e. 'rainbow.mp3'
(Currently macro list full path - C:\Documents and Settings\User\My Documents\mp3utilities\rainbow.mp3)

4. I want that the list was numbered( 01, 02...)

5. need show file size in Megabytes (i.e. 4,5Mb), not in bytes

could someone help how to make this adjusting correctly?
Thanks

[Killian]

Hi there,

I started making a couple of changes to the code you linked to but had a couple of problems... first, it's quite ugly code. Even though it came from MS, all that flipping back and forth with GoTo was disturbing. Also, typing in a filepath was getting boring so I grabbed a user form based folder finder I already had and adapted that to do what you want.
The attached file has a toolbar that runs the form. You navigate by double-clicking an item in the list. There's also an "up one level" button by the filepath text box, or you cab type or paste in a path
I think it should work for you ok. To check out the code and make changes, opens the VBEditor in Word (Alt+F11), right click the form and select View Code.

[colibri]

Hi,

thank you. I agree that original code was quite ugly code, even though it came from MS.. Also, probably, it will more handy if implement a "Copy File dialog box" to allow the user to navigate to a folder (i.e. integrated browsing capability, to select required folder via browsing)?
Also I want change this to list all files(all types).
Thanks,
colibri

[Killian]

Yeah, the easiest way would have been to use a built in dialog to get the path to the folder, but Word doesn't make a folder/file picker dialog available that just returns a path (at least not that I could find) which is why I had already done this file browsing form.
To change to show all files, you'll need to change the filesearch to search for all and take out the conditional statements that check for mp3/m3u. So, in the PopListBox routine, change this section: [VBA]With fs
.LookIn = txtPath.Text
.FileName = "*.m??"
If .Execute > 0 Then
For i = 1 To .FoundFiles.Count
ff = fso.getfilename(.FoundFiles(i))
If Right(ff, 3) = "mp3" Or Right(ff, 3) = "m3u" Then
lstFolders.AddItem ff 'add each mp3/m3u file
End If
Next i
End If
End With[/VBA]
to [VBA]With fs
.LookIn = txtPath.Text
.FileName = "*.*"
If .Execute > 0 Then
For i = 1 To .FoundFiles.Count
ff = fso.getfilename(.FoundFiles(i))
lstFolders.AddItem ff 'add each file
Next i
End If
End With[/VBA] and the final For...Next loop of the cmdExport_click event will need to change to this[VBA]For i = 0 To lstFolders.ListCount - 1
If Len(lstFolders.List(i)) > 4 And InStr(lstFolders.List(i), ".") = Len(lstFolders.List(i)) - 3 Then 'check if the name is "*.???"
WriteLine n & vbTab & lstFolders.List(i) & vbTab & _
Format(((FileLen(txtPath.Text & lstFolders.List(i)) / 1024) / 1024), "#.0") & "Mb" & vbLf, False
n = n + 1
End If
Next i[/VBA]

[colibri]

Thanks. Its works good and form is really very convenient. Please check changes i has made, I wish to be assured that I'd make it correctly.

Does the button which call a form should be shown at opening a file?(at top left corner) It seems it was visible when i test in the beginning. Or I should call macro from Tools -macro?

Two things I would like to adjust a little: to add one space(paragraf) right after list name (first row File listing of C:\... )
To reduce a little distance (tab/indent) between numbers and a file name in the file list.

[Killian]

No problem. It looks fine to me and works well.
The button is a toolbar I added to the document to run the form. You can use that or use tools|macros... which ever you prefer
This section of the code in the Export button click event sets the second tab stop and writes the first two lines[VBA]With Selection.ParagraphFormat.TabStops
.Add _
Position:=CentimetersToPoints(14), _
Alignment:=wdAlignTabRight, _
Leader:=wdTabLeaderSpaces
End With

WriteLine "File listing of " & txtPath.Text & vbLf, True
WriteLine "File Name" & vbTab & "File Size" & vbLf, True[/VBA]You can see there that it's setting the second tabstop to 14 centimeters so you can adjust that there.
Both lines end with a paragraph ( & vbLF) so you can add another there[VBA]WriteLine "File listing of " & txtPath.Text & vbLf & vbLf, True[/VBA]