Excel error when sheet is selected

[Agu]

How are you people?
I am new here and I am having a problem when I process several excel files.

First, I created the excel instance:
Local $ oExcel = _Excel_Open (False, False)

Then in a for, I open each of them:
Local $ dataWorkbook = _Excel_BookOpen ($oExcel, $excelPath, True, True)
and when opening the Workbook, it does not return any error

Once opened, I need the list of sheets that it has:
Local $ aWorkSheets = _Excel_SheetList ($dataWorkbook)

At the end of the for, I close $dataWorkbook. And when I finish executing the code, I close the excel instance.

My problem is that when doing the first run of the for, I can process the first excel without problems, when processing the second I get the following error:
"C: \ Program Files (x86) \ AutoIt3 \ Include \ Excel.au3" (1170): ==> The requested action with this object has failed .:
$ aSheets [$ iIndex] [0] = $ oWorkbook.Sheets ($ iIndex + 1) .Name
$ aSheets [$ iIndex] [0] = $ oWorkbook ^ ERROR

I tried changing the parameters when creating the excel instance (making it visible or not) and also tried changing the parameters when opening the Workbook (also making it visible or not, read-only True and read-only False, $ bUpdateLinks = 0 or $ bUpdateLinks = Default) and still I still have the same error.

I have tried to select a sheet in a way:
$dataWorkbook.Activate
$ oExcel.Application.ActiveWorkbook.Sheets (0) .Select
but it also gives
$ oExcel.Application.ActiveWorkbook ^ ERROR

In conclusion, every time I want to select a sheet of a Workbook that is processing gives me an error (in the first run of the for, usually does not happen, but in the second always happens) either using VBA code or the UDF of Autoit (Excel .au3).

Someone who happens the same?
I appreciate your help!

[water]

Welcome to AutoIt and the forum!

Can you please post your script or a stripped down reproducer script so we can have a look at it or even test it?

[Agu]

I have an excel file with 3 columns,
the first, it tells me in which sheet I should extract the data
the second, it tells me in which cell (from that sheet) I extract the values
the third is the name of that value
What I do is keep me in 3 arrays those columns, and then begin to process the files individually excel

Local $oExcel = _Excel_Open(False, False)
Local $dataEntryWorkbook = _Excel_BookOpen($oExcel, $entryDir, True, False, Default, Default, 0)
Local $itsNotEmpty = True
Local $j = 1
While ($itsNotEmpty)
    If (_Excel_RangeRead($dataEntryWorkbook, Default, "A" & $j, 3) = "") Then
        $itsNotEmpty = False
    Else
        $j = $j + 1
    EndIf
WEnd
$j = $j - 1
Local $entrySheet = _Excel_RangeRead($dataEntryWorkbook, Default, "A1:A" & $j)
Local $entryCell = _Excel_RangeRead($dataEntryWorkbook, Default, "B1:B" & $j)
Local $entryName = _Excel_RangeRead($dataEntryWorkbook, Default, "C1:C" & $j)
_Excel_BookClose($dataEntryWorkbook)

Local $allFilesDir = _FileListToArrayRec($filesDir, "*.xls;*.xlsx", $FLTAR_FILES + $FLTAR_NOHIDDEN, 0,0,2)
Local $allFilesDirSize = UBound($allFilesDir)
For $k = 1 To ($allFilesDirSize - 1) Step 1
    excelProcessing($allFilesDir[$k],$entrySheet,$entryCell,$entryName)
Next
_Excel_Close($oExcel)

The function "excelprocessing" what it does is open each excel, runs the sheets (the sheet may not exactly match the ones I have stored in the array, that's why I use StringInStr) when I find the sheet, I keep the value of the cell (which is in another array) and this I will insert later in a database.

Func excelProcessing(ByRef $excelPath, ByRef $arrayOfSheet, ByRef $arrayOfCell, ByRef $arrayOfName)
    Local $oExcel = _Excel_Open(False)
    Local $dataWorkbook = _Excel_BookOpen($oExcel, $excelPath, True, True)
    Local $serialNumber
    Local $date
    Local $sqlInsert
    Local $count
    Local $aWorkSheets = _Excel_SheetList($dataWorkbook)
    For $j = 0 To (UBound($aWorkSheets) - 1) Step 1
        $sqlInsert = "INSERT INTO BlahBlah (SerialNumber, Sheet, Cell, Name, Value) VALUES "
        $count = 0
        Local $getSerialAndDate = False
        For $i = 0 To (UBound($arrayOfCell) - 1) Step 1
            If (StringInStr ($aWorkSheets[$j][0], $arrayOfSheet[$i]) <> 0) Then
                If Not $getSerialAndDate Then
                    $serialNumber = _Excel_RangeRead($dataWorkbook, $aWorkSheets[$j][0], $arrayOfCell[$i], 3)
                    $date = _Excel_RangeRead($dataWorkbook, $aWorkSheets[$j][0], $arrayOfCell[$i + 1], 3)
                    $getSerialAndDate = True
                EndIf
                If ($date <> "") Then
                    Local $cell = $arrayOfCell[$i]
                    Local $value = _Excel_RangeRead($dataWorkbook, $aWorkSheets[$j][0], $cell, 3)
                    $sqlInsert = $sqlInsert & "('" & $serialNumber & "', '" & $aWorkSheets[$j][0] & "', '" & $cell & "', '" & $arrayOfName[$i] & "', '" & $value & "'),"
                    If ($value <> "") Then
                        $count = $count + 1
                    EndIf
                EndIf
            EndIf
        Next
        If ($date <> "") Then
            $toCheck = $serialNumber & $aWorkSheets[$j][0]
            verifyAndSave($toCheck,$count,$sqlInsert)
        EndIf
    Next
    _Excel_BookClose($dataWorkbook)
EndFunc

Is that information enough?
thanks water!

[Agu]

The first line of the function, which creates the instance of excel is wrong, I know.
I had an error when I copied the code

[water]

Which version of AutoIt do you run?

[Agu]

v3.3.14.2

[water]

The error message you see is returned by _Excel_SheetList.
Can you check that the following line does not return an error (means: @error <> 0)?

Local $dataWorkbook = _Excel_BookOpen($oExcel, $excelPath, True, True)

 

[Agu]

I added the following line of code

Func excelProcessing(ByRef $excelPath, ByRef $arrayOfSheet, ByRef $arrayOfCell, ByRef $arrayOfName)
    MsgBox(0,"CheckError", "Return value: " & _Excel_BookOpen($oExcel, $excelPath, True, True) & @CRLF & "Error value: " & @error & @CRLF & "Extended value: " & @extended)
    Local $dataWorkbook = _Excel_BookOpen($oExcel, $excelPath, True, True)
    Local $serialNumber
    Local $date
    .....
    .....
    .....

Which shows in the first run of for:

In the second run:

And when I accept the last message box:

[water]

I have created a stripped down version of your script:

#include <Excel.au3>
#include <File.au3>

Global $oExcel = _Excel_Open(False, False)
Global $sFilesDir = "C:\temp\"
Global $aAllFilesDir = _FileListToArrayRec($sFilesDir, "*.xls;*.xlsx", $FLTAR_FILES + $FLTAR_NOHIDDEN, 0, 0, 2)
For $i = 1 To $aAllFilesDir[0]
    _ExcelProcessing($aAllFilesDir[$i])
Next
_Excel_Close($oExcel)

Func _ExcelProcessing($sExcelPath)
    ConsoleWrite("Processing file: " & $sExcelPath & @CRLF)
    Local $oWorkbook = _Excel_BookOpen($oExcel, $sExcelPath, True, True)
    ConsoleWrite("  BookOpen: " & @error & @CRLF)
    Local $aWorkSheets = _Excel_SheetList($oWorkbook)
    ConsoleWrite("  SheetList: " & @error & @CRLF)
    _ArrayDisplay($aWorkSheets, "Worksheets in Workbook " & $sExcelPath)
    _Excel_BookClose($oWorkbook)
EndFunc   ;==>_ExcelProcessing

Does this work for you too or do you get an error?

[Agu]

Thanks for your help!
I placed 140 excel files to process in C: \ Temp
Something curious happens with the code you posted:
If I comment on the code line that has _ArrayDisplay, it processes them all without problems!
If I let the window of _ArrayDisplay appear (And show me the sheets that has the excel) at the beginning it processed:
29 files and gives error: 
$aSheets[$iIndex][0] = $oWorkbook.Sheets($iIndex + 1).Name
$aSheets[$iIndex][0] = $oWorkbook^ ERROR

I tried to run again, and at 31 files it gives error
I tried to run again, and at the 33 files it gives error
I tried to run again, and at the 27 files it gives error

Will it be something with odd numbers of processed files? (just kidding)
Anyway, I still do not understand what happens :/

[water]

If you replace the _ArrayDisplay line with "Sleep(500)" do the errors still appear?

[Agu]

Al reemplazarlo no da error
Probé también con Sleep(1000) y Sleep(2000) y tampoco da error

By way of comment:
I'm running Windows 7 64bit, Office 2013 32bit and I'm compiling (by default) in 32bit

[Agu]

sorry, language error haha
When replacing it does not give error
I also tried with Sleep (1000) and Sleep (2000) and it does not give an error either

[water]

Very, very strange.
You could try to reduce the sleep time so your script doesn't run forever.