Extracting file names to Excel



Vijayaraj
07-09-2005, 11:46 PM
Hi,
how to Extract the names of all Files in a folder and to paste it in Excel
sheet as text

Regards,
vijay

Pavel
07-09-2005, 11:46 PM
There are number of ways to do this but a very simple method that does not
require any macro knowledge is to load Command Prompt (CMD.EXE) and type:
DIR /B >filenames.txt. This will save all the file names in a text file. Now
just drag this file to Excel.

--
Pavel


"Vijayaraj" <Vijayaraj@discussions.microsoft.com> wrote in message
news:163A2F53-20A3-44BA-B08B-8821353E25D6@microsoft.com...
> Hi,
> how to Extract the names of all Files in a folder and to paste it in
> Excel
> sheet as text
>
> Regards,
> vijay
>

David Candy
07-09-2005, 11:46 PM
You can drag a folder from (say explorer) on to the file. You can put it in
Sendto, type sendto in Start Run, copy the file into the sendto folder, then
right click the folder you want to list, Send To, PrintDetailsAppend.
Sending To simulates a drag and drop.

Then import the file into excel, delete the columns you don't want (as most
are blank for any individual type of file, eg MP3 don't have a Date Picture
Taken and JPEGs don't have a Genre property), sort it on description and
delete the files you don't want. That will leave just the properties and
files you want. This is the most general purpose way I can think of to meet
everyones needs.

A drive is the same as a folder for these purposes.

Attached is a VBScript that generate the shell properties (what you see or
could see in Explorer). It is a 51 column csv. There is about 40 properties
on a standard XP and I've allowed about 10 columns for custom properties
that applications may add. Those whove seen it before this one automatically
finds the desktop rather than editing the script. To use, drop a folder on
it or place in Sendto and send a folder to it. If using the for command
(below) you must run it once whereever you put it so it can be found.

To do sub folders type in a command prompt in the folder that you want to
start in (It also does the parent folder - a quirk of For)
for /r %A in (.) do start "" /w "PrintDetailsAppend" "%~dpA"

It creates a file on the desktop called Folder Property List.txt

Copy the following line into a text document and rename it to
PrintDetailsAppend.vbs

Set objShell = CreateObject("Shell.Application")
Set Ag=Wscript.Arguments
set WshShell = WScript.CreateObject("WScript.Shell")

WshShell.RegWrite "HKLM\Software\Microsoft\Windows\CurrentVersion\App
Paths\" & Wscript.ScriptName & "\", Chr(34) & Wscript.ScriptFullName &
Chr(34)
WshShell.RegWrite "HKLM\Software\Microsoft\Windows\CurrentVersion\App
Paths\" & Left(Wscript.ScriptName, Len(Wscript.ScriptName)-3) & "exe" & "\",
Chr(34) & Wscript.ScriptFullName & Chr(34)

Set Fldr=objShell.NameSpace(Ag(0))

Set FldrItems=Fldr.Items
Set fso = CreateObject("Scripting.FileSystemObject")


Set DeskFldr=objShell.Namespace(16)
FName=fso.buildpath(DeskFldr.self.path, "Folder Property List.txt")


Set ts = fso.OpenTextFile(FName, 8, vbtrue)



For x = 0 to 50
t1 = t1 & Fldr.GetDetailsOf(vbnull, x) & " (Shell)" & vbtab
Next
ts.write FLDR.self.path &vbcrlf
ts.Write T1 & vbcrlf
T1=""


For Each FldrItem in FldrItems
For x = 0 to 50
t1 = t1 & Fldr.GetDetailsOf(FldrItem, x) & vbtab
Next
t1=t1 & vbcrlf
ts.Write T1
T1=""
Next


"Vijayaraj" <Vijayaraj@discussions.microsoft.com> wrote in message
news:163A2F53-20A3-44BA-B08B-8821353E25D6@microsoft.com...
> Hi,
> how to Extract the names of all Files in a folder and to paste it in
Excel
> sheet as text
>
> Regards,
> vijay
>

Donald Lessau
07-09-2005, 11:46 PM
"Vijayaraj" <Vijayaraj@discussions.microsoft.com> schrieb im Newsbeitrag
news:163A2F53-20A3-44BA-B08B-8821353E25D6@microsoft.com...
> Hi,
> how to Extract the names of all Files in a folder and to paste it in
Excel
> sheet as text

TrackerV3 (http://www.trackerv3.com/) can export extended file informations
of whole directories (or even directory trees) to CSV-formatted files. That
means, you can save absolutely all file information that's available on a
Windows platform in a well-defined, easily processable, and widely portable
file format. It's the documentation junkie's wildest dream come true.

Don

Bruce Chambers
07-09-2005, 11:47 PM
Vijayaraj wrote:
> Hi,
> how to Extract the names of all Files in a folder and to paste it in Excel
> sheet as text
>
> Regards,
> vijay
>


From the command prompt (Start > Run > Cmd.exe), simply change to
the desired directory and type "dir > filelist.txt" or "dir > lpt1,"
just as you used to do in DOS. Any of the switches for the DIR
command (type "dir /?") will work with this command, if you wish to
modify the output. You can then subsequently edit the resulting text
file using NotePad, WordPad, Word, etc.


--

Bruce Chambers

Help us help you:
http://dts-l.org/goodpost.htm
http://www.catb.org/~esr/faqs/smart-questions.html

You can have peace. Or you can have freedom. Don't ever count on having
both at once. - RAH

Don Taylor From:
07-09-2005, 11:47 PM
=?Utf-8?B?VmlqYXlhcmFq?= <Vijayaraj@discussions.microsoft.com> writes:
>how to Extract the names of all Files in a folder and to paste it in Excel
>sheet as text

Hop into Excel, you are going to create a simple macro to do this.

Go into Tools->Macro->Macros and give it a name MyDir and click Create.

That should drop you onto a new screen with

Sub MyDir()
End Sub

Get rid of those, replace all of those two lines with this:

Function MyDir(VIn1 As Variant) As Variant
Dim vaResult(10) As Variant
Dim i
i = 0
vaResult(i) = Dir(VIn1) ' Retrieve the first entry.
Do While vaResult(i) <> ""
i = i + 1
vaResult(i) = Dir ' Retrieve the next entries.
Loop
MyDir = vaResult
End Function

Now click File->Close and return to Excel
and you will be put back in your spreadsheet.

Now select a horizontal row of cells, perhaps ten or so
by depressing the left mouse button in the left most cell
and dragging horizontally across the ten cells.

Release mouse button and then type

=MyDir("C:\*.*")<ctrl><shift><enter>

where <ctrl> is the Ctrl key pressed first and held down
and then <shift> is pressed next and held down
and finally <enter> is pressed. Then release all three keys.

If the magic worked then your row of cells will be
filled with the directory contents of C:\*.*.

Test this cautiously on sheets that don't have data
that you cannot afford to loose. Test it several
different times, make sure it works for you as you expect.

David Candy
07-09-2005, 11:47 PM
>Test this cautiously on sheets that don't have data
>that you cannot afford to loose.

Why?


"Don Taylor From:" <dont@rdrop.com> wrote in message
news:W8OdnZl1lIjlxgrfRVn-gQ@scnresearch.com...
> =?Utf-8?B?VmlqYXlhcmFq?= <Vijayaraj@discussions.microsoft.com> writes:
> >how to Extract the names of all Files in a folder and to paste it in
Excel
> >sheet as text
>
> Hop into Excel, you are going to create a simple macro to do this.
>
> Go into Tools->Macro->Macros and give it a name MyDir and click Create.
>
> That should drop you onto a new screen with
>
> Sub MyDir()
> End Sub
>
> Get rid of those, replace all of those two lines with this:
>
> Function MyDir(VIn1 As Variant) As Variant
> Dim vaResult(10) As Variant
> Dim i
> i = 0
> vaResult(i) = Dir(VIn1) ' Retrieve the first entry.
> Do While vaResult(i) <> ""
> i = i + 1
> vaResult(i) = Dir ' Retrieve the next entries.
> Loop
> MyDir = vaResult
> End Function
>
> Now click File->Close and return to Excel
> and you will be put back in your spreadsheet.
>
> Now select a horizontal row of cells, perhaps ten or so
> by depressing the left mouse button in the left most cell
> and dragging horizontally across the ten cells.
>
> Release mouse button and then type
>
> =MyDir("C:\*.*")<ctrl><shift><enter>
>
> where <ctrl> is the Ctrl key pressed first and held down
> and then <shift> is pressed next and held down
> and finally <enter> is pressed. Then release all three keys.
>
> If the magic worked then your row of cells will be
> filled with the directory contents of C:\*.*.
>
> Test this cautiously on sheets that don't have data
> that you cannot afford to loose. Test it several
> different times, make sure it works for you as you expect.

Don Taylor From:
07-09-2005, 11:47 PM
"David Candy" <.> writes:
>>Test this cautiously on sheets that don't have data
>>that you cannot afford to loose.

>Why?

Because, occasionally, there are mistakes made and I don't want
to have you perhaps blow away decades of irreplacable work in a
fraction of a second and have no way to get it back.

If you experiment of a sheet that you don't care whether you
might loose it or not, and it seems to work dependably for you
then maybe you can get a little more wild and crazy with it
and use it on something more important, like your own data.

Example: Dragon Naturally Speaking Service Pack 1.
Supposed to be a fairly modest bug fix, Ok, so DNS had been
working just fine for me. I installed it. Humm. No errors
in the install. But wait. It seems it can no longer set the
volume of the microphone. And it seems it can no longer work
with dual monitors the way it did. And it popped up a little
box with "Unexpected Parameter. Close". And there is no
uninstall available. And weeks of training and building up
a vocabulary inside it... well, blow it away and reinstall to
get back to pre DNS SP-1 and see if that works... Damn.

I used to work on a team the delivered high reliability software.

>"Don Taylor From:" <dont@rdrop.com> wrote in message
>news:W8OdnZl1lIjlxgrfRVn-gQ@scnresearch.com...
>> =?Utf-8?B?VmlqYXlhcmFq?= <Vijayaraj@discussions.microsoft.com> writes:
>> >how to Extract the names of all Files in a folder and to paste it in
>Excel
>> >sheet as text
>>
>> Hop into Excel, you are going to create a simple macro to do this.
>>
>> Go into Tools->Macro->Macros and give it a name MyDir and click Create.
>>
>> That should drop you onto a new screen with
>>
>> Sub MyDir()
>> End Sub
>>
>> Get rid of those, replace all of those two lines with this:
>>
>> Function MyDir(VIn1 As Variant) As Variant
>> Dim vaResult(10) As Variant
>> Dim i
>> i = 0
>> vaResult(i) = Dir(VIn1) ' Retrieve the first entry.
>> Do While vaResult(i) <> ""
>> i = i + 1
>> vaResult(i) = Dir ' Retrieve the next entries.
>> Loop
>> MyDir = vaResult
>> End Function
>>
>> Now click File->Close and return to Excel
>> and you will be put back in your spreadsheet.
>>
>> Now select a horizontal row of cells, perhaps ten or so
>> by depressing the left mouse button in the left most cell
>> and dragging horizontally across the ten cells.
>>
>> Release mouse button and then type
>>
>> =MyDir("C:\*.*")<ctrl><shift><enter>
>>
>> where <ctrl> is the Ctrl key pressed first and held down
>> and then <shift> is pressed next and held down
>> and finally <enter> is pressed. Then release all three keys.
>>
>> If the magic worked then your row of cells will be
>> filled with the directory contents of C:\*.*.
>>
>> Test this cautiously on sheets that don't have data
>> that you cannot afford to loose. Test it several
>> different times, make sure it works for you as you expect.

David Candy
07-09-2005, 11:47 PM
It was the double negative I was complaining about.
"Don Taylor From:" <dont@rdrop.com> wrote in message
news:XpqdnaOFfN5CPQrfRVn-3Q@scnresearch.com...
> "David Candy" <.> writes:
> >>Test this cautiously on sheets that don't have data
> >>that you cannot afford to loose.
>
> >Why?
>
> Because, occasionally, there are mistakes made and I don't want
> to have you perhaps blow away decades of irreplacable work in a
> fraction of a second and have no way to get it back.
>
> If you experiment of a sheet that you don't care whether you
> might loose it or not, and it seems to work dependably for you
> then maybe you can get a little more wild and crazy with it
> and use it on something more important, like your own data.
>
> Example: Dragon Naturally Speaking Service Pack 1.
> Supposed to be a fairly modest bug fix, Ok, so DNS had been
> working just fine for me. I installed it. Humm. No errors
> in the install. But wait. It seems it can no longer set the
> volume of the microphone. And it seems it can no longer work
> with dual monitors the way it did. And it popped up a little
> box with "Unexpected Parameter. Close". And there is no
> uninstall available. And weeks of training and building up
> a vocabulary inside it... well, blow it away and reinstall to
> get back to pre DNS SP-1 and see if that works... Damn.
>
> I used to work on a team the delivered high reliability software.
>
> >"Don Taylor From:" <dont@rdrop.com> wrote in message
> >news:W8OdnZl1lIjlxgrfRVn-gQ@scnresearch.com...
> >> =?Utf-8?B?VmlqYXlhcmFq?= <Vijayaraj@discussions.microsoft.com> writes:
> >> >how to Extract the names of all Files in a folder and to paste it in
> >Excel
> >> >sheet as text
> >>
> >> Hop into Excel, you are going to create a simple macro to do this.
> >>
> >> Go into Tools->Macro->Macros and give it a name MyDir and click Create.
> >>
> >> That should drop you onto a new screen with
> >>
> >> Sub MyDir()
> >> End Sub
> >>
> >> Get rid of those, replace all of those two lines with this:
> >>
> >> Function MyDir(VIn1 As Variant) As Variant
> >> Dim vaResult(10) As Variant
> >> Dim i
> >> i = 0
> >> vaResult(i) = Dir(VIn1) ' Retrieve the first entry.
> >> Do While vaResult(i) <> ""
> >> i = i + 1
> >> vaResult(i) = Dir ' Retrieve the next entries.
> >> Loop
> >> MyDir = vaResult
> >> End Function
> >>
> >> Now click File->Close and return to Excel
> >> and you will be put back in your spreadsheet.
> >>
> >> Now select a horizontal row of cells, perhaps ten or so
> >> by depressing the left mouse button in the left most cell
> >> and dragging horizontally across the ten cells.
> >>
> >> Release mouse button and then type
> >>
> >> =MyDir("C:\*.*")<ctrl><shift><enter>
> >>
> >> where <ctrl> is the Ctrl key pressed first and held down
> >> and then <shift> is pressed next and held down
> >> and finally <enter> is pressed. Then release all three keys.
> >>
> >> If the magic worked then your row of cells will be
> >> filled with the directory contents of C:\*.*.
> >>
> >> Test this cautiously on sheets that don't have data
> >> that you cannot afford to loose. Test it several
> >> different times, make sure it works for you as you expect.
>
>

NobodyMan
07-09-2005, 11:47 PM
On Fri, 27 May 2005 15:51:34 +1000, "David Candy" <.> wrote:

>You can drag a folder from (say explorer) on to the file. You can put it in
>Sendto, type sendto in Start Run, copy the file into the sendto folder, then
>right click the folder you want to list, Send To, PrintDetailsAppend.
>Sending To simulates a drag and drop.
>
>Then import the file into excel, delete the columns you don't want (as most
>are blank for any individual type of file, eg MP3 don't have a Date Picture
>Taken and JPEGs don't have a Genre property), sort it on description and
>delete the files you don't want. That will leave just the properties and
>files you want. This is the most general purpose way I can think of to meet
>everyones needs.
>
>A drive is the same as a folder for these purposes.
>
>Attached is a VBScript that generate the shell properties (what you see or
>could see in Explorer). It is a 51 column csv. There is about 40 properties
>on a standard XP and I've allowed about 10 columns for custom properties
>that applications may add. Those whove seen it before this one automatically
>finds the desktop rather than editing the script. To use, drop a folder on
>it or place in Sendto and send a folder to it. If using the for command
>(below) you must run it once whereever you put it so it can be found.
>
>To do sub folders type in a command prompt in the folder that you want to
>start in (It also does the parent folder - a quirk of For)
>for /r %A in (.) do start "" /w "PrintDetailsAppend" "%~dpA"
>
>It creates a file on the desktop called Folder Property List.txt
>
>Copy the following line into a text document and rename it to
>PrintDetailsAppend.vbs
>
>Set objShell = CreateObject("Shell.Application")
>Set Ag=Wscript.Arguments
>set WshShell = WScript.CreateObject("WScript.Shell")
>
>WshShell.RegWrite "HKLM\Software\Microsoft\Windows\CurrentVersion\App
>Paths\" & Wscript.ScriptName & "\", Chr(34) & Wscript.ScriptFullName &
>Chr(34)
>WshShell.RegWrite "HKLM\Software\Microsoft\Windows\CurrentVersion\App
>Paths\" & Left(Wscript.ScriptName, Len(Wscript.ScriptName)-3) & "exe" & "\",
>Chr(34) & Wscript.ScriptFullName & Chr(34)
>
>Set Fldr=objShell.NameSpace(Ag(0))
>
>Set FldrItems=Fldr.Items
>Set fso = CreateObject("Scripting.FileSystemObject")
>
>
>Set DeskFldr=objShell.Namespace(16)
>FName=fso.buildpath(DeskFldr.self.path, "Folder Property List.txt")
>
>
>Set ts = fso.OpenTextFile(FName, 8, vbtrue)
>
>
>
>For x = 0 to 50
> t1 = t1 & Fldr.GetDetailsOf(vbnull, x) & " (Shell)" & vbtab
>Next
>ts.write FLDR.self.path &vbcrlf
>ts.Write T1 & vbcrlf
>T1=""
>
>
>For Each FldrItem in FldrItems
> For x = 0 to 50
> t1 = t1 & Fldr.GetDetailsOf(FldrItem, x) & vbtab
> Next
> t1=t1 & vbcrlf
> ts.Write T1
> T1=""
>Next
>
>
>"Vijayaraj" <Vijayaraj@discussions.microsoft.com> wrote in message
>news:163A2F53-20A3-44BA-B08B-8821353E25D6@microsoft.com...
>> Hi,
>> how to Extract the names of all Files in a folder and to paste it in
>Excel
>> sheet as text
>>
>> Regards,
>> vijay
>>
>
David, david, how may times must you be reminded:

Don't post binaries in this text newsgroup. I didn't want your
potentially deadly vb script file on my hard drive.


Extracting file names to Excel