Open new application VBA

It’s possible to open another program within Excel VBA code and then send commands to manipulate the program. This can be useful when file formats need to be specific. Here’s my example with a line by line breakdown below –

After substituting everything within the speech marks to your chosen filenames\directories etc; The following vba code will copy some selected data open Notepad paste the copied data then save and close Notepad

  Sub OpenNotepad()

ThisWorkbook.Worksheets("WorksheetName ").Activate
Range("A1:G2500").Copy     'Select the data I want to copy and copy it to the clipboard
SendKeys "% n", True     'Minimize Excel
Shell "notepad.exe", vbNormalFocus      'Open Notepad
SendKeys "^V", True      'Paste into notepad from clipboard
SendKeys "%FA", True      'Alt, F, A to File saveas
SendKeys "DirectoryAndNewWorkbookName.txt", True      'Name file
SendKeys "%EUUU", True      'Select Encoding UTF-8
SendKeys "%S", True     'Save
SendKeys "%Y%FX "     'Select yes to save over, then close notepad

End Sub

That's the example now here's the breakdown

The below code will specify a worksheet then copy a selected range of cells

ThisWorkbook.Worksheets("WorksheetName ").Activate Range("A1:G2500").Copy ' Select the data I want to copy and copy it to the clipboard

The next line sends a key command (or key combination command). The true after the comma determines whether the Excel should wait for the key command to be processed before moving onto the next line in your VBA code.

SendKeys "% n", True ' Minimize Excel

The next line uses a shell command to open the new application

Shell "notepad.exe", vbNormalFocus ' Open Notepad

Windows will normally recognise notepad, but with most applications you will need to prefix the application name with it’s path. The ‘vbNormalFocus’ determines how the invoked application opens, vbNormalFocus will open it in it’s most recent size and position and give it ‘focus’. Focus means it will be at the forefront on your screen and Windows will treat it as the ‘active’ program. You can set this variable to open the application without focus or minimized.

The Shell command also has some other optional variables. You can set a wait Boolean (true or false) variable to tell the function whether to wait for your program to complete the command. If omitted this defaults to false.

If you set the wait to true you can set a timeout period ie how long should the shell command wait for. If omitted this defaults to -1 ie it will wait forever.

An example of the Shell command with all the variables set manually is below –

ID = Shell("""C:\Program Files\MyFile.exe"" -a -q", , True, 100000)

Back to the original example now and the next lines show how to paste into notepad from the clipboard. Save the file with the name, formatting and directory you choose, then close Notepad. Just enter the filename (without the directory) if you want to save the text file in the same location as the spreadsheet.

SendKeys "^V", True
SendKeys "%FA", True      ' Alt, F, A to File saveas
SendKeys "DirectoryAndNewWorkbookName.txt", True
SendKeys "%EUUU", True     ' Select Encoding UTF-8
SendKeys "%S", True      ' Save
SendKeys "%Y%FX "      ' Select yes to save over, then close notepad

To make up your key combinations the following characters are used to represent non-character keys

AltKey = "%"
CtrlKey = "^"
ShiftKey = "+"
TabKey = "{TAB}"
EnterKey = "~"

Add a comment