Sign in to follow this  
Followers 0
Bert

_ExcelBookSaveAs crashing script

8 posts in this topic

#1 ·  Posted (edited)

I have a script I'm building that will save to the desktop and close any Office file. In testing, I'm having a bit of trouble on the Excel part. When I test I simply open Excel to create a new workbook. I don't save the file at all so it is called book1.

I run this script:

Func _ExcelSave($EFile, $varE)
    $oExcel = _ExcelBookAttach($varE, "Title")
    _ExcelWriteCell($oExcel, "I Wrote to This Cell", 1, 1) ;Write to the Cell   - TESTING - verifies attachment here.
    _ExcelBookSaveAs($oExcel,@DesktopDir & "\EFS files\test", "xls")  ;Crashes here. not know why.      
    _ExcelBookClose($oExcel, 0, 0)
EndFunc

When it gets to _ExcelBookSaveAs, I get this in the console:

>Running AU3Check (1.54.19.0)  from:C:\Program Files\AutoIt3
+>22:10:08 AU3Check ended.rc:0
>Running:(3.3.6.0):C:\Program Files\AutoIt3\autoit3.exe "C:\Documents and Settings\Owner.YOUR-9258729F9F\My Documents\AU3 scripts\office\office_file_save.au3"    
C:\Program Files\AutoIt3\Include\Excel.au3 (356) : ==> The requested action with this object has failed.:
If $sPassword = "" And $sWritePassword = "" Then $oExcel.ActiveWorkBook.SaveAs($sFilePath, $sType, Default, Default, Default, Default, $iAccessMode, $iConflictResolution)
If $sPassword = "" And $sWritePassword = "" Then $oExcel.ActiveWorkBook^ ERROR
->22:10:09 AutoIT3.exe ended.rc:1
+>22:10:10 AutoIt3Wrapper Finished
>Exit code: 1    Time: 1.817

I also notice Excel is messed up at this point and I have to close it. Any idea what I'm doing wrong? I tested the _ExcelBookSaveAs example script, and that works fine.

Edit - the file path exist. I tested the Word part of my script, and that works fine. It saves to the same folder.

Entire script for those who are interested:

#include <Word.au3>
#include <Excel.au3>
#include <File.au3>

dim $name
dim $var = WinList()
_createFolder()

For $i = 1 to $var[0][0]
  ; Only display visble windows that have a title for Office Applications
    If $var[$i][0] <> "" AND IsVisible($var[$i][1]) Then
        Select
            case StringRight($var[$i][0], 14) = "Microsoft Word" 
                $name = StringtrimRight($var[$i][0], 17)
                if $name >1 then _WordSave($name)
                if $name = 0 then 
                    $oWordAppx = _WordAttach ($var[$i][1], "HWND")
                    _WordQuit ($oWordAppx)
                endif           
            case StringLeft($var[$i][0], 15) = "Microsoft Excel" 
                $name = StringtrimLeft($var[$i][0], 18)
                $stl = stringlen($name)
                if $stl > 0 then _ExcelSave($name, $var[$i][0])
                if $stl = 0 then ProcessClose("EXCEL.EXE")  
                ;MsgBox(0, "Details", "Title=" & $var[$i][0] & @LF & "Handle=" & $var[$i][1])   
            case StringLeft($var[$i][0], 20) = "Microsoft PowerPoint" 
                ;MsgBox(0, "Details", "Title=" & $var[$i][0] & @LF & "Handle=" & $var[$i][1])
            case StringRight($var[$i][0], 15) = "Microsoft Visio" 
                ;MsgBox(0, "Details", "Title=" & $var[$i][0] & @LF & "Handle=" & $var[$i][1])               
        EndSelect   
  EndIf
Next

Func _createFolder() ;creates a folder to save files to. 
    $f = DirGetSize(@DesktopDir&"\EFS files",2)
    if $f = -1 then DirCreate(@DesktopDir&"\EFS files")
EndFunc

Func _WordSave($WFile)
$oWordApp = _WordAttach ($WFile, "FileName")
$oDoc = _WordDocGetCollection ($oWordApp, 0)
_WordDocSaveAs($oDoc, @DesktopDir&"\EFS files\"&$WFile,0,0)
_WordDocClose($oDoc)
EndFunc

Func _ExcelSave($EFile, $varE)
    $oExcel = _ExcelBookAttach($varE, "Title")
    _ExcelWriteCell($oExcel, "I Wrote to This Cell", 1, 1) ;Write to the Cell   - TESTING - verifies attachment here.
    _ExcelBookSaveAs($oExcel,@DesktopDir & "\EFS files\test", "xls")  ;Crashes here. not know why.      
    _ExcelBookClose($oExcel, 0, 0)
EndFunc 

Func IsVisible($handle)
  If BitAnd( WinGetState($handle), 2 ) Then 
    Return 1
  Else
    Return 0
  EndIf
EndFunc
Edited by Volly

Share this post


Link to post
Share on other sites



Maybe a com error handler will give you more error info.

Share this post


Link to post
Share on other sites

Not sure about this but maybe you need to open the file first in order "Attach" to succeed.

From the error you get, I can presume that it is a problem of "ActiveWorkBook" - the script is somehow confused about that.

Try this function:

Func _ExcelSave($EFile, $varE)
    _ExcelBookOpen($varE)
    $oExcel = _ExcelBookAttach($varE, "Title")
    _ExcelWriteCell($oExcel, "I Wrote to This Cell", 1, 1) ;Write to the Cell   - TESTING - verifies attachment here.
    _ExcelBookSaveAs($oExcel,@DesktopDir & "\EFS files\test", "xls")  ;Crashes here. not know why.      
    _ExcelBookClose($oExcel, 0, 0)
EndFunc

As I said, I'm not sure this is the problem. It might worth a try though.


SNMP_UDF ... for SNMPv1 and v2c so far, GetBulk and a new example script

wannabe "Unbeatable" Tic-Tac-Toe

Paper-Scissor-Rock ... try to beat it anyway :)

Share this post


Link to post
Share on other sites

Hi,

have a look here:

http://www.autoitscript.com/forum/index.php?showtopic=99611&st=0&p=714665&hl=If%20$sPassword%20=%20&quot;&quot;%20And%20&036;sWritePassword%20=%20&quot;&quot;%20Then%20&036;oExcelActiveWorkBookSaveAs&fromsearch=1&entry714665

;-))

Stefan

Share this post


Link to post
Share on other sites

The link from 99ojo fixes the problem. Another way is

Func _ExcelSave($EFile, $varE)
    $oExcel = _ExcelBookAttach($varE, "Title")
    $oExcel.Application.DisplayAlerts = False
    $oExcel.SaveAs(@DesktopDir & "\EFS files\test")
    _ExcelBookClose($oExcel, 0, 0)
EndFunc
In this case a com error handler is not more informative.

Share this post


Link to post
Share on other sites

That is why i always use something like :

$oExcel.Application.ActiveWorkBook.SaveAs("C:\Documents and Settings\test.xls",56,"","",False,False)

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
Sign in to follow this  
Followers 0