EXPERIMENT 002

EXPERIMENT 002: Invoking of 'SHELL' function; shows exact address of an Excel file on one of the cells on one of the sheets AND opens the folder of current location of this file as the file loads.

The Shell Function: This function is well equivalent to 'Run' item on the Windows start menu. This VBA Shell function can be used to start an external program or perform any operation. This page describes a procedure named mRunCal that will call the Shell function and then invoke the executive file of Calculator from Windows.

Public Sub mRunCal()

Dim aShell

aShell = Shell("calc.exe")

'The function will search the executive file of calculator,

'otherwise you may give complete address of the file also.

End Sub

Assign this procedure to any command button on excel sheet. This button when clicked, will run the calculator.

You can run any programme on excel sheet using this function.

Given below are two formulae. Copy and paste these two in two different cells. But before doing it, your file must have been saved.

1. This will display the address of current location of the file.

=MID(CELL("filename"),1,FIND("[",CELL("filename"))-1)


2. This changes into a hyperlinked text and when clicked, opens the folder in which the file lies.

=HYPERLINK(MID(CELL("filename"),1,FIND("[",CELL("filename"))-1),"CURRENT LOCATION")

If you want, you can lock this file, so that no body has opportunity of seeing the formula.