Last year, J. Woolley shared his technique to run command files from Excel hyperlinks. He has improved how this works, and now you can get his latest version of the files. It has a SuperLink function too, that is better than Excel's HYPERLINK function.
Original Run Command Files From Excel
Click here to see the original article, that describes how the first version works, and why J. Woolley created it, to make it easy to run command files from Excel.
New and Improved Version
Since I posted that article, J. Wooley has made significant improvements to this technique. For example:
- RUN::COMMAND can be included in a hyperlink cell's text value or in its ScreenTip (or both)
- Multiple RUN::COMMANDs can be processed for a single hyperlink cell
- Defining the path for each RUN::COMMAND is now simplified
J. Wooley also developed a new user-defined function (UDF) named SuperLink. This function supports RUN::COMMAND , and it can be used independently too, as a better way to create hyperlinks. (The SuperLink UDF details are described further down in this article.)
Get the Run Command Files
To get started, click this link to get the zipped folder with the Run Command files, and download it to a folder on your computer. RunCommand.zip contains the following files:
Install the Run Command Files
First, before you unzip the file, you will probably need to unblock the downloaded file using its Properties dialog box.
If you don't unblock the file, the RUN::COMMAND technique might appear to work, but it will not work correctly.
Then, after the downloaded file is unblocked, unzip all files to a single folder. The TestRunCommand.xlsm workbook assumes it is in the same folder as the other files.
Use the Run Command Files
After the files are unzipped, open TestRunCommand.xlsm in Excel; you will probably need to Enable Content for Macros.
- NOTE: The files named M_ProcessRunCommand.bas and M_SuperLink.bas have already been imported as VBA Modules. in TestRunCommand.xlsm
On the Tests sheet, there is an explanation in cells C1:D6 and there are 3 columns with hyperlinks:
- B – Standard Hyperlinks
- C – links created with Excel's HYPERLINK function
- D – Standard hyperlinks with RUN::Commands
Use the hyperlinks in column D to test the RUN::COMMAND technique -- the hyperlink text explains what each link does.
Excel HYPERLINK Function Issues
Excel's HYPERLINK function lets you create links, but it has several shortcomings:
- It does not create an Excel Hyperlink object.
- It does not populate the worksheet’s Hyperlinks collection.
- It does not trigger the worksheet’s FollowHyperlink event.
- It returns a “shortcut” that looks like a text string but should not be treated as one.
- A cell using the HYPERLINK function has a default ScreenTip that cannot be changed.
- The Insert > Link (or Ctrl+K) dialog is not available for a cell containing the HYPERLINK function.
- If a cell with a Hyperlink object defined using Ctrl+K is edited to add the HYPERLINK function, that HYPERLINK “shortcut” will be ignored in favor of the original Hyperlink object.
SuperLink User Defined Function
The SuperLink UDF, that J. Woolley created, resolves those HYPERLINK function issues.
On the Test sheet of the TestRunCommand.xlsm workbook, there is a section with examples for the SuperLink function, and an introduction in merged cells A19:D21.
For a detailed explanation of the User Defined Function, read the SuperLink PDF file that is included in the download folder. You can open that file with Test Number 12 link, on the Tests sheet.
To test the SuperLink UDF, use the links that are set up on the Test sheet, in cells B23:D29. Those formulas show how the SuperLink UDF can be used to support application the updated RUN::COMMAND technique.
Questions or Comments
If you have questions or comments about the Run Command Files technique, post in the Comments section below, and J. Woolley will try to help.