Set Priority with Shell command in VBA

[PennStateBrian]

I have a resource intensive excel spreadsheet and I'm trying to make it run cleaner for my end users. One idea I had was to increase the priority windows gives excel when this speadsheet is open. I'm close to getting what I want, but am stuck on the code. Right now I have the below.

Private Sub Workbook_Open()
Shell (cmd /c start /high excel.exe)
End Sub

The problem is, this starts a new excel document at a high priority, but does not change the current environment. Another option is to have the shell call a batch file, but because this is for end users, I want to keep it hardcoded as to reduce the number of files that need to be sent.

 

[PennStateBrian]

bump

 

[Legacy 98055]

I don't know how to do it by way of the command line but I know you can do this using WMI. I'll look into it for you and get back if a few...

 

[Legacy 98055]

Found some infor here: http://msdn.microsoft.com/en-us/library/aa393587(VS.85).aspx

Note that you can use realtime which is higher but you will have to set permissions to do so...

Sub SetPriority()

    Const ABOVE_NORMAL = 32768
    Const HIGH = 128
    
    strComputer = "."
    Set objWMIService = GetObject("winmgmts:" _
        & "{impersonationLevel=impersonate}!\\" _
        & strComputer & "\root\cimv2")
    Set colProcesses = objWMIService.ExecQuery _
        ("Select * from Win32_Process Where Name = 'excel.exe'")
    For Each objProcess In colProcesses
        objProcess.SetPriority (HIGH)
    Next
    
End Sub

 

[Legacy 98055]

By the way Brian, you could fine tune this a bit more by determining which instance of Excel you wish to apply the higher priority by using its process id in the wql statement... As is, all running instances of Excel will recieve a high priority... See WMI reference at MSDN.

<A HREF="http://msdn.microsoft.com/en-us/library/aa394582(VS.85).aspx" TARGET="_blank">MSDN: Windows Management Instrumentation</A>

 

[keen2xl]

Good Stuff Tom

U Da Man works perfect

 

[Dimitris254]

i'm bringing this thread back from the dead, cause i'm in the same situation

i'm using a macro to copy a lot of charts as images from Excel and paste them to PowerPoint. Currently not all cpu & ram power is used and i'd like to do so.

i tried copying the sub Tom quoted, but i'm using Option Explicit and it asks to define the variables. Any help on that?

or, if there has been another solution since 2009, i'm all ears


ps: i'm using Excel 2010

 

[KenVarl]

i'm bringing this thread back from the dead, cause i'm in the same situation

i'm using a macro to copy a lot of charts as images from Excel and paste them to PowerPoint. Currently not all cpu & ram power is used and i'd like to do so.

i tried copying the sub Tom quoted, but i'm using Option Explicit and it asks to define the variables. Any help on that?

or, if there has been another solution since 2009, i'm all ears


ps: i'm using Excel 2010

Hi

We are now in 2018 and I assume that the old thread of 2016 has been resolved. However, I came across this yesterday and have resolved the previous question

Just add the following line into the subroutine and it works.

Dim strComputer, objWMIService, colProcesses, objProcess

I have a dashboard that calls all other spreadsheets as I require them. It is a full accounting package. When I open the dashboard, there is a macro called AUTO_OPEN. This runs automatically when the workbook is opened. It calls the SetPriority code below, and therefore automatically sets the priority to high every time that I open Excel.

Hope it helps someone


This is the full code:

'----------------------------------------------------------
Sub AUTO_OPEN

SetPriority

End Sub
'----------------------------------------------------------
Sub SetPriority()
'copied from https://www.mrexcel.com/forum/excel-questions/438142-set-priority-shell-command-vba.html
'dim line added by kjv

Const ABOVE_NORMAL = 32768
Const HIGH = 128

Dim strComputer, objWMIService, colProcesses, objProcess

strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!" _
& strComputer & "\root\cimv2")
Set colProcesses = objWMIService.ExecQuery _
("Select * from Win32_Process Where Name = 'excel.exe'")
For Each objProcess In colProcesses
objProcess.SetPriority (HIGH)
Next

End Sub

'-----------------------------------------------------

 

[KTo]

Being self-trained, I know I regularly miss some details myself. If I mention something that is untrue, forgive me. May be Ken’s system allowed the non-declared names. Sifting through what was needed and what was not; removed unnecessary code, this worked on my Office 2016 version.

Sub Workbook_Open() ‘ Goes in ThisWorkbook portion of the VBAProject
Call SetPriority
End Sub



Sub SetPriority() ‘ goes in to a module.
Const HIGH = 128
Dim strComputer As String
Dim objWMIService As Object, colProcesses As Object, objProcess As Object

strComputer = "."
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colProcesses = objWMIService.ExecQuery _
("Select * from Win32_Process Where Name = 'excel.exe'")
For Each objProcess In colProcesses
objProcess.SetPriority (HIGH)
Next

Set objWMIService = Nothing
Set colProcesses = Nothing
Set objProcess = Nothing
End Sub


Note: if Ken’s “Autpen” sub was to load this code when he opened the file, the “Autpen” code should be named “Workbook_Open” and go in to the ThisWorkbook portion of the VBAProject; while the SetPriority() code should go in to a module, as noted above.