|
|
capture directory listings to Excel |
|
Author |
Message |
JosefMalinekLogos
|
Posted: Sun Nov 09 09:19:15 CST 2003 |
Top |
Excel >> capture directory listings to Excel
This is probably a stupid question, but is there some way to capture the
file names, type, and date information from a Windows Explore dialog for use
in Excel?
Thanks,
E-Jo
Excel348
|
|
|
|
|
Don
|
Posted: Sun Nov 09 09:19:15 CST 2003 |
Top |
Excel >> capture directory listings to Excel
Here is one I use to get .mp3 files. You probably don't need anything before
'Finds Files
Modify to suit
Sub FindFiles()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
'sets Erase or Append option
lastrow = Range("a65536").End(xlUp).Row
If lastrow = 4 Then lastrow = 5 Else lastrow = lastrow
'MsgBox lastrow
If UCase([a3]) = "E" Then
Range("a5:f" & lastrow).ClearContents
lastrow = 4
ElseIf UCase([a3]) = "A" Then lastrow = lastrow
End If
'Sets Musicpath
If Right([a1], 1) <> "\" And Left([a1], 1) <> "_" Then x = "\"
If IsEmpty([a2]) = False And Right([a2], 1) <> "\" Then y = "\"
musicpath = [a1] & x & [a2] & y
'Finds Files
With Application.FileSearch
.NewSearch
.LookIn = musicpath
.SearchSubFolders = True 'False
.MatchTextExactly = False
.Filename = ".mp3" '*.mp3* did not work in 97
If .Execute(msoSortOrderDescending) > 0 Then
'MsgBox "There were " & .FoundFiles.Count & " file(s) found."
For i = 1 To .FoundFiles.Count
'MsgBox Mid(.FoundFiles(i), Len(musicpath) + 1, 2)
If Mid(.FoundFiles(i), Len(musicpath) + 1, 2) <> "__" Then 'added for
__INCOMPLETE
x = Application.Find("\", StrReverse(.FoundFiles(i))) - 2 'must have
function before xl2000
y = Application.Find("-", StrReverse(.FoundFiles(i))) - 1
Cells(i + lastrow, 1).Value = Mid(.FoundFiles(i), Len(.FoundFiles(i)) - x,
x - y)
x = Application.Find("-", .FoundFiles(i)) + 1
Cells(i + lastrow, 2).Value = Mid(.FoundFiles(i), x, Len(.FoundFiles(i)) -
x - 3)
Cells(i + lastrow, 3).Value = FileLen(.FoundFiles(i))
Cells(i + lastrow, 4).Value = FileDateTime(.FoundFiles(i))
Cells(i + lastrow, 5).Value = .FoundFiles(i) 'Path to play
End If 'added
Next i
Else
MsgBox "There were no files found."
End If
End With
Range("a5:g" & Range("a65536").End(xlUp).Row) _
.Sort Key1:=Cells(1, 1), Order1:=xlAscending, Key2:=Cells(1, 2),
Order2:=xlAscending, Orientation:=xlTopToBottom
[a5].Select
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
--
Don Guillett
SalesAid Software
> This is probably a stupid question, but is there some way to capture the
> file names, type, and date information from a Windows Explore dialog for
use
> in Excel?
>
> Thanks,
> E-Jo
>
>
|
|
|
|
|
Gord
|
Posted: Sun Nov 09 11:53:57 CST 2003 |
Top |
Excel >> capture directory listings to Excel
Everett
Several methods to accomplish this.......
To add a "Print Directory" feature to Explorer, go to
this KB Article.
http://support.microsoft.com/default.aspx?scid=KB;EN-US;q272623&
Or you can download Printfolder 1.2 from.....
http://no-nonsense-software.com/freeware/
I use PF 1.2 and find it to be more than adequate with custom features.
OR Go to DOS(Command) prompt and directory.
Type DIR >MYFILES.TXT
All the above create a *.TXT file which can be opened in Excel.
One more method if you want to by-pass the *.TXT file and pull directly to
Excel is to use Tushar Mehta's Excel Add-in.
http://www.tushar-mehta.com/ scroll down to Add-ins>Directory Listing.
Download the ZIP file and un-zip to your Office\Library folder.
Gord Dibben XL2002
>This is probably a stupid question, but is there some way to capture the
>file names, type, and date information from a Windows Explore dialog for use
>in Excel?
>
>Thanks,
>E-Jo
>
Gord Dibben XL2002
|
|
|
|
|
Everett
|
Posted: Sun Nov 09 15:09:09 CST 2003 |
Top |
Excel >> capture directory listings to Excel
Thanks Don and Gord for your detailed responses. I'm not fluent in VBA at
the moment so I opted for the first method listed by Gord. Actually there is
a slightly different version for Win2K and XP described under KB 321379.
The only problem is that it works fine going to the default printer, but I
don't know how to make it print to a file since no print dialog comes up
(where I could check 'print to file').
How do I make it do that?
Thanks again,
E-Jo
> This is probably a stupid question, but is there some way to capture the
> file names, type, and date information from a Windows Explore dialog for
use
> in Excel?
>
> Thanks,
> E-Jo
>
>
|
|
|
|
|
|
|