Felix said:
I know how to print a "Directory Listing" but instead I would like to copy
and paste it into Excel. All I want from the listing are the files' name.
Right now I print the listing and key in the name of the files in a
spreadsheet. This is very time consuming and subject to errors.
Can I copy the "Directory Listing" and paste it into an Excel spreadsheet?
If so how?
I am using Windows XP SP 2 Home
Thank you.
Two methods.
Method one:
Start->Run->cmd<enter>
CD "C:\Documents and Settings\someusername\somedirectory"<enter>
or whatever path you need to supply to get to the desired directory,
with the
quotes if you have a space anywhere in the path.
DIR<enter>
Right mouse click in the window and a little menu pops up.
Slide the mouse over the "Mark" entry and left click.
Then the highlighting disappears.
Position mouse at upper left corner of file name text.
Left button down and drag to lower right corner and release.
(repeat if needed until you have the names highlighted,
the window can be resized if need be to get the names all in)
Tap the enter key, highlighting disappears, names are in clipboard.
Switch to Excel sheet, click in cell, Control V or Edit->Paste
and you have all your names in your cells.
Method two:
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
Replace all of those two lines with this:
Function MyDir(VIn1 As Variant) As Variant
Dim vaResult(20) 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 twenty cells in a horizontal row of cells,
by depressing the left mouse button in the left most cell
and dragging horizontally across the twenty 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.
