AnuradhaPrasad

AutoIT error while saving excel file in Office 2013

24 posts in this topic

Hi All,

I am using AUTOIT (v3.3.6.1) on Windows10 with Office 2013 installed on it. Below is the code snippet to save excel file-

Func ExcelSaveAs($oXLS, $profile, $iter)
   $timestamp = HostTimerInit()
           If FileExists($datafilesPath&"\" & $profile & $iter & $xlExt) Then
            FileDelete($datafilesPath&"\" & $profile & $iter & $xlExt)
        EndIf
        $oXLS.ActiveWorkBook.SaveAs($datafilesPath&"\" & $profile & $iter & $xlExt)  --> v:\Sort1.xlsx [Value which should be replaced at runtime]
    $diff = HostTimerDiff($timestamp)
    annotate("stop: excel formula")
    $logStr = "Excel_" & $profile & "-Save "
    LogFileWrite($Logfile, $logStr & $diff)
EndFunc

When the script is executed for 1st iteration it saves file with name Sort1.xlsx. For second iteration onward it opens a popup with message "Do you wish to save Book1" with 3 button of "save, saveas, close". The entire script fails after this message, with below error -

05:15:59 ERROR: Workload was killed because it was stuck for too long.
05:15:59 ERROR: LastOp: Excel_Sort_SAVE, numOps: 674

We do have error during closing ppt as well. [Error - server_benchmark_test_68_PPool268.log:04:43:00 AUTOIT ERROR********************************Presentation.Saved : Object does not exist.]

If $officeVersion == 15 Then
       LogFileWrite($Logfile, "Inside If pptpid : " & $pptpid)
       ProcessClose($pptpid)
    Else

The same script used to work seamlessly with Windows7 and Office 2010.

Is anyone aware of any such issue with latest Office 2013 and AutoIT version? Please help...

Thanks in Advance

Share this post


Link to post
Share on other sites



You could use

$oExcel.DisplayAlerts = $bDisplayAlerts

where $bDisplayAlerts can be set to True or False and $oExcel is the Excel application object.
https://msdn.microsoft.com/en-us/library/ff839782.aspx


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-02-03 - Version 1.4.7.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-03 - Version 1.2.4.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
Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Hi,

Thanks for the update, I will test the changes and update you.

But just a thought, the same function works fine on 95 desktop and fails on 5. 

Thanks.

Share this post


Link to post
Share on other sites

All do have the same version of Excel installed?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-02-03 - Version 1.4.7.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-03 - Version 1.2.4.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
Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
3 hours ago, AnuradhaPrasad said:

$oXLS.ActiveWorkBook.SaveAs($datafilesPath&"\" & $profile & $iter & $xlExt)  --> v:\Sort1.xlsx [Value which should be replaced at runtime]

seems you have forgotten a semikolon in this line.

Share this post


Link to post
Share on other sites

@Water - Yes all desktop has same version of Excel installed. 

@AutoBert - The command used is and it doesn't require semicolon as it is written in python

$oXLS.ActiveWorkBook.SaveAs($datafilesPath&"\" & $profile & $iter & $xlExt)  

Thanks

Share this post


Link to post
Share on other sites

@Water : The property suggested by you, didn't solve the problem. I am still getting popup for second iteration of run during excel save.

$oExcel.DisplayAlerts = $bDisplayAlerts

Share this post


Link to post
Share on other sites

I run Office 2010 on Win7 with Autoit 3.3.12.0. So, unfortunately, I'm out of ideas now :(


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-02-03 - Version 1.4.7.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-03 - Version 1.2.4.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
Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Is anyone else aware of any known autoit issue with Windows10 and Office2013?

Share this post


Link to post
Share on other sites

Hi Water,

For above issue I am trying latest version of AutoIT.

In 3.3.6.1 version of AUTOIT we had method present in Excel.au3 for writing to Cell - _ExcelWriteCell.

The same is not present in latest 3.3.14.2 version, are u aware of any other equivalent method in Excel.au3?

Thanks in Advance.

Share this post


Link to post
Share on other sites

Yes. It is now called _Excel_RangeWrite.
The docu holds a page about all script breaking changes in the "History / Changelog" section: https://www.autoitscript.com/autoit3/docs/script_breaking_changes_excel_udf.htm


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-02-03 - Version 1.4.7.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-03 - Version 1.2.4.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
Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Thanks Water for help. Excel save issue is resolved with latest AutoIT version.

For few occurrences I see AUTOIT ERROR on following method- $otmp = _Excel_Open(). Though the second call to -> $oWorkbooktmp = _Excel_BookNew($otmp) works fine. Any idea?

Also, with latest AutoIT version PPT is not working. Do we not get powerpoint.au3 in include folder of installed autoIT? Please do let me know if you have source of code of same.

Thanks. 

Share this post


Link to post
Share on other sites

You need to be more specific about the Excel error! Do you get an error message? What is the value of @error and @extended after calling the function?

PPT UDF is not part of AutoIt. Which PPT UDF do you use?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-02-03 - Version 1.4.7.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-03 - Version 1.2.4.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
Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Hi Water,

These are values for @error=TRUE and @extended=0, during the AUTOIT error.

I am using Functions listed in below URL for powerpoint-

 

Thanks once Again.

Share this post


Link to post
Share on other sites
3 hours ago, AnuradhaPrasad said:

These are values for @error=TRUE and @extended=0, during the AUTOIT error.

That's for the Excel error, right? @error never returns True it returns an integer. So I suspect that @error = 1 after you called _Excel_Open.
According to the help file this means:

Quote

1 - Error returned by ObjCreate. @extended is set to the COM error code.

But @extended should have a value <> 0.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-02-03 - Version 1.4.7.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-03 - Version 1.2.4.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
Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
Quote

Also, with latest AutoIT version PPT is not working.

Define "not working".


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-02-03 - Version 1.4.7.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-03 - Version 1.2.4.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
Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Hi Water,

After adding additional logger to Excel.au3, I could identify the line where AUTOIT ERROR was thrown-

If Not $bForceNew Then $oExcel = ObjGet("", "Excel.Application")  - Use to fail and Error out

If $bForceNew Or @error Then
        $oExcel = ObjCreate("Excel.Application")  - This method works fine and excel object was created
        If @error Or Not IsObj($oExcel) Then Return SetError(1, @error, 0)
               $bApplCloseOnQuit = True
    EndIf

I used to get error at [_Excel_Open() -> ObjGet] and then rest of Func calls to Excel like open, insert and close use to work fine.

Just a clarification what is difference between ObjGet and ObjCreate?

 

Also you mentioned PPT is not working with latest AUTOIT version.

I did execute 100 desktop scale run on ppt. For some there was no error but for others, the presentation object handler was not found.

Return $obj.Slides.Count - Didn't return any value

$obj.Close()  - Presentation.Close : Object does not exist.

Are the above related to issue you are referring to?

Thanks in Advance.

Share this post


Link to post
Share on other sites

The error with ObjGet is expected.
ObjGet tries to connect to a running instance of Excel. If there is none then the error is raised and a new instance of Excel is started using ObjCreate.
So everything is just fine.

PowerPoint:
You are sure that PowerPoint is installed on those machines where the script fails?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-02-03 - Version 1.4.7.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-03 - Version 1.2.4.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
Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Hi Water,

We clone desktop using golden desktop image. So all desktop has same set of software and configuration.

The ppt error reported on any desktop are random. It might have successfully executed 1st iteration and starts failing from second iteration. For other cases, it did perform operations like ppt_open, run_slide, save_slide but would have failed for append_slide.

What error did you mention in earlier post with regard to ppt. Can you please provide more details.

Thanks,

Anuradha 

Share this post


Link to post
Share on other sites

The PPT UDF you mentiond is quite old and was written for an older version of AutoIt.
I have started to code a new PPT UDF, but it is still in an early alpha state but the functions provided work well:

 

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-02-03 - Version 1.4.7.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-03 - Version 1.2.4.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
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