How to use shell command in VBA Excel

How to use shell Function in VBA Excel

Starting an Application from Excel

It is often useful for launching an application from Excel VBA. For Example, we want to execute another Microsoft Office Application or even DOS batch file from Excel then we can use Shell Function in VBA.

Using the VBA Shell function

The VBA Shell function makes easier to launch other programs.

Shell( PathName [,WindowStyle)

The Shell function syntax has two arguments:

  • PathName:This is a required argument so we have to give this argument. We have to give Name of the program as a pathname to execute. The pathname is a variant (string) data type.

 

  • WindowStyle:This is optional arguments for Shell Function. WindowStyle is the corresponding style of window in which the program is to be run. WindowStyle is a variant (Integer) data type.

The WindowStyle named argument has these types of values and with the description:

  • vbHide:When Window is hidden then focus is passed to the hidden window. We put 0 for this constant.
  • vbNormalFocus: When Window has focus then it is restored as to its original size and position. We put 1 for this constant.
  • vbMinimizedFocus:When Window is minimized then it is displayed as an icon with focus. We put 1 for this constant.
  • vbMaximizedFocus:When Window is maximized then it is displayed with focus. We put 1 for this constant.
  • vbNormalNoFocus:We see that Window is restored as its most recent size and position. The currently active window remains active. We put 1 for this constant.
  • vbMinimizedNoFocus:Window is displayed as an icon. The currently active window remains active. We put 1 for this constant.

Following is an example of VBA code that launches the Windows Calculator application.

Shell function open a Notepad.exe with a Maximized screen

When we this run code then Notepad application shows as full screen.

Sub Shell_Notepad()

    On Error Resume Next

    Dim Program As String

    Dim TaskID As Double

    Program = “Notepad.exe”

    TaskID = Shell(Program, vbMaximizedFocus)

    If Err <> 0 Then

        MsgBox “Cannot Start ” & Program, vbCritical, “Error”

    End If

End Sub

 

Shell function open a Notepad.exe with Minimized screen

When this we run code then Notepad application shows as a minimized screen.

Sub Shell_Notepad()

    On Error Resume Next

    Dim Program As String

    Dim TaskID As Double

    Program = “Notepad.exe”

    TaskID = Shell(Program, vbMinimizedFocus)

    If Err <> 0 Then

        MsgBox “Cannot Start ” & Program, vbCritical, “Error”

    End If

End Sub

 

We launch a calculator from VBA Shell function

Shell function open a Calc.exe

Sub Shell_Calculator()

    On Error Resume Next

    Dim Program As String

    Dim TaskID As Double

    Program = “Calc.exe”

    TaskID = Shell(Program, vbMaximizedFocus)

    If Err <> 0 Then

        MsgBox “Cannot Start ” & Program, vbCritical, “Error”

    End If

End Sub

Paste data into Notepad using Shell Function

When we run this code then excel data paste into Notepad file.

Sub Paste_Data_Notepad()

    On Error Resume Next

    Range(“A1”).CurrentRegion.Copy

    VBA.Shell “Notepad.exe C:\Users\AKASH\Desktop\New Text Document.txt”, vbMaximizedFocus

    VBA.SendKeys “^v” ‘Paste in Notepad

    VBA.SendKeys “^s” ‘Save Notepad File

End Sub

Note:

“^v” this is denote as Ctrl+V

“^s” this is denote as Ctrl+S

How to use shell command in VBA

How to use shell Function in VBA

How to run a shell command in VBA

Leave a Reply

Your email address will not be published. Required fields are marked *