create list of files from explorer 9

[smiley0q0]

I don't know if this is the right forum to post this question in, if not please let me know.

I have some folders that have 500-1000 files in each, I want to create a printable list of these files. is there a way to put a list like this in word or excel? Just the title of the files is all I need.

the only way i know of right know is an extremely time consuming long way... using explorer... F2,copy,ctl+tab over to excel,paste... then repeat... over and over and over and over...
just wondering if there is a faster more efficient way.

thanks,
Smiley

 

[SkipVought]

Hi smiley,

Try this VBA code...

Sub SpellCheckAll()
    Set fs = Application.FileSearch
    With fs
        .LookIn = "C:\Documents and Settings\metzgerc\My Documents\LineOfBalance"
        .FileName = "*.doc"
        If .Execute(SortBy:=msoSortByFileName, _
        SortOrder:=msoSortOrderAscending) > 0 Then
            For i = 1 To .Execute(SortBy:=msoSortByFileName, _
        SortOrder:=msoSortOrderAscending)
              Msgbox fs.Name
            Next
        Else
            MsgBox "There were no files found."
        End If
    End With
End Sub

If you want to put this list on a sheet, substitute the Msgbox statement with...

With ActiveCell 
  .Value = fs.Name
  .Offset(1,0).Select
End With

Be sure to select A1 before you start.

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[smiley0q0]

hey skip,

thanks for the quick reply

the code looks great... but it's baulking at
[red].Value = fs.Name[/red]
says
"Run-Time error '438':
Object doesn't support this property or method"

is there a reference i need to apply?
the ones i have checked are
- Visual Basic for Applications
- Microsoft Excel 10.0 Object Library
- OLE Automation
- Microsoft Office 10.0 Object Library

Thanks,
Smiley

 

[xlbo]

This should do it:

Sub LoopFoldersListFiles()
'Loops thru sub folders, listing files
'Application.ScreenUpdating = False

Dim fso As New FileSystemObject
Dim f As Folder, path As String
'Initialize path.
path = "Your Path"
'Get a reference to the Folder object.
Set f = fso.GetFolder(path)
fName = path & "\*.*"
Filename = Dir$(fName)
Do While Filename <> &quot;&quot;
Range(&quot;A1&quot.Offset(r, 0) = Filename
r = r + 1
Filename = Dir$()
Loop
Next
End Sub

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[smiley0q0]

hey xlbo,

yours looks good too... but...

it's baulking at
[red]Dim fso As New FileSystemObject[/red]

it's saying there's a compile error
&quot;User-defined type not defined&quot;

i am on windows NT running Office XP... if that makes any difference

and just a side note, does it matter if my folder name has a space in it?

Thanks,
Smiley

 

[xlbo]

go to VBE
Go Tools>References
Find the reference for Scripting Runtime and tick it
then run again

an no - shouldn't make any difference if the folder name has a space

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[smiley0q0]

PERFECT!

Thank you

a star for you xlbo, for your extremely short turn around time as well as your code ... works very smooth.

and a star for you too skip

Thanks again,
Smiley

 

[Adnane]

Hi,
i can't find the Scripting Runtime Reference you mention. is there anything i should install or should i look for a different name referencing this runtime? (i'm running Win XP + Office 2003)

Thanks

Adnane

http://www.microsoft.com/northafrica

 

[smiley0q0]

the one i found and used was called

Microsoft Scripting Runtime.

hope that helps

 

[Adnane]

Thanks Smiley,
i was looking under S for Scripting Runtime and not under Microsoft.
sorry for that

Adnane

http://www.microsoft.com/northafrica

 

[newbiewonkinobie]

I used to have to go about this the long way:
Open up Notepad, copy and paste this:

dir|sort > File_List.txt

Then save it as:

Make_File_List.BAT

Then put into whichever folder you want to make a list of, double click it, and it makes a file called: File_List.txt
Open File_List.txt and delete the first 6 or 7 lines removing the heading, and leaving only the list of files. Save and close. Open in Excel using the fixed-width option. Now you can delete columns or whatever and keep the data you need.

Flores

 

[Adnane]

You deserve a star for this Newbie!!

Adnane

http://www.microsoft.com/northafrica

 

[MeGustaXL]

I like it Newbie! Have a star from me, too

XL<>[Smile]

Chris

 

[nikdiva]

Another modification of the .BAT file solution if you want to avoid opening and doing manual stuff is...

Open up Notepad, copy and paste this:
dir *.(FILE EXTENSION) /B > File_List.txt

Then save it as: Make_File_List.BAT

Then put into whichever folder you want to make a list of, double click it or open it programatically from the application that needs the list, and it makes a file called: File_List.txt

Import the file into whatever format you want, all there is in the text file are file names with extensions.

Nikki Wright
Database Specialist
&quot;No this. No that. No this or that. Oh boy, what do you want?&quot;

 

[logbook]

Well I don’t know why this is so complicated.
You can just go to a command prompt (DOS prompt) and
cd to the directory you want to list
then
dir /B >output.txt

Some things are a lot easier outside of Windows!

 

[cdavisdccb]

Is there a DOS &quot;/&quot; command that will perform this same thing but also the subfolders under each?

&quot;It doesn't matter how idiot-proof you make it, someone makes a better idiot!&quot;

 

[zbnet]

Include a /S in the command to give the contents of subfolders.

 

[jonsi]

following on from the excellent solutions offered here (who says DOS is Dead???)

Once captured, I've sorted them into types and stuck them into an Excel Workbook. Is there an easy way to make each of them a Hyperlink in one fell swoop rather than wading thru the list and individually linking each one?

hwyl
Jonsi B-)

 

[zbnet]

Well, Excel has the =hyperlink(ref) function, so you could build another column of hyperlinks by specifying the cell the file names are contained in, but beware: it will only work with a full directory path in front of the file name...

 

[jonsi]

thanks zbnet

=HYPERLINK(Cell Ref) works a treat ...you are a STAR!

Jonsi B-)