_ExcelBookSaveAs

[uncouthyouth]

I am attempting to write a script to automate a lengthy manual process.

Part of this involves taking user input, writing it to a specific cell on an open Excel spreadsheet (with an arbitrary filename/location), and then saving the spreadsheet to a different location.

However the Excel UDF function _ExcelBookSaveAs is causing errors and Excel to freeze.

Here is the relevant code.

#include <Excel.au3>
$title=WinGetTitle("Microsoft Excel","")

if not $title=0 then
$oExcel = _ExcelBookAttach($title, "Title")
Else
    $oExcel=_excelbooknew()
EndIf
 
_ExcelWriteCell($oExcel, "CRIT", "L4")

$oExcel = _ExcelBookAttach($title, "Title")

_ExcelBookSaveAs($oExcel, @MyDocumentsDir & "\temp", "xls", 0, 0)
If Not @error Then MsgBox(0, "Success", "File was Saved!", 3)
_ExcelBookClose($oExcel, 1, 0)
FileMove(@MyDocumentsDir & "\temp.xls", "C:\Folder\CR-" & $CRnum & " " & $custname & ".xls")
Exit

The helpfile makes it clear that _ExcelBookSaveAs cannot save to a different folder, which is why I used FileMove. But it never gets to that point. Instead it returns this error:

>"C:\Program Files\AutoIt3\SciTE\..\autoit3.exe" /ErrorStdOut "C:\Program Files\AutoIt3\Examples\test.au3"    
C:\Program Files\AutoIt3\Include\Excel.au3 (347) : ==> 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
>Exit code: 1    Time: 1.110

And I find Excel laying dead on the pavement.

Any help would be greatly appreciated!

[PsaltyDS]

I am attempting to write a script to automate a lengthy manual process.

Part of this involves taking user input, writing it to a specific cell on an open Excel spreadsheet (with an arbitrary filename/location), and then saving the spreadsheet to a different location.

However the Excel UDF function _ExcelBookSaveAs is causing errors and Excel to freeze.

Here is the relevant code.

#include <Excel.au3>
$title=WinGetTitle("Microsoft Excel","")

if not $title=0 then
$oExcel = _ExcelBookAttach($title, "Title")
Else
    $oExcel=_excelbooknew()
EndIf
 
_ExcelWriteCell($oExcel, "CRIT", "L4")

$oExcel = _ExcelBookAttach($title, "Title")

_ExcelBookSaveAs($oExcel, @MyDocumentsDir & "\temp", "xls", 0, 0)
If Not @error Then MsgBox(0, "Success", "File was Saved!", 3)
_ExcelBookClose($oExcel, 1, 0)
FileMove(@MyDocumentsDir & "\temp.xls", "C:\Folder\CR-" & $CRnum & " " & $custname & ".xls")
Exit

The helpfile makes it clear that _ExcelBookSaveAs cannot save to a different folder, which is why I used FileMove. But it never gets to that point. Instead it returns this error:

>"C:\Program Files\AutoIt3\SciTE\..\autoit3.exe" /ErrorStdOut "C:\Program Files\AutoIt3\Examples\test.au3"    
C:\Program Files\AutoIt3\Include\Excel.au3 (347) : ==> 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
>Exit code: 1    Time: 1.110

And I find Excel laying dead on the pavement.

Any help would be greatly appreciated!

Did you try it without the path in front of the file name? As in just "temp" without @MyDocumentsDir in front of it?

I don't have Excel here to test the idea.

>_<

[Authenticity]

_ExcelBookSaveAs($oExcel.Application, @MyDocumentsDir & "\temp", "xls", 0, 0)

[uncouthyouth]

Did you try it without the path in front of the file name? As in just "temp" without @MyDocumentsDir in front of it?

I don't have Excel here to test the idea.

>_<

That did not make any difference - same error.

But I was messing with things and found some more truly schizophrenic behavior. For example, running it without having an open Excel worksheet results in creating a blank worksheet and writing the text, but then it's saved as Book1.xls instead of temp.xls like it should. And the FileMove doesn't actually move anything (even after defining $CRnum and $custname).

[uncouthyouth]

_ExcelBookSaveAs($oExcel.Application, @MyDocumentsDir & "\temp", "xls", 0, 0)

Thank you! That works. But why?

[Authenticity]

Attaching returns different object than creating or opening. But the functions interact with the Application object internally so it might be confusing.