Iceman1988

Excel close problem...

14 posts in this topic

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

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

#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 by bogQ

TCP server and client - Learning about TCP servers and clients connection
Au3 oIrrlicht - Irrlicht project
Au3impact - Another 3D DLL game engine for autoit. (3impact 3Drad related)



460px-Thief-4-temp-banner.jpg
There are those that believe that the perfect heist lies in the preparation.
Some say that it’s all in the timing, seizing the right opportunity. Others even say it’s the ability to leave no trace behind, be a ghost.

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

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

√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

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?

; #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 by bogQ

TCP server and client - Learning about TCP servers and clients connection
Au3 oIrrlicht - Irrlicht project
Au3impact - Another 3D DLL game engine for autoit. (3impact 3Drad related)



460px-Thief-4-temp-banner.jpg
There are those that believe that the perfect heist lies in the preparation.
Some say that it’s all in the timing, seizing the right opportunity. Others even say it’s the ability to leave no trace behind, be a ghost.

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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 :)


TCP server and client - Learning about TCP servers and clients connection
Au3 oIrrlicht - Irrlicht project
Au3impact - Another 3D DLL game engine for autoit. (3impact 3Drad related)



460px-Thief-4-temp-banner.jpg
There are those that believe that the perfect heist lies in the preparation.
Some say that it’s all in the timing, seizing the right opportunity. Others even say it’s the ability to leave no trace behind, be a ghost.

 

Share this post


Link to post
Share on other sites

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 :D

thanks a lot

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

#11 ·  Posted (edited)

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

TCP server and client - Learning about TCP servers and clients connection
Au3 oIrrlicht - Irrlicht project
Au3impact - Another 3D DLL game engine for autoit. (3impact 3Drad related)



460px-Thief-4-temp-banner.jpg
There are those that believe that the perfect heist lies in the preparation.
Some say that it’s all in the timing, seizing the right opportunity. Others even say it’s the ability to leave no trace behind, be a ghost.

 

Share this post


Link to post
Share on other sites

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.


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

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

 

Share this post


Link to post
Share on other sites

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. 


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

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