I need to create a spreadsheet with a list of folders (in A2 and below plus
other information) and start Windows Explorer to a specific folder (relevant
to the current cursor row) from within the sheet, at the click of a button.

My main problem is how to "call" the Windows Explorer with an initial folder
parameter (ok, that's easy I think) from the button code.

Thanks

Try something like

Shell "explorer.exe c:\somefolder"


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"DoctorG" wrote in message
...
I need to create a spreadsheet with a list of folders (in A2 and
below plus
other information) and start Windows Explorer to a specific
folder (relevant
to the current cursor row) from within the sheet, at the click
of a button.

My main problem is how to "call" the Windows Explorer with an
initial folder
parameter (ok, that's easy I think) from the button code.

Thanks

If your folder name has spaces, use something like

Shell "explorer.exe ""C:\some folder"""


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Chip Pearson" wrote in message
...
Try something like

Shell "explorer.exe c:\somefolder"


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"DoctorG" wrote in message
...
I need to create a spreadsheet with a list of folders (in A2
and below plus
other information) and start Windows Explorer to a specific
folder (relevant
to the current cursor row) from within the sheet, at the click
of a button.

My main problem is how to "call" the Windows Explorer with an
initial folder
parameter (ok, that's easy I think) from the button code.

Thanks

Hi DoctorG

Try this

Shell "explorer.exe C:\Data\", vbNormalFocus


--
Regards Ron de Bruin
http://www.rondebruin.nl


"DoctorG" wrote in message ...
I need to create a spreadsheet with a list of folders (in A2 and below plus
other information) and start Windows Explorer to a specific folder (relevant
to the current cursor row) from within the sheet, at the click of a button.

My main problem is how to "call" the Windows Explorer with an initial folder
parameter (ok, that's easy I think) from the button code.

Thanks

Thank you both. Ron's addition was helpful as well since the window opened
but got no focus.

Is there an easy way (??!!) to traverse a folder structure and place the
results (filenames) in a vertical range?

"DoctorG" wrote:

I need to create a spreadsheet with a list of folders (in A2 and below plus
other information) and start Windows Explorer to a specific folder (relevant
to the current cursor row) from within the sheet, at the click of a button.

My main problem is how to "call" the Windows Explorer with an initial folder
parameter (ok, that's easy I think) from the button code.

Thanks

You may not even need Windows Explorer. You just need a tiny .bat file to:

1. change directory to the one of interest
2. do something like dir *.* file.txt

The .bat file can also be executed by a SHELL command
One the .txt file is created, then the VBA code can directly open and read it.
--
Gary''s Student


"DoctorG" wrote:

Thank you both. Ron's addition was helpful as well since the window opened
but got no focus.

Is there an easy way (??!!) to traverse a folder structure and place the
results (filenames) in a vertical range?

"DoctorG" wrote:

I need to create a spreadsheet with a list of folders (in A2 and below plus
other information) and start Windows Explorer to a specific folder (relevant
to the current cursor row) from within the sheet, at the click of a button.

My main problem is how to "call" the Windows Explorer with an initial folder
parameter (ok, that's easy I think) from the button code.

Thanks

This might actually work. If I can get to understand Jim's code though it
will be better for my Excel knowledge. I am quite familiar with DOS and batch
files since my early days. If I can manage this the new way it will be best.

Thanks

Here is some code for you to try. You need to reference it to the "Microsoft
Scripting Runtime". In the VBE Tools - References and check the appropriate
item.

Sub ListFiles()
'Must reference "Microsoft Scripting Runtime" library

Dim fso As New FileSystemObject
Dim oCurrentFile As File
Dim oCurrentFolder As Folder
Dim wks As Worksheet
Dim rng As Range

Set wks = Sheets("Sheet1")
Set rng = wks.Range("A2")

Set oCurrentFolder = fso.GetFolder("C:\")
On Error Resume Next
For Each oCurrentFile In oCurrentFolder.Files
rng.Value = oCurrentFile.Name
rng.Offset(0, 1).Value = oCurrentFile.ShortName
rng.Offset(0, 2).Value = oCurrentFile.Path
rng.Offset(0, 3).Value = oCurrentFile.DateCreated
rng.Offset(0, 4).Value = oCurrentFile.DateLastAccessed
rng.Offset(0, 5).Value = oCurrentFile.DateLastModified
rng.Offset(0, 6).Value = oCurrentFile.Size
rng.Offset(0, 7).Value = oCurrentFile.Type
rng.Offset(0, 8).Value = oCurrentFile.Attributes
Set rng = rng.Offset(1, 0)
Next oCurrentFile
End Sub

--
HTH...

Jim Thomlinson


"DoctorG" wrote:

Thank you both. Ron's addition was helpful as well since the window opened
but got no focus.

Is there an easy way (??!!) to traverse a folder structure and place the
results (filenames) in a vertical range?

"DoctorG" wrote:

I need to create a spreadsheet with a list of folders (in A2 and below plus
other information) and start Windows Explorer to a specific folder (relevant
to the current cursor row) from within the sheet, at the click of a button.

My main problem is how to "call" the Windows Explorer with an initial folder
parameter (ok, that's easy I think) from the button code.

Thanks

Jim,

Your code has done most of what I need to do, which is to read the file
contents of a folder. Yet it doesn't "see" folders. I guess I haven't been
able to understand yet (I hope) the object-class-method approach. Perhaps if
I see the modifications to handle folders I can make the connection.

Thanks again for your help

Thanks everyone, consider this closed. I just bumped on "Scan A Directory",
dated 9/9/2005 which deals with exactly the same matter. No need to say the
same things over and over.