Macro to open Folder but not file?

Hi
Why not have the macro delete the files as well?

Provide some parameters as to what is being deleted.

At least allow the macro to delete the files with a user prompt (YES/NO)...

Tom

You can open a folder using the following:

Shell ("c:winntexplorer.exe c:winnt")

where c:winnt is the folder you want to open.

Thanks, the shell command works but it opens the folder as a tab. Is there a way of getting in to open as a display screen.

try:
Shell ("c:winntexplorer.exe /e,c:winnt")

Thanks.

That brings up the complete list of folders and file names. What I'm really after is the display that just shows the contents of the opened folder. I can live with just a tab but the display would be preferable.

you can open the folder how you want if you assign the shell command to a variable e.g.

myval = Shell("c:winntexplorer.exe c:winnt", 1)

the , 1 tells it to open as normal, 0 will hide it, 2 is minimized, 3 is maximised, 4 is normal with no focus and 6 is minimised without focus. These can be replaced with the usual vb commands of vbNormalFocus etc

You don't have to do anything with the variable, just have it assigned. The variable it returns is the programs task ID and is the main purpose of the shell command.

Hope this clears it up.

Regards

Nick

Nick, thank you.

As a beginner, could you tell me how I can place this within the macro to make it work.

Thanks again.

Jon
This message was edited by Zorax on 2003-01-17 16:30

Hi Zorax,

it depends how you want to trigger it. Maybe the best way would be to add a button onto the worksheet for the user to click. To do this, choose Customize from the tools menu and put a tick in the box next to Control Toolbox.
You should have another toolbar now so choose the command bar button on this toolbar(looks like a small grey rectangle) and drag the mouse whilst holding the left mouse button somewhere on your worksheet to the size you want the button to be.

Right click on the button and choose properties so you can alter how it looks. The property that reads Caption should be changed from CommandButton1 to whatever you want it to read e.g. Open Folder.

Close the properties box with the x in the top right hand corner and then double click the button. This should bring up the VBE at the point where you can post the code in. It should read as follows

Private Sub CommandButton1_Click()
myval = Shell("c:winntexplorer.exe c:winnt", 1)
End Sub

Then close the VBE and click the button with the set square, ruler and pencil which exits out of design mode on the control toolbar. Now whenever you click the button the folder will open.

Another way would be to include the question and use an IF statement in your code. For instance:

Sub OpenFolderRequest()
YesNo = MsgBox("Would you like to open the folder to see" _
& vbCr & "which files are currently there?", vbYesNo + vbQuestion, "Open Folder?")
Select Case YesNo
Case vbYes
myval = Shell("c:winntexplorer.exe c:winnt", 1)
Case vbNo
End Select
End Sub

You can then use CALL OpenFolderRequest anywhere in your code to ask the user if they want to open it the folder.

Hope this helps.

Best Regards

Nick

Nick, thank you, the second example is ideal and works perfectly.

I would now like to take this a stage further!

Is it possible to add some code that will select all files within the folder and email them to a specified address?

I know that there have been numerous postings on the subject of emailing and I have just spent 45 minutes reading some of them, but I still can't work out how to achieve this (from a beginners perspective!).