Queener

Close the entire application of excel

82 posts in this topic

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)

 

 


Msgbox(0, "Hate", "Just hate it when I post a question and find my own answer after a couple tries. But if I don't post the question, I can't seem to resolve it at all.")

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

$oExcel.Quit()

or

$oExcel.Application.Quit
Edited by MikahS
1 person likes this

Snips & Scripts


My Snips: graphCPUTemp ~ getENVvars
My Scripts: Short-Order Encrypter - message and file encryption V1.6.1 ~ AuPad - Notepad written entirely in AutoIt V1.9.4

Feel free to use any of my code for your own use.                                                                                                                                                           Forum FAQ

 

Share this post


Link to post
Share on other sites

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

 

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.

:alien:


if @error Then
	MsgBox(262192,"",@ComputerName&" slaps "&@UserName&" around a bit with a large trout!")
	EndIf

>Exit code: 0

REAL search results

"Yeah yeah yeah, patience. How long will that take?"  -Ed Gruberman

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

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 by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#6 ·  Posted (edited)

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:

post-76739-0-23354800-1417767725_thumb.j

 

For app close; I used:

$oExcel.Quit()

Thanks to MikahS

Edited by asianqueen

Msgbox(0, "Hate", "Just hate it when I post a question and find my own answer after a couple tries. But if I don't post the question, I can't seem to resolve it at all.")

Share this post


Link to post
Share on other sites

#7 ·  Posted (edited)

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 by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Thanks to MikahS

 

My pleasure. ;)


Snips & Scripts


My Snips: graphCPUTemp ~ getENVvars
My Scripts: Short-Order Encrypter - message and file encryption V1.6.1 ~ AuPad - Notepad written entirely in AutoIt V1.9.4

Feel free to use any of my code for your own use.                                                                                                                                                           Forum FAQ

 

Share this post


Link to post
Share on other sites

#9 ·  Posted (edited)

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 by asianqueen

Msgbox(0, "Hate", "Just hate it when I post a question and find my own answer after a couple tries. But if I don't post the question, I can't seem to resolve it at all.")

Share this post


Link to post
Share on other sites

Glad you could fix your script:)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#11 ·  Posted (edited)

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 by mpower

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

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

Share this post


Link to post
Share on other sites

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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().

Share this post


Link to post
Share on other sites

What is the value of @error after _Excel_Close?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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)

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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.

 

2015-07-30 14_57_47-Windows Task Manager.png

Share this post


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