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.’

Hyperlink to Open 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.

Hyperlink Bug

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 Hyperlink Sample Files

Download the sample file 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:

• 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 Files

Before you unzip the downloaded folder, be sure to unblock the zipped file in Windows Explorer.

1. Right-click the zipped file, then click Properties
2. Add a check mark in the Unblock box, near the bottom of the Properties window.
3. Click OK, to close the Properties window
4. The, unzip the folder, and all the files inside will be unblocked, and ready to use.

Comments or Questions

If you have questions or comments about the Run Command Files technique, contact J. Woolley.

_________________