Sign in to follow this  
Followers 0
uncouthyouth

_ExcelBookSaveAs

6 posts in this topic

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!

Share this post


Link to post
Share on other sites



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.

>_<


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Thank you! That works. But why?

Share this post


Link to post
Share on other sites

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

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