Sign in to follow this  
Followers 0
DavidH

@error set to 4 when using _ExcelBookSaveAs()

7 posts in this topic

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)

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?

 

Share this post


Link to post
Share on other sites



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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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)

√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites

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

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