Jump to content

@error set to 4 when using _ExcelBookSaveAs()


Go to solution Solved by JLogan3o13,

Recommended Posts

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?

 

Link to comment
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.

Link to comment
Share on other sites

  • Moderators
  • Solution

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)

"Profanity is the last vestige of the feeble mind. For the man who cannot express himself forcibly through intellect must do so through shock and awe" - Spencer W. Kimball

How to get your question answered on this forum!

Link to comment
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

Link to comment
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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...