How to use the System Shell to open files

It is possible by means the Run method associated with the CreateObject function.

  • – The Run method runs a macro or call a function. It can be used to execute a macro written in Visual Basic or the Microsoft Excel macro language, or to perform a function in   a DLL or XLL (the .dll libraries are the Dinamik Link Library).
  • – The CreateObject function creates and returns a reference to ActiveX object.

Let’s see an example where in column A of a worksheet, keep a list of files with images that you can open (.jpg extension) with full path:

 

1

 

Below is reported an example:

 

2

 

With this code as is, you have to select the cell that contains the file to open, and then press a button associated to the routine. The Shell statement used above (CreateObject (“WScript.Shell”)) is used for the opening of applications and related files, the Run method involved the use of Shell.dll Shell32.dll or other libraries, that can be called by VB instructions through the CreateObject function.

So, keeping the control in order to respect the column A and starts after the second line, we get the same thing with the following routine:

3

 

but you can further reduce the instructions by removing control on the “authorized cells”, so any cell in the worksheet can be selected, and only if it contains a complete path. Only activecell we use to identify the selected cell:

4

 

If the active cell is empty, Excel opens the “My Computer” window, but if the cell contains a value that has no valid path, you get a run-time error.

Another example is reported below, you are able to open a file simply by selecting a cell that contains a complete path of file. But we need in this case to “control” the selected cells that belong to an area designed to contain the list of files to be opened, for this scope we use the “Intersect” method, to avoid the activation of some instructions contained in the select cells that do not contain any paths.

A typical application could be a Market sheet where, beside the fields that make up a typically articles archive (part number, description, etc. etc.) you want to insert an “Images” associated with each product; you can with a simple click on the cell that contains the path open the related image:

 

5

 

And here is the routine:

6

This entry was posted in Excel & VBA tips and tagged , , , , , , , , , , , , . Bookmark the permalink.

Leave a Reply