Close the entire application of excel

[Queener]

I notice when I used this code below, it doesn't exit the excel application, rather it only close the worksheet leaving the excel open. Any suggestion?:

_Excel_BookClose($oWorkBook, False)

 

 

[MikahS]

$oExcel.Quit()

or

$oExcel.Application.Quit

Edited December 4, 2014 by MikahS

[water]

_Excel_Close?

[alienclone]

_Excel_Close?

 

when i use _Excel_Close it still leaves the process running in the background. in most cases that wouldnt be an issue, but i have a script that opens a file in Excel, gets info, closes the file, THEN i need to open the same file with LibreOffice to convert it to PDF (because Excel does a crappy job when it comes to saving as PDF) but the file is still "in use"

my heavy handed solution to this was 

_Excel_Close($oApp)
ProcessClose("Excel.exe")

but i will also try what MikahS suggested.

[water]

Can you please post your script so we can reproduce the problem? When working with the Excel UDF ist shouldn't be necessary to kill the application at the end.

Edited December 5, 2014 by water

[Queener]

Code:

Func NoChanges()
    $filepath = @ScriptDir & "\Files\JANPRO_REPORT.xlsx"
    if Not FileExists($filepath) Then
        MsgBox(0, "Error", "Report file does not exist")
    Else
;------------------------------------> Code goes below:


$tCur = _NowDate()

$sWorkBook = _Excel_Open()

$oWorkBook = _Excel_BookOpen($sWorkBook, $filepath)

$oExcel = _Excel_BookAttach($oWorkBook, False)

WinWaitActive("JANPRO_REPORT.xlsx - Excel", "", 1000)

_Excel_RangeWrite($oWorkBook,"REPORT",$tCur,"D1",True)
;_Excel_BookSaveAs($oWorkBook,@DesktopDir & "\JanPro_REPORT.xlsx", "xlsx",0,1)
;If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookSave Example 1", "Error saving workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
;MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookSave Example 1", "Workbook has been successfully saved as '" & @DesktopDir & "\JanProReport.xlsx'.")


_Excel_BookClose($oWorkBook, False)

    EndIf
;------------------------------------> End of Code

    Return ;Must use this code at the end of code
EndFunc

I also need help on saveas... I get this error base on saveas:

 

For app close; I used:

$oExcel.Quit()

Thanks to MikahS

Edited December 5, 2014 by asianqueen

[water]

I think mixing BookOpen and BookAttach causes the problems. BookAttach is to be used when you want to attach to an already open workbook, BookOpen to open a new workbook.

Func NoChanges()
    $sFilepath = @ScriptDir & "\Files\JANPRO_REPORT.xlsx"
    If Not FileExists($sFilepath) Then Return MsgBox(0, "Error", "Report file does not exist")

;------------------------------------> Code goes below:

$tCur = _NowDate()
$oExcel = _Excel_Open()
$oWorkBook = _Excel_BookOpen($oExcel, $sFilepath)
_Excel_RangeWrite($oWorkBook, "REPORT", $tCur, "D1", True)
_Excel_BookSaveAs($oWorkBook, @DesktopDir & "\JanPro_REPORT.xlsx", Default, 0, 1)
If @error Then Return MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookSave Example 1", "Error saving workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookSave Example 1", "Workbook has been successfully saved as '" & @DesktopDir & "\JanProReport.xlsx'.")
_Excel_BookClose($oWorkBook, False)

;------------------------------------> End of Code

    Return ;Must use this code at the end of code
EndFunc

SaveAs has two problems. Parameter 3 is an integer, not a text ("xlsx").
Problem #2: Do you really want to set the password to "1"?
Please have a look at the help file for proper parameter description.

Edited December 5, 2014 by water

[MikahS]

Thanks to MikahS

 

My pleasure.

[Queener]

I think mixing BookOpen and BookAttach causes the problems. BookAttach is to be used when you want to attach to an already open workbook, BookOpen to open a new workbook.

Func NoChanges()
    $sFilepath = @ScriptDir & "\Files\JANPRO_REPORT.xlsx"
    If Not FileExists($sFilepath) Then Return MsgBox(0, "Error", "Report file does not exist")

;------------------------------------> Code goes below:

$tCur = _NowDate()
$oExcel = _Excel_Open()
$oWorkBook = _Excel_BookOpen($oExcel, $sFilepath)
_Excel_RangeWrite($oWorkBook, "REPORT", $tCur, "D1", True)
_Excel_BookSaveAs($oWorkBook, @DesktopDir & "\JanPro_REPORT.xlsx", Default, 0, 1)
If @error Then Return MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookSave Example 1", "Error saving workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookSave Example 1", "Workbook has been successfully saved as '" & @DesktopDir & "\JanProReport.xlsx'.")
_Excel_BookClose($oWorkBook, False)

;------------------------------------> End of Code

    Return ;Must use this code at the end of code
EndFunc

SaveAs has two problems. Parameter 3 is an integer, not a text ("xlsx").

Problem #2: Do you really want to set the password to "1"?

Please have a look at the help file for proper parameter description.

 #include <Excel.au3>
_Excel_BookSaveAs ( $oWorkbook, $sFilePath [, $iFormat = $xlWorkbookDefault [, $bOverWrite = False [, $sPassword = Default [, $sWritePassword = Default [, $bReadOnlyRecommended = False]]]]] )

I also Notice that it quoted:

E.g. $iFormat = $xlExcel8 and extension = "xlsx" will return an error.

Para. 3 ask for format.

 

EDIT:     Got it; I checked the ExcelConstants.au3 and the extension allow is integer.

Thank you both for helping... Just incase someone need to know it; it's:

_Excel_BookSaveAs($oWorkBook,@DesktopDir & "\JanPro_REPORT.xlsx", 51, True)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookSave Example 1", "Error saving workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookSave Example 1", "Workbook has been successfully saved as '" & @DesktopDir & "\JanProReport.xlsx'.")

Edited December 5, 2014 by asianqueen

[water]

Glad you could fix your script:)

[mpower]

Apologies for reviving an old thread but I am facing the exact same issue at the moment and can't seem to find a solution. In the past _Excel_Close() worked fine but now since I've upgraded to Office 2013 _Excel_Close() doesn't seem to close the "Excel.exe" process which remains running in the background (i.e. it doesnt disappear from Task Manager).

My code is:

$oExcel = _Excel_Open(False, False, False, False, True)
    $oWorkbook = _Excel_BookOpen($oExcel, $file)
    $LastRow = $oExcel.ActiveSheet.UsedRange.Rows.Count
    $LastCol = $oExcel.ActiveSheet.UsedRange.Columns.Count
    $LastColl = ExcelColumnLetter($LastCol)
    $OrgArray = $oExcel.transpose($oExcel.Activesheet.Range("A1:" & $LastColl & $LastRow))
    _Excel_Close($oExcel, False, True)
    Sleep(2000)
    _ArrayDisplay($OrgArray)

 

Func ExcelColumnLetter($iColumn = 0)
    Local $letters
    While $iColumn
        $x = Mod($iColumn, 26)
        If $x = 0 Then $x = 26
        $letters = Chr($x + 64) & $letters
        $iColumn = ($iColumn - $x) / 26
    WEnd
    Return $letters
EndFunc ;==> _ExcelColumnLetter

Any help would be much appreciated.

Edited July 29, 2015 by mpower

[JohnOne]

Maybe you have to close the book first.

[mpower]

Maybe you have to close the book first.

You'd think so, but it does nothing.

The docs also state that:

If Excel was started by _Excel_Open() then _Excel_Close() closes all workbooks
(even those opened manually by the user for this instance after _Excel_Open()) and closes the specified Excel instance.

[JohnOne]

Then change _Excel_Close($oExcel, False, True) to _Excel_Close($oExcel, True, False)

[mpower]

Then change _Excel_Close($oExcel, False, True) to _Excel_Close($oExcel, True, False)

No effect. Process still remains and has to be killed manually.

[water]

You have made sure that no Excel process existed when you start your script?

[mpower]

You have made sure that no Excel process existed when you start your script?

Yes, I actually have task manager running with no Excel.exe processes, then I run the script and an Excel.exe process gets created, but does not disappear after _Excel_Close().

[water]

What is the value of @error after _Excel_Close?

[water]

I would use the following code:

$oExcel = _Excel_Open(False, False, False, False, True)
$oWorkbook = _Excel_BookOpen($oExcel, $file, True)
$aOrgArray = _Excel_RangeRead($oWorkbook)
$oWorkbook = _Excel_BookClose($oWorkbook, False)
_Excel_Close($oExcel, False, True)
_ArrayDisplay($OrgArray)

 

[mpower]

I would use the following code:

$oExcel = _Excel_Open(False, False, False, False, True)
$oWorkbook = _Excel_BookOpen($oExcel, $file, True)
$aOrgArray = _Excel_RangeRead($oWorkbook)
$oWorkbook = _Excel_BookClose($oWorkbook, False)
_Excel_Close($oExcel, False, True)
_ArrayDisplay($OrgArray)

 

Much for muchness really, the only thing that changed here is the way the sheet is read into an array.

The problem is, it still leaves Excel.exe running in the background, see attachment of a screenshot from Task Manager - taken after the function has ended.