Jump to content

Excel UDF - Many Instance Running


Recommended Posts

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

 

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

 

Link to post
Share on other sites

_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.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to post
Share on other sites

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

 

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...