How to Use VBA Shell
In this lesson we'll learn how to use VBA Shell to open Excel, Outlook, and the Calculator from within Word.
Open a new Word document and press ALT + F11 to open the Editor, a.k.a. the IDE.
Since you may want to make the code we'll write in this lesson, "How to use VBA Shell," permanent, let's create a new Module to hold the Sub procedures.
Right-click on the yellow Modules folder in the Project pane at the upper left and go to Insert... and click on "Module."
in the Properties pane below the Module, change the name from Module1 to RunApps, short for Run Applications.
In the blank code window on the right, type sub OpenExcel and press Enter.
In the blank line between the Sub and End Sub lines, type Shell
Open the Search Window. Go to Start > Search.
Type "excel.exe" in the first field at the top. This time, you can actually use the quotes or not.
Press Search. When you see that the file has been found, you can press the Stop button if you wish.
Click on the Start menu and then on Run... The Run... command is above the start button and possibly to the right. Look in the relative vicinity of the Start button.
Press Del on your keyboard to delete the highlighted text in the field.
Drag the file, Excel.exe, from the search window to the Run field. Windows has filled in the Path to the file in the Run field. We need the full path for the Shell function in order for it to run the Excel application. The path to any file is constructed of everything we would have to double-click on to get to the folder where the file resides, separated by backslashes, followed by another backslash and the file's name.
Since this path is long, the easiest way to copy it is to press the Shift and End keys, Shift + End. If you have trouble doing this drag the mouse over all the text to select it.
To be sure that we are copying all the text, right-click on the highlighted text and click "Cut." If you see any text remaining, you didn't get it all. If so, try again. Go back a few paragraphs if you have to start from the Search window. We'll all wait until you've got it :)
Go back to the Editor and type a space after the word Shell. Right-click after the space and Paste the path or press Ctrl + v to paste it.
Type a comma and you'll see that Intellisense shows you a list of options for the second and last argument of the Shell function. This argument is Optional so we could have stopped before we typed the comma. With the down arrow key, move to the option to have the window of the Excel application opened in a Maximized state and to have it in Focus, vbMaximizedFocus. Focus here means, instead of Excel opening behind Word since we will be pressing a button on the Word toolbar, after Excel opens it will be in the foreground with the focus and Word will be behind it.
When vbMaximizedFocus is highlighted, press the space bar to select it.
Go to the new Word document we opened at the beginning (or open another one).
We’ll now add a button to the toolbar. To do this right click on any toolbar button or grey space in the toolbar. A long menu will appear of all the possible toolbars you could add to Word. Click on the word Customize… at the bottom. The Customize dialog box should be displayed with the middle tab "Commands" selected to the foreground. Scroll down in the list on the left and select Macros. In the left pane you should now see a list of macros you’ve created. Scroll down and find the row with OpenExcel:
Drag the macro to anywhere in any toolbar. The menu bar at the top is also a toolbar. As you progress upward you’ll see an X by the pointer until you clear the dialog box, where upon the X will change to a +. When you are over a place on the toolbar where Word can insert your new button, the vertical insert bar will appear. Release the mouse button to insert the button.
While the Customize dialog box is open you have control over the toolbar buttons. If you right click on the OpenExcel macro button you will see options to change how it is displayed, with or without an image or text, and what image and text are used. On the third line is the Name field. Delete the first part of the name so that only OpenExcel remains, and if you want you can insert a space between Open and Excel, for readability.
We could have added an icon to the button, but there aren't many icons available to choose from. It is possible to add any icon we want with code, but that's not in the scope of this lesson.
Close the dialog and click on the button, the macro should run and Excel will open, maximized and with the focus.
If you like the idea of having a button in Word to open Excel, Outlook, and/or the calculator...
Repeat the above and search for: "outlook.exe"
Repeat the above and search for: "calc.exe"
This site is powered by Site Build It!. If you enjoy it, please check out the
Site Build It homepage to learn more.
Go to the next Tutorial: VBA Label Flash Cards