Running Other Applications

In the next chapter of this book, you will learn various methods of launching external applications from Excel. You can add to these methods what you are about to find out in this section.

Suppose you want to start up Windows Notepad from your VBA procedure. The procedure that follows shows you how easy it is to run an application using the WshShell object that is a part of Windows Scripting Host. If you'd rather launch the built-in calculator, just replace the name of the Notepad application with Calc.

Sub RunNotepad()

Dim WshShell As Object Set WshShell = CreateObject("WScript.Shell") WshShell.Run "Notepad" Set WshShell = Nothing End Sub

The above procedure begins by declaring and creating a WshShell object: Dim WshShell As Object

Set WshShell = CreateObject("WScript.Shell")

The next statement uses the Run method to run the required application:

WshShell.Run "Notepad"

Using the same concept, it is easy to run Windows utility applications such as Calculator or Explorer:

WshShell.Run "Calc" WshShell.Run "Explorer"

The last line in the procedure destroys the WshShell object because it is no longer needed:

Set WshShell = Nothing

Instead of launching an empty application window, you can start your application with a specific document, as shown in the following procedure:

Sub OpenTxtFileInNotepad() Dim WshShell As Object Set WshShell = CreateObject("WScript.Shell") WshShell.Run "Notepad C:\Phones.txt" Set WshShell = Nothing End Sub

To launch the MS-DOS window and print out the list of files in the current directory, try the following procedure:

Sub RunDOSCommand()

Dim WshShell As Object

Set WshShell = CreateObject("WScript.Shell") WshShell.Run ("Command /c Dir >lpt1:") End Sub

0 0

Post a comment