Excel UDF - Many Instance Running

[Sly01]

Hi Everyone - My First post here - Please do let me know if I am at the right spot to ask questions ;)

I will most likely have a lot of questions in the new weeks... My first question I have... 

We are running a machine that we need to keep Excel running as "clean" as possible, what I mean "clean" is that not many instances must be running (for memory leak), that no workbooks stays open if not meant to be open (in case of error messages Book1 open, then book2 etc...).

I am almost getting there to make it work... But now I have a problem that Excel runs more than 1 instance... So the current script that I have doesn't work...

If I run OpenExcel() on it's own... It Works... If I open more than 2 instances of Excel my TooManyProcesses will kick in and kill the process (the reason I run the check twice is because I need to give a chance to a "current" job running to complete before forcing shutdown of all processes)...

If I run OpenExcel() and there would be 2 processes open, which normally would be 1 Excel running "empty" and then a second one with a Book1 opened (after an error message) is there a way to tell _Excel_Open which Instance to get?

Also, if I can decide which instance I'm looking at, I will also be able to get rid of my TooManyProcesses as I will be dealing with different instances instead

 

expandcollapse popup

Func TooManyProcesses($ProcessName, $MaxAmout)
    Local $aProcessList = ProcessList($ProcessName) ;~ Process Array of example.exe
    If $aProcessList[0][0] > $MaxAmout Then
        Sleep(5000)
        Local $a2ndProcessList = ProcessList($ProcessName) ;~ Process Array of example.exe
        If $a2ndProcessList[0][0] > $MaxAmout Then
            For $i = 1 to $a2ndProcessList[0][0]
                WinClose($a2ndProcessList[$i][1])
                Sleep(10)
                ProcessClose($a2ndProcessList[$i][1])
            Next
        EndIf
    EndIf
EndFunc

func OpenExcel ()

; Detect if any Excel is running - Otherwise - Launching and closing current workbook
    if Not ProcessExists ("EXCEL.EXE") Then
        Local $oExcel = _Excel_Open()
        Local $oWorkbook = _Excel_BookNew($oExcel)
        _Excel_BookClose($oWorkbook, False)
        WinMove("Excel", "", 71, 71, 350, 350 )
    EndIf

; Checking if more then one process is running for EXCEL.EXE
    TooManyProcesses("EXCEL.EXE", 2)
    Sleep(1000)

; If Excel is running With new Empty Workbooks Then Closing the new workbooks
    Local $oExcel = _Excel_Open()
    Local $aWorkbooks = _Excel_BookList($oExcel)
    if UBound($aWorkbooks) > 2 Then
        _ArrayDisplay($aWorkbooks)
        for $i = 0 to UBound($aWorkbooks) - 1
            if ($aWorkbooks[$i][2] == "") Then
                Local $oWorkbook = _Excel_BookAttach($aWorkbooks[$i][1])
                _Excel_BookClose($oWorkbook, False)
            EndIf
        Next
    EndIf

; If Excel Running Normally, bring to the front and resize
AutoItSetOption("WinTitleMatchMode", 3)
    if WinActivate ("Excel") Then
        Local $oExcel = _Excel_Open()
        WinMove("Excel", "", 71, 71, 350, 350 )
    EndIf
EndFunc

 

[water]

_Excel_Booklist without a parameter returns an array holding all open workbooks for all Excel instances.
If you can tell which open workbook should be kept it is easy to shutdown all other Excel instances.

[Subz]

You use Excel object to resize for example:

Func _ResizeExcel()
    Local $oExcel = _Excel_Open(False)
        $oExcel.Application.ActiveWindow.WindowState = -4143
        $oExcel.Application.Top = 76
        $oExcel.Application.Left = 76
        $oExcel.Application.Width = 350
        $oExcel.Application.Height = 350
EndFunc

You can also enable merging instances into one instance using:

;~ Excel 2016 and above
;~ Enable merging instances (Shift+Excel shortcut) into one instance
RegWrite("HKCU\Software\Microsoft\Office\16.0\Excel\Options", "DisableMergeInstance", "REG_DWORD", 0)

;~ Disable merging instances (Shift+Excel shortcut) into one instance
;~ RegWrite("HKCU\Software\Microsoft\Office\16.0\Excel\Options", "DisableMergeInstance", "REG_DWORD", 1)

You can remove Bookxx workbooks using:

Func _GetBookList()
    Local $aWorkbooks = _Excel_BookList()
        If @error Then Return
    For $i = 0 To UBound($aWorkbooks) - 1
        If StringLeft($aWorkbooks[$i][1], 4) = "Book" And $aWorkbooks[$i][2] = "" Then _Excel_BookClose($aWorkbooks[$i][0])
    Next
EndFunc