tiki2k said:
Is there a way to catalog the contents of your drive/folders from Windows
Explorer into an Excel sheet? I thought I've seen something like that before
in a Windows Tip involving certain shortcut keys. Or maybe it requires
program to do that.
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(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.
