_ExcelBookSaveAs crashing script

[Bert]

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:

expandcollapse popup

#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 March 18, 2010 by Volly

[picaxe]

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

[enaiman]

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.

[Bert]

It is attaching. You can see I write to the cell to verify that it did attach.

[99ojo]

Hi,

have a look here:

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

;-))

Stefan

[picaxe]

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.

[Bert]

Sweet! I'll put it in tonight when I get home. Thanks!

[Juvigy]

That is why i always use something like :

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