Excel close problem...

[Iceman1988]

Hello there

I've just started programing in autoit and i've encontered a problem trying to close excel workbook.

You see i have 3 workbooks opened and when i try to close one of them it gives an error concerning display alerts. i have googled for help on the matter but haven't found not (or haven´t found it )

i'm using this code:

#include <Excel.au3>

$oExcel = _ExcelBookAttach("PS_AGD_FIM_DIA.xls","Filename")
SCRIPT"
_ExcelBookClose($oExcel, 0, 0)

thanks

[bogQ]

#include <Excel.au3>
$oExcel = _ExcelBookAttach(@ScriptDir&'PS_AGD_FIM_DIA.xls');full path to documment
_ExcelWriteCell($oExcel, 'If you can read this, then Success!', 1, 1) ;Write to the Cell
Sleep(1000)
_ExcelBookClose($oExcel, 0, 0)
$oExcel = '';clean var

works for me no alerts, what version of excel are you using?

try to see if this dumb idea can help

edit lines in that udf for func

from

If $oExcel.Application.Workbooks.Count > 1 Then
    $oExcel.Close
    ; Restore the users specified settings
    $oExcel.Application.DisplayAlerts = $fDisplayAlerts
    $oExcel.Application.ScreenUpdating = $fScreenUpdating
   Else
    $oExcel.Application.Quit
   EndIf

to

If $oExcel.Application.Workbooks.Count > 1 Then
    $oExcel.Close
    $oExcel = ''
    $oExcel = ObjGet("", "Excel.Application")
    ; Restore the users specified settings
    $oExcel.Application.DisplayAlerts = $fDisplayAlerts
    $oExcel.Application.ScreenUpdating = $fScreenUpdating
    $oExcel = ''
   Else
    $oExcel.Application.Quit
   EndIf

Edited January 10, 2012 by bogQ

[Iceman1988]

hello

first of all thanks for replying that works fine if you have only one workbook opened when you run the script i have at least 3 which makes the error come out again i've tried your idea but it does the same :S

i suck at this xD

[JLogan3o13]

Hi, Iceman1988. Did you ever say which version of Excel you're using? The below snippet works find for me on 2007 and 2010 with multiple workbooks open.

#include <Excel.au3>
$path = @DesktopDir & "SCCM Progress.xls"
$oExcel = _ExcelBookAttach($path)
_ExcelWriteCell($path, "Test", 1, 1)
_ExcelBookClose($oExcel, 0, 0)

Edited January 12, 2012 by JLogan3o13

[bogQ]

i've tried your idea but it does the same :S

it doesnt work the same from the place im standing, are you shure that you edited correct part of that func in udf and that you saved the udf before trying?

expandcollapse popup

; #FUNCTION# ====================================================================================================================
; Name...........: _ExcelBookClose
; Description ...: Closes the active workbook and removes the specified Excel object.
; Syntax.........: _ExcelBookClose($oExcel[, $fSave = 1[, $fAlerts = 0]])
; Parameters ....: $oExcel - Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
;                 $fSave - Flag for saving the file before closing (0=no save, 1=save) (default = 1)
;                 $fAlerts - Flag for disabling/enabling Excel message alerts (0=disable, 1=enable) (default = 0)
; Return values .: On Success - Returns 1
;                 On Failure - Returns 0 and sets @error on errors:
;                 [email="|@error=1"]|@error=1[/email] - Specified object does not exist
;                 [email="|@error=2"]|@error=2[/email] - File exists, overwrite flag not set
; Author ........: SEO <locodarwin at yahoo dot com>
; Modified.......: 07/17/2008 by bid_daddy; litlmike
; Remarks .......: None
; Related .......:
; Link ..........:
; Example .......: Yes
; ===============================================================================================================================
Func _ExcelBookClose($oExcel, $fSave = 1, $fAlerts = 0)
    If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
    Local $sObjName = ObjName($oExcel)
    If $fSave > 1 Then $fSave = 1
    If $fSave < 0 Then $fSave = 0
    If $fAlerts > 1 Then $fAlerts = 1
    If $fAlerts < 0 Then $fAlerts = 0
    ; Save the users specified settings
    Local $fDisplayAlerts = $oExcel.Application.DisplayAlerts
    Local $fScreenUpdating = $oExcel.Application.ScreenUpdating
    ; Make necessary changes
    $oExcel.Application.DisplayAlerts = $fAlerts
    $oExcel.Application.ScreenUpdating = $fAlerts
    Switch $sObjName
        Case "_Workbook"
            If $fSave Then $oExcel.Save()
            ; Check if multiple workbooks are open
            ; Do not close application if there are
            If $oExcel.Application.Workbooks.Count > 1 Then
                $oExcel.Close
                $oExcel = ''
                $oExcel = ObjGet("", "Excel.Application")
                ; Restore the users specified settings
                $oExcel.Application.DisplayAlerts = $fDisplayAlerts
                $oExcel.Application.ScreenUpdating = $fScreenUpdating
                $oExcel = ''
            Else
                $oExcel.Application.Quit
            EndIf
        Case "_Application"
            If $fSave Then $oExcel.ActiveWorkBook.Save()
            $oExcel.Quit()
        Case Else
            Return SetError(1, 0, 0)
    EndSwitch
    Return 1
EndFunc   ;==>_ExcelBookClose

note that Excel.au3 udf is read only so before you save it youl need to turn off read only on that file

pls copy paste scite error after you edit and resave udf excel UDF.

@JLogan3o13

for some reason object $oExcel is delited or messed up after $oExcel.Close in some casses so reattaching the object to remaining excel shud do the trick in this case, or deliting lines

; Restore the users specified settings
                $oExcel.Application.DisplayAlerts = $fDisplayAlerts
                $oExcel.Application.ScreenUpdating = $fScreenUpdating

shud work but you will not restore settings.

(if im not wrong ofc)

dont know if this is realted to _ExcelBookAttach in some way or only to version of autoit (Excel.au3) or only problem is in _ExcelBookClose, but on some computers i can confirm this error and i think now that version of excel dont make diffrance.

nice description

; Description ...: Closes the active workbook and [b]removes[/b] the specified Excel object.

Edited January 12, 2012 by bogQ

[Iceman1988]

Thanks a lot both of you my excel UDF was messed up ( don't know why :S) i just paste the one @bogQ sent and it works just fine now

really thaks a lot i was banging my head on the wall because of this xD

[Iceman1988]

hello again

my script opens an email and attaches a file to it... then i'm trying to paste some data from the previous mentioned excel workbook in a email body before i can close it but... the problem is that it selects the data i want to paste but i cant seem to find the func to paste it on the email body

do someone by any chance knows the func i need?

the email is in outlook 2007 btw

tks

[bogQ]

maybe it's best for you to look at send mail ) from Jos-s example script, i played with it on numerous times and it returned perfect results on sending emails from your gmail accounts. I assume that if you're looking on sending $var data in body or to attach file, this can do the job.

If you`r trying not to use gmail, and as i think that outlook don`t have standard udf included directly with autoit instal, and your trying to copy paste something i would suggest you to try to controlsend* with '^v' (or controlsetfocus* and then send*) (^v is equivalent to ctrl+v that is paste func on kboard, look at help file about send* or controllsend*). if your using controlsend* take control ID with autoit window info tool (it's in start menu where all autoit things are) to get needed things for controlsend parameters.

pls feel free to post here some script that can replicate problem it you run into the problems. i'm sure that as soon anyone who's familiar with commands that your using will help you with the problem if it don't have conflict with Forum Rules ofc

[Iceman1988]

thanks for the tips i found out a diferent excel UDF o allows me to do everything i writed in the previous posts

but non the less your tips where another way of make it work perfectly

thanks a lot

[EvilEd]

Thanks for the tips. I was also having this problem.

BTW, I tried to just put $oExcel = "" after the _ExcelBookClose($oExcel, 0, 0) line and that worked, so no need to modify the UDF.

[bogQ]

yes, yes, i know that -->

but i don't know do it restore rest of DisplayAlerts if you don`t modify the udf, so that's why i suggested the modified version, after all he is working with 3 documents at the same time

Edit: added clip

http://www.youtube.com/watch?v=V-OYKd8SVrI

Edited February 13, 2012 by bogQ

[water]

A lot of problems with the Excel or other UDFs have been caused by running a 64 bit AutoIt script when the installed MS Office is 32 bit.

Compile or run your AutoIt script for 32 bit in this case and most problems should be gone.

[julie]

guys.. i have trouble in disabling the confirmation message of excel in replacing same file name.. need help right now

 

[water]

Welcome to AutoIt and the forum!

You know that the thread is more than 3 years old. 

I suggest to open a new thread. And please post your code so we can see what you have tried so far.