@error set to 4 when using _ExcelBookSaveAs()

[DavidH]

Hey guys, 

I am relatively new to autoIT and I am loving it, except I am getting this one little problem when calling the _ExcelBookSaveAs() function. Documentation gives me explanations for @error at 1, 2 and 3... but the @error flag is set to 4.

I have looked at the function code in Excel.au3,

; #FUNCTION# ====================================================================================================================
; Author ........: SEO <locodarwin at yahoo dot com>
; Modified.......: litlmike, water
; ===============================================================================================================================
Func _ExcelBookSaveAs($oExcel, $sFilePath, $vType = "xls", $fAlerts = 0, $fOverWrite = 0, $sPassword = "", $sWritePassword = "", $iAccessMode = 1, _
$iConflictResolution = 2)
If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
If $vType = "xlsx" Or $vType = "xls" Or $vType = "csv" Or $vType = "txt" Or $vType = "template" Or $vType = "html" Then
If $vType = "xlsx" Then $vType = $xlWorkbookDefault
If $vType = "xls" Then $vType = $xlNormal
If $vType = "csv" Then $vType = $xlCSVMSDOS
If $vType = "txt" Then $vType = $xlTextWindows
If $vType = "template" Then $vType = $xlTemplate
If $vType = "html" Then $vType = $xlHtml
ElseIf Not IsNumber($vType) Then
Return SetError(2, 0, 0)
EndIf
If $fAlerts > 1 Then $fAlerts = 1
If $fAlerts < 0 Then $fAlerts = 0
$oExcel.Application.DisplayAlerts = $fAlerts
$oExcel.Application.ScreenUpdating = $fAlerts
If FileExists($sFilePath) Then
If Not $fOverWrite Then Return SetError(3, 0, 0)
FileDelete($sFilePath)
EndIf
If $sPassword = "" And $sWritePassword = "" Then $oExcel.ActiveWorkBook.SaveAs($sFilePath, $vType, Default, Default, Default, Default, $iAccessMode, $iConflictResolution)
If $sPassword <> "" And $sWritePassword = "" Then $oExcel.ActiveWorkBook.SaveAs($sFilePath, $vType, $sPassword, Default, Default, Default, $iAccessMode, $iConflictResolution)
If $sPassword <> "" And $sWritePassword <> "" Then $oExcel.ActiveWorkBook.SaveAs($sFilePath, $vType, $sPassword, $sWritePassword, Default, Default, $iAccessMode, $iConflictResolution)
If $sPassword = "" And $sWritePassword <> "" Then $oExcel.ActiveWorkBook.SaveAs($sFilePath, $vType, Default, $sWritePassword, Default, Default, $iAccessMode, $iConflictResolution)
If @error Then Return SetError(4, @error, 0)
If Not $fAlerts Then
$oExcel.Application.DisplayAlerts = 1
$oExcel.Application.ScreenUpdating = 1
EndIf
Return 1
EndFunc   ;==>_ExcelBookSaveAs

but cant make any sense of it.

Here is my code (bare in mind, i am in the depth of a testing phase at the moment, so the function may look a little silly)

expandcollapse popup

Func writeToDB($DBfileName, $url)


if not FileExists($DBfileName) then ; Just a check to be sure.. If the database file does not exist, then create one and save it
Local $newBook = _ExcelBookNew()
_ExcelBookSaveAs($newBook, $DBfileName, "xls", 0, 0)
_ExcelBookClose($newBook, 1, 0)
endif


Local $oExcel = _ExcelBookOpen($DBfileName) ;Open the book, do NOT make it visible


Local $DBReadTest
Local $Count = 0


for $i = 1 To 100 Step 1


$DBReadTest = _ExcelReadCell ( $oExcel, $i)


if $DBReadTest <> "" Then
$Count = $Count + 1
Else
ExitLoop
EndIf
Next


     ; _ExcelWriteCell($oExcel, $url, $Count + 1, 1) ;Write to the Cell


     _ExcelRowDelete ( $oExcel, 20, 5 )


_ExcelBookSaveAs($oExcel, @ScriptDir & "/rotatorDB.xls", "xls", 1, 1)


     _ExcelBookClose($oExcel)


EndFunc

Anybody that can help?

 

[Danp2]

There was an error saving the file. The actual error is in @extended.

[DavidH]

There was an error saving the file. The actual error is in @extended.

 

Okay, so I have found the error code in @extended, im not sure where to go with it though?

-2147352567

[Danp2]

You should try the suggestion from >here and the post the results.

[DavidH]

@@ DEBUG COM Error encountered in excel_update_david.au3 (249) :
Number         = 0x80020009 (-2147352567)
WinDescription = Exception occurred.
Description    = Cannot access read-only document 'rotatorDB.xls'.
Source         = Microsoft Excel
HelpFile       = xlmain11.chm
HelpContext    = 0
LastDllError   = 0
Retcode        = 0x800A03EC

so the excel doc is automatically setting to read-only... will have to see what the hell is causing this.

[JLogan3o13]

Are you sure you have access to the location you're trying to save? This (slightly altered for efficiency's sake) version of your code works just fine for me:

#include <Excel.au3>

$DBfileName = @DesktopDir & "\TestMe.xls"

If Not FileExists($DBfileName) then ; Just a check to be sure.. If the database file does not exist, then create one and save it
    Local $newBook = _ExcelBookNew()
        _ExcelBookSaveAs($newBook, $DBfileName, "xls", 0, 0)
        _ExcelBookClose($newBook, 1, 0)
EndIf


Local $DBReadTest, $Count = 0, $oExcel = _ExcelBookOpen($DBfileName, 1)

    For $i = 1 To 100 ;Step 1 is the default, don't have to declare.
        $DBReadTest = _ExcelReadCell( $oExcel, $i)
            If @error Then MsgBox(0, "", "Error Reading Cell") ;Try to catch @error
        If $DBReadTest <> "" Then
            $Count = $Count + 1
        Else
            ExitLoop
        EndIf
    Next

     ; _ExcelWriteCell($oExcel, $url, $Count + 1, 1) ;Write to the Cell
    _ExcelRowDelete ( $oExcel, 20, 5 )
    _ExcelBookSaveAs($oExcel, @ScriptDir & "/rotatorDB.xls", "xls", 1, 1)
        If @error Then MsgBox(0, "", "Error Saving") ;Try to catch @error
    _ExcelBookClose($oExcel)

[DavidH]

After trying the code with my excel file saved on my Desktop, it works perfectly! Must be a permission thing, not sure how but at least now i know what is wrong! Thank you everybody for your help!

-Davey