Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Excel macro from XP shortcut menu
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Excel macro from XP shortcut menu
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" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Excel macro from XP shortcut menu
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" wrote: 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" wrote in message ... 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Excel macro from XP shortcut menu
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 "hmm" wrote in message ... 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" wrote: 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" wrote in message ... 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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Excel macro from XP shortcut menu
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 " __________________________________ 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 _________________________________ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Excel macro from XP shortcut menu
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. "Steve Yandl" wrote: 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 "hmm" wrote in message ... 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" wrote: 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" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
cannot right-click a shortcut menu in Excel | Excel Discussion (Misc queries) | |||
Help with shortcut menu in Excel | Excel Discussion (Misc queries) | |||
Macro menu item versus keyboard shortcut on pivot table | Excel Programming | |||
hyperlink greyed out on shortcut menu in Excel | Excel Discussion (Misc queries) | |||
how can you customize a shortcut menu in excel? | Excel Discussion (Misc queries) |