Excel Hyperlinks Run Command Files
You can use Excel hyperlinks to navigate through a workbook, go to web pages, open other Excel files, and even open PDF files. However, if you try to use an Excel hyperlink to run command files, you'll run into problems. Until now! Here's how J.Woolley uses hyperlinks, with a background macro, to avoid those problems – and a other problems too. He also found a strange hyperlink bug.
Hyperlink Command File Problems
Websites and Excel files open nicely (usually), if you click a hyperlink in a worksheet. With command files, you'll run into issues, such as:
- Dialog boxes appear, and you have to click an OK or Cancel button
- You can't add command line arguments in the hyperlink
- Shell window flashes with a long or complex script file (cmd or vbs)
Bad Address Problems
Even with file types that should open from a hyperlink, you'll see an error message if there is a problem with the address. Here's what appears if I misspell Contextures in a cell, and then click the link that is automatically created.
The message says, "Unable to open http://www.contexturs.com/. Cannot locate the Internet server or proxy server.'
Excel Hyperlinks Run Command Files
To overcome these problems, J. Woolley created a technique that runs command files, by launching a macro when specific types of hyperlinks are clicked.
- If the cell contains text that looks like "RUN::COMMAND", then a macro named ProcessRunCommand runs.
- Normal hyperlinks, without that text, are not affected.
To set up one of these special hyperlinks:
- Insert a hyperlink in a cell (this doesn't work for the HYPERLINK function)
- Change its text to include the RUN::COMMAND text
- Add the worksheet code and ProcessRunCommand to your workbook
Sample Hyperlink for Script
For example, this hyperlink is set up to run a script file, stored in the same folder as the Excel file.
When you click the link, the Excel macro runs the script runs, and opens a window where you enter text
Next, it shows a message with the text that you entered.
While working on this technique, J. Wooley found an odd hyperlink bug. Or maybe it's a feature.
Here's how you can recreate it:
- Insert a hyperlink in a worksheet cell
- Copy the cell with the hyperlink
- Select a few other cells, and paste the hyperlink
- Click on any of the cells where the hyperlink was pasted – the link should work correctly
Everything looks okay, but the next step shows the problem
- Delete any one of those pasted cells
- Try to click on one of the remaining hyperlinks
You can't click on them -- the hyperlink is removed from all of the other cells where the hyperlink was pasted!
Instead of a pointing hand, the mouse pointer is a white plus sign.
Avoiding the Hyperlink Bug
All the pasted cells were treated as a single Hyperlink.Range.Address, instead of having one address per cell. You can see that if you run the following code on that worksheet.
I was able to avoid the problem by also copying a blank cell beside the original hyperlink cell, and pasting that too.
J. Woolley has a few other suggestions for avoiding the problem, and those are in the PDF file that's in the download file.
Get the Excel Hyperlinks Run Command Files
Download the zipped folder from my Contextures website, to get all the details on how this technique works. On the Sample Files page, go to the UserForms and VBA section. Look for UF0038 – Hyperlinks Run Command Files
Unblock the zipped file (see notes below), then unzip the file, and keep all the files in the same folder, for testing.
The zipped folder contains the following Excel Hyperlinks Run Command files:
- Excel workbook with the macros, and hyperlinks for testing.
- Command file for testing
- Script file for testing
- PDF file with notes on the hyperlink bug
- PDF file with setup details and notes
- BAS file with the ProcessRunCommand macro
Unblock the Excel Hyperlinks Run Command Files
Before you unzip the downloaded folder, be sure to unblock the zipped file in Windows Explorer.
- Right-click the zipped file, then click Properties
- Add a check mark in the Unblock box, near the bottom of the Properties window.
- Click OK, to close the Properties window
- The, unzip the folder, and all the files inside will be unblocked, and ready to use.