Run Excel macro from XP shortcut menu

[hmm]

I want to create an Excel macro that would be activated by the user
right-clicking on folder in Windows Explorer. How would I even begin to
learn how to arrange such a shortcut activation?

 

[Steve Yandl]

To make an item appear in the Windows Explorer shortcut (context or right
click) menu for folders, you would create a new registry key under
HKEY_CLASSES_ROOT\Folder\shell using the name you want your user to see.
Then you create a subkey under that new subkey named 'command' and give it a
default value that is the command line you want executed. The problem with
this approach is that it can cause quirky behaviors with the Explore and
Open options already present by default. You also have to figure out how to
let your Excel macro know which folder you clicked on because, presumably,
you want the macro to do something with that folder or its contents.

I'm guessing that you want a macro that will display folder contents or
other folder information in a spreadsheet. To do something like that, I
find it works best to use a vbScript helper file to launch Excel and run the
macro because you can then use the 'Arguments' property of the 'WScript
Object' to capture the full path to the folder and feed that to your macro.
Place the vbScript or a shortcut to it in the SendTo folder and then the
user can locate the helper file by right clicking the folder and scanning
the SendTo flyout options from the context menu.

Steve

 

[hmm]

Thanks, Steve, for your detailed answer.

I understand you do not recommend using the Windows registry for the task.

Regarding the vbScript approach, I have to admit I don't know enough about
it. You are correct in your assumption: I want the macro to find all files
of a certain type, and present their data graphically using a particular
template. Presently, the template is duplicated for each folder, rather
inefficient since it is a file of several hundred kB. In fact, the only
reason I save it is so that I don't have to remember and enter the folder
path every time. If I can simply right-click on a folder or a control button
and get its path into the designated cell in the template, then I'm in
business! One other detail, whatever method is used I must be able to
distribute to co-workers who are less computer savvy than (even?) I am.
That's it in a nutshell.

Any insights to lead me down the the most straightforward path would be
appreciated.

 

[Steve Yandl]

Here is an example to get you started.

For the example, create a folder on your system named "C:\Test". Open a new
Excel workbook and save it to that folder, naming it "C:\Test\myBook.xls".
Create a new subroutine with what I've got between the lines immediately
below:

__________________________________

Sub FileList(strPath As String)
Cells(1, 1).Value = strPath
End Sub
__________________________________

The sub above simply takes a string argument and places it in Cell "A1".
You're going to be doing something more elaborate but this will get you
going.

Go to the 'Start > Run' line, type
SendTo
and then press Enter. This should open Windows Explorer to your SendTo
folder. Before creating a new text document here, click 'Tools > Folder
Options' and then click the View tab so you can be sure that you don't have
a check mark in the box saying "Hide Extensions for Known File Types"
(remove the check if you do). Now right click a vacant area in the folder
and opt to create a new text document here. Paste what I've got between the
lines below and then save the file with a name that includes the file
extension vbs (you don't want the default txt extension or the vbScript will
act like a text file)

__________________________________

Set fso = CreateObject("Scripting.FileSystemObject")

If fso.FolderExists(WScript.Arguments.Item(0)) Then
Set objXL = CreateObject("Excel.Application")
objXL.Visible = True
objXL.Workbooks.Open("C:\Test\myBook.xls")
strCmdMacro = "FileList(" & Chr(34) & WScript.Arguments.Item(0) & Chr(34)
& ")"
objXL.Run(strCmdMacro)
Else
WScript.Quit
End If
_________________________________

Now, if you open Explorer, right click a folder and click "SendTo> whatever
you named your vbs file", your Workbook "C:\Test\myBook.xls" will open and
the path to the folder you were right clicking will be written to A1.

You can also modify the script to save the changes to the workbook and close
it as well as close Excel but initially this makes things easier to observe.
I generally place my vbs files in a script folder and place shortcuts to
them in the SendTo rather than the vbs file itself but didn't want to add
too many steps to the project right off the bat.


Steve Yandl

 

[Steve Yandl]

Note for the example above. The line

strCmdMacro = "FileList(" & Chr(34) & WScript.Arguments.Item(0) & Chr(34)
& ")"

needs to all be on a single line in the script. The line got split in my
post.

Steve


">

 

[hmm]

Thanks, Steve, for your tremendous help.

You've given me a lot to start learning with; for example, I had never heard
of the SendTo menu before. Now with your help and all the MS help menus, MS
sites, etc., I can learn how to implement your examples and create my
application.

Thanks again.