Update the Excel UDF that comes with AutoIt

[nitekram]

Not sure...maybe someone else can test? I might be able to test on Windows 7 soon...I will let you know.

[water]

I would like to see anyone else test this on Windows XP.

I don't like this kind of unsolved poblems

[nitekram]

OK - had a coworker using XP Pro SP3 with 2010 version of Office try to run my script - he received an AutoIt Error on line 5405 (have no clue), so I turned on your COM Error Handler and this is what came up - FYI the application continued to run and was able to get data from the array.

FYI - since I am not using your DEFAULT parameter and hard coding the lines, as it does not pull anything into the array as said before - I used the production version of AutoIt 3.3.8.1 (I will try later to have it compiled with Beta version of AutoIt and see if I get any other messages).

@AutoItVersion=3.3.8.1

@AutoItX64=0

@Compileted = 1

@OSArch=X86

@OSVersin=Win_XP

@Scriptline=-1

NumberHex=80020009

Number= -2147352567

WinDescritpion=

Description=Save Method of Workbook class failed

Source=Microsoft Excel

HelpFile=xlmain11.chm

Helpcontext=0

LastDllError=0

EDIT - AND it did create a copied version on his computer - in the My Documents root of his username

Edited February 10, 2013 by nitekram

[water]

The UDF has only about 1700 lines. So which script do you test? It can't be the reproducer script I posted!

[nitekram]

Well my script is only 4761 lines, so it is not mine??? But I did have him use my script not yours...as he is only able to test a couple a day, I have exhausted the use of him for today. I will try testing in the middle of the week, as that is when I am back in - sorry.

[water]

Nitekram,

to track down an error best practice is to use the smallest reproducer script possible.

How on earth can you make sure that your 5000 liner doesn't create the copy?

Please use my 10 liner reproducer script! Else I'm sorry to tell you that I can't help you - because here everything runs perfect and no one else has reported the same problem.

[nitekram]

will do, next time - I will post when I have more info - thanks

[water]

I'm eager to know if it works.

[nitekram]

OK - sorry it took so long, but getting testers is hard work. The dialog box opened and user was able to click file, but the file never opened as the _arraydisplay() never opened.

Here is the output

Error encountered in waters reproducer script.exe:

@AutoItVersion = 3.3.9.4

@AutoItX64 = 0

@Compiled = 1

Description =

@OSArch = X86

Scriptline = -1

@OSVersion = WIN_XP

NumberHex = 80020006

Number = -2147352570

WinDescription = Unknown name.

Source =

HelpFile =

HelpContext = 585553341

LastDllError = 0

Here is the reproducer I used: Notice I just added the time stamp to the code, and that is not included in the output

expandcollapse popup

_COMError_Notify(2)

Func _COMError_Notify($iDebug, $sDebugFile = Default)

    Static Local $avDebugState[3] = [0, "", 0] ; Debugstate, Debugfile and AutoIt.Error object
    If $sDebugFile = Default Or $sDebugFile = "" Then $sDebugFile = @ScriptDir & "\COMError_Debug.txt"
    If Not IsInt($iDebug) Or $iDebug < -1 Or $iDebug > 3 Then Return SetError(1, 0, 0)
    Switch $iDebug
        Case -1
            Return $avDebugState
        Case 0
            $avDebugState[0] = 0
            $avDebugState[1] = ""
            $avDebugState[2] = 0
        Case Else
            If $iDebug = 2 And $sDebugFile = "" Then Return SetError(4, 0, 0)
            ; A COM error handler will be initialized only if one does not exist
            If ObjEvent("AutoIt.Error") = "" Then
                $avDebugState[2] = ObjEvent("AutoIt.Error", "__COMError_Handler") ; Creates a custom error handler
                If @error <> 0 Then Return SetError(2, @error, 0)
                $avDebugState[0] = $iDebug
                $avDebugState[1] = $sDebugFile
                Return SetError(0, 1, 1)
            ElseIf ObjEvent("AutoIt.Error") = "__COMError_Handler" Then
                Return SetError(0, 0, 1) ; COM error handler already set by a previous call to this function
            Else
                Return SetError(3, 0, 0) ; COM error handler already set to another function
            EndIf
    EndSwitch
    Return

EndFunc   ;==>_COMError_Notify

Func __COMError_Handler($oCOMError)

    Local $sTitle = "AutoIt COM error handler"
    Local $avDebugState = _COMError_Notify(-1)
    Local $sError = "Error encountered in " & @ScriptName & ":" & @CRLF & _
            "  @AutoItVersion = " & @AutoItVersion & @CRLF & _
            "  @AutoItX64 = " & @AutoItX64 & @CRLF & _
            "  @Compiled = " & @Compiled & @CRLF & _
            "  @OSArch = " & @OSArch & @CRLF & _
            "  @OSVersion = " & @OSVersion & @CRLF & _
            "  Scriptline = " & $oCOMError.Scriptline & @CRLF & _
            "  NumberHex = " & Hex($oCOMError.Number, 8) & @CRLF & _
            "  Number = " & $oCOMError.Number & @CRLF & _
            "  WinDescription = " & StringStripWS($oCOMError.WinDescription, 2) & @CRLF & _
            "  Description = " & StringStripWS($oCOMError.Description, 2) & @CRLF & _
            "  Source = " & $oCOMError.Source & @CRLF & _
            "  HelpFile = " & $oCOMError.HelpFile & @CRLF & _
            "  HelpContext = " & $oCOMError.HelpContext & @CRLF & _
            "  LastDllError = " & $oCOMError.LastDllError & @CRLF
    Switch $avDebugState[0]
        Case 1
            MsgBox(64, $sTitle & '1', $sError)
            ConsoleWrite($sTitle & " - " & $sError & @CRLF)
            FileWrite(@ScriptDir & '\error_1.txt', @YEAR & "." & @MON & "." & @MDAY & " " & @HOUR & ":" & @MIN & ":" & @SEC & " " & $sTitle & @CRLF & $sError)
        Case 2
            MsgBox(64, $sTitle & '2', $sError)
            ConsoleWrite($sTitle & @CRLF & $sError)
            FileWrite(@ScriptDir & '\error_2.txt', @YEAR & "." & @MON & "." & @MDAY & " " & @HOUR & ":" & @MIN & ":" & @SEC & " " & $sTitle & @CRLF & $sError)
        Case 3
            MsgBox(64, $sTitle & '3', $sError)
            FileWrite($avDebugState[1], @YEAR & "." & @MON & "." & @MDAY & " " & @HOUR & ":" & @MIN & ":" & @SEC & " " & $sTitle & _
                    " - " & $sError & @CRLF)
            FileWrite(@ScriptDir & '\error_3.txt', @YEAR & "." & @MON & "." & @MDAY & " " & @HOUR & ":" & @MIN & ":" & @SEC & " " & $sTitle & @CRLF & $sError)

    EndSwitch

EndFunc   ;==>__COMError_Handler


#include "Excel Rewrite.au3"
#include <array.au3>

Const $sPath = '\\cscsvrndc001\users\mthompson49\shared\' ; 'c:\'


$ExcelFile = FileOpenDialog('Look GCAR report spreadsheet', $sPath, "Excel (*.xls;*.xlsx)", 1 + 2)
Global $oExcel = _Excel_Open(False)
If @error <> 0 Then Exit MsgBox(16, "Excel UDF: _Excel_Open " & $ExcelFile, "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Global $oWorkbook = _Excel_BookOpen($oExcel, $ExcelFile, True)
If @error Then
    MsgBox(16, "Excel UDF: _Excel_BookOpen " & $ExcelFile, "Error opening '" & $ExcelFile & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Else
    $aArrayExcel = _Excel_RangeRead($oExcel, $oWorkbook, Default, "A7:CC1200")
    If @error Then MsgBox(16, "Excel UDF: _Excel_RangeRead " & $ExcelFile, "Error reading '" & $ExcelFile & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
EndIf

_ArrayDisplay($aArrayExcel)

_Excel_BookClose($oExcel, $oWorkbook)
_Excel_Close($oExcel)

Edited February 14, 2013 by nitekram

[water]

The COM error message "Unknown name." is fine. It's triggered by _Excel_Open trying to connec to an existing instance. When no instance exists then the COM error message appears.

But which of the MsgBoxes do you then get?

"Error opening '" & $ExcelFile .. or

"Error reading '" & $ExcelFile

[nitekram]

Neither, only one the tester gets is the Case 2 in the function __COMError_Handler($oCOMError)

[water]

At the moment I have absolutely no idea what's going on.

I hope to do some more testing tomorrow.

[GMK]

water, there seems to be a limitation of the Transpose function of 2 ^ 16 elements. For those that are larger, I suppose it would have to be done in multiple chunks. I can try my hand at incorporating this into the _Excel_RangeRead function and let you know my findings. Thanks again for your work on this rewrite.

[water]

I would be glad to hear about any limitations you find. At the moment development of the UDF has nearly come to a halt because I'm very, very busy.

But I hope I can put some more time into extending the UDF and move from an Alpha to a Beta release in the near future.

[GMK]

Whenever there are more than 2^16 elements (65536) in an array, the Transpose function returns a "type mismatch" error. To get around this, here was my fix (which you may be able to optimize):

expandcollapse popup

; #FUNCTION# ====================================================================================================================
; Name...........: _Excel_RangeRead
; Description ...: Reads the value, formula or displayed text from a cell or range of cells of the specified workbook and worksheet.
; Syntax.........: _Excel_RangeRead($oExcel[, $oWorkbook = Default[, $oWorksheet = Default[, $vRange = Default[, $iReturn = 1]]]])
; Parameters ....: $oExcel   - Excel application object
;                 $oWorkbook  - Optional: Excel workbook object. If set to Default the active workbook will be used
;                 $oWorksheet - Optional: Excel worksheet object. If set to Default the active sheet will be used
;                 $vRange    - Optional: Either a range object or an A1 range. If set to Default all used cells will be processed
;                 $iReturn    - Optional: What to return of the specified cell:
;                 |1 - Value (default)
;                 |2 - Formula
;                 |3 - The displayed text
; Return values .: Success - Returns the data from the specified cell(s). A string for a cell, a zero-based array for a range of cells.
;                 Failure - Returns 0 and sets @error:
;                 |1 - $oExcel is not an object
;                 |2 - $oWorkbook is not an object
;                 |3 - $oWorksheet is not an object
;                 |4 - $vRange is invalid
;                 |5 - Parameter $iReturn is invalid. Has to be > 1 and < 3
;                 |6 - Error occurred when reading data. @extended is set to the error code returned when accessing the Value property
; Author ........: SEO <locodarwin at yahoo dot com>
; Modified.......: litlmike, water, GMK
; Remarks .......:
; Related .......:
; Link ..........:
; Example .......: Yes
; ===============================================================================================================================
Func _Excel_RangeRead($oExcel, $oWorkbook = Default, $oWorksheet = Default, $vRange = Default, $iReturn = Default)
    If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
    If $oWorkbook = Default Then $oWorkbook = $oExcel.ActiveWorkbook
    If Not IsObj($oWorkbook) Then Return SetError(2, 0, 0)
    If $oWorksheet = Default Then $oWorksheet = $oWorkbook.ActiveSheet
    If Not IsObj($oWorksheet) Then Return SetError(3, 0, 0)
    If $vRange = Default Then $vRange = $oWorksheet.UsedRange
    If IsString($vRange) Then $vRange = $oWorksheet.Range($vRange)
    If Not IsObj($vRange) Then Return SetError(4, 0, 0)
    If $iReturn = Default Then $iReturn = 1
    If $iReturn < 1 Or $iReturn > 3 Then Return SetError(5, 0, 0)
    Local $vResult
    If $vRange.Columns.Count * $vRange.Rows.Count > 65536 Then
        Local $aRange = StringRegExp($vRange.Address, "\w+", 3)
        Local $sStartCol = $aRange[0]
        Local $iStartRow = Int($aRange[1])
        Local $sEndCol = $aRange[2]
        Local $iEndRow = Int($aRange[3])
        Local $iChunk = Int(65536 / $vRange.Columns.Count)
        Local $iEndRangeRow = $iStartRow + $iChunk - 1
        While 1
            Local $aTemp = 0
            If $iEndRangeRow > $iEndRow Then $iEndRangeRow = $iEndRow
            Local $oTempRange = $oWorksheet.Range($sStartCol & $iStartRow & ":" & $sEndCol & $iEndRangeRow)
            Switch $iReturn
                Case 1
                    $aTemp = $oExcel.Transpose($oTempRange.Value)
                Case 2
                    $aTemp = $oExcel.Transpose($oTempRange.Formula)
                Case Else
                    $aTemp = $oExcel.Transpose($oTempRange.Text)
            EndSwitch
            If @error Then Return SetError(6, @error, 0)
            If Not IsArray($vResult) Then
                Local $iChunkStart = 0
                Local $vResult[UBound($aTemp)][UBound($aTemp, 2)]
            Else
                $iChunkStart = UBound($vResult)
                ReDim $vResult[UBound($vResult) + UBound($aTemp)][UBound($vResult, 2)]
            EndIf
            For $iRow = 0 To UBound($aTemp) - 1
                For $iCol = 0 To UBound($aTemp, 2) - 1
                    $vResult[$iRow + $iChunkStart][$iCol] = $aTemp[$iRow][$iCol]
                Next
            Next
            If $iEndRangeRow = $iEndRow Then ExitLoop
            $iStartRow = $iEndRangeRow + 1
            $iEndRangeRow = $iStartRow + $iChunk - 1
        WEnd
    Else
        Switch $iReturn
            Case 1
                $vResult = $oExcel.Transpose($vRange.Value)
            Case 2
                $vResult = $oExcel.Transpose($vRange.Formula)
            Case Else
                $vResult = $oExcel.Transpose($vRange.Text)
        EndSwitch
        If @error Then Return SetError(6, @error, 0)
    EndIf
    Return $vResult
EndFunc   ;==>_Excel_RangeRead

Edited March 13, 2013 by GMK

[water]

GMK,

Thanks a lot for this optimization! Will add it to the UDF.

As I'm very busy at the moment it will take some time before I can release a new alpha or even a beta version of the UDF.

[GMK]

expandcollapse popup

; #FUNCTION# ====================================================================================================================
; Name...........: _Excel_RangeWrite
; Description ...: Write value(s) or formula(s) to a cell or a cell range on the specified workbook and worksheet.
; Syntax.........: _Excel_RangeWrite($oExcel, $oWorkbook, $oWorksheet, $vValue, $vRangeOrRow[, $iColumn = 1[, $iArrayRowStart = 0[, $iArrayColStart = 0[, $bValue = True]]]])
; Parameters ....: $oExcel       - Excel application object
;                 $oWorkbook      - Excel workbook object. If set to Default the active workbook will be used
;                 $oWorksheet    - Excel worksheet object. If set to Default the active sheet will be used
;                 $vValue        - Can be a string, a 1D or 2D array containing the data to be written to the worksheet
;                 $vRangeOrRow    - Either an A1 range (only valid when $vValue is a string) or an integer row number to write to if using R1C1
;                 $iColumn      - Optional: The column to write to if using R1C1 (default = 1)
;                 $iArrayRowStart - Optional: Array index base for rows (default is 0)
;                 $iArrayColStart - Optional: Array index base for columns (default is 0)
;                 $bValue        - Optional: If True the $vValue will be written to the value property. If False $vValue will be written to the formula property (default = True)
; Return values .: Success - Returns 1
;                 Failure - Returns 0 and sets @error:
;                 |1 - $oExcel is not an object
;                 |2 - $oWorkbook is not an object
;                 |3 - $oWorksheet is not an object
;                 |4 - Parameter out of range. Sets @extended:
;                 |    0 - $vRangeOrRow out of range
;                 |    1 - $iColumn out of range
;                 |5 - Base index out of range. Sets @extended:
;                 |    0 - $iArrayRowStart out of range
;                 |    1 - $iArrayColStart out of range
;                 |6 - Error occurred when writing data. @extended is set to the COM error code
; Author ........: SEO <locodarwin at yahoo dot com>
; Modified.......: litlmike and PsaltyDS 01/04/08 - 2D version _Excel_RangeWrite(), Golfinhu (improved speed), water, GMK
; Remarks .......:
; Related .......:
; Link ..........:
; Example .......: Yes
; ===============================================================================================================================
; >>> Angegebenes Workbook muss aktiviert werden oder $oWorksheet nach $oWorkBook.$oWorksheet ändern? (testen mit Sheet2 etc.)
Func _Excel_RangeWrite($oExcel, $oWorkbook, $oWorksheet, $vValue, $vRangeOrRow, $iColumn = Default, $iArrayRowStart = Default, $iArrayColStart = Default, $bValue = Default)
    If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
    If $oWorkbook = Default Then $oWorkbook = $oExcel.ActiveWorkbook
    If Not IsObj($oWorkbook) Then Return SetError(2, 0, 0)
    If $oWorksheet = Default Then $oWorksheet = $oWorkbook.ActiveSheet
    If Not IsObj($oWorksheet) Then Return SetError(3, 0, 0)
    If $iColumn = Default Then $iColumn = 1
    If $iArrayRowStart = Default Then $iArrayRowStart = 0
    If $iArrayColStart = Default Then $iArrayColStart = 0
    If $bValue = Default Then $bValue = True
    If $iColumn < 1 Then Return SetError(4, 1, 0)
    Local $iDims = UBound($vValue, 0), $iArrayRowSize = UBound($vValue, 1), $iArrayColSize = UBound($vValue, 2)
    If $iArrayColSize = 0 Then $iArrayColSize = 1
    If $iArrayRowStart > $iArrayRowSize Then Return SetError(5, 0, 0)
    If $iArrayColStart > $iArrayColSize Then Return SetError(5, 1, 0)
    Local $iLastRow, $iLastCol
    If Not IsArray($vValue) Then
        If Not StringRegExp($vRangeOrRow, "[A-Z,a-z]", 0) Then
            If $bValue Then
                $oWorksheet.Cells($vRangeOrRow, $iColumn).Value = $vValue
            Else
                $oWorksheet.Cells($vRangeOrRow, $iColumn).Formula = $vValue
            EndIf
        Else
            If $bValue Then
                $oWorksheet.Range($vRangeOrRow).Value = $vValue
            Else
                $oWorksheet.Range($vRangeOrRow).Formula = $vValue
            EndIf
        EndIf
    Else
        If $vRangeOrRow < 1 Then Return SetError(4, 0, 0)
        Local $iNewRowArraySize = $iArrayRowSize - $iArrayRowStart
        Local $iNewColArraySize = $iArrayColSize - $iArrayColStart
        $iLastRow = $vRangeOrRow + $iNewRowArraySize - 1
        $iLastCol = $iColumn + $iNewColArraySize - 1
        ; Create a transposed new array and add new values
        Local $aTransposed[$iNewColArraySize][$iNewRowArraySize]
        For $i = $iArrayRowStart To $iArrayRowSize - 1
            For $j = $iArrayColStart To $iArrayColSize - 1
                If $iDims = 2 Then
                    $aTransposed[$j - $iArrayColStart][$i - $iArrayRowStart] = $vValue[$i][$j]
                Else
                    $aTransposed[$j - $iArrayColStart][$i - $iArrayRowStart] = $vValue[$i]
                EndIf
            Next
        Next
        Local $oRange
        #forceref $oRange
        If UBound($aTransposed) * UBound($aTransposed, 2) > 65536 Then
            Local $iTransposedRows = UBound($aTransposed)
            Local $iTransposedCols = UBound($aTransposed, 2)
            Local $iChunk = Int(65536 / $iTransposedRows)
            Local $iStartCol = 0
            Local $iEndCol = $iStartCol + $iChunk - 1
            While 1
                Local $aTemp[$iTransposedRows][$iEndCol - $iStartCol + 1]
                For $iRow = 0 To $iTransposedRows - 1
                    For $iCol = 0 To $iEndCol - $iStartCol
                        $aTemp[$iRow][$iCol] = $aTransposed[$iRow][$iCol + $iStartCol]
                    Next
                Next
                $oRange = $oWorksheet.Range($oWorksheet.Cells($vRangeOrRow + $iStartCol, $iColumn), $oWorksheet.Cells($vRangeOrRow + $iEndCol, UBound($aTemp)))
                If $bValue = 1 Then
                    $oRange.Value = $aTemp
                Else
                    $oRange.Formula = $aTemp
                EndIf
                If @error Then Return SetError(6, @error, 0)
                If $iEndCol = $iTransposedCols - 1 Then ExitLoop
                $iStartCol = $iEndCol + 1
                $iEndCol = $iStartCol + $iChunk - 1
                If $iEndCol > $iTransposedCols - 1 Then $iEndCol = $iTransposedCols - 1
                $aTemp = 0
            WEnd
        Else
            $oRange = $oWorksheet.Range($oWorksheet.Cells($vRangeOrRow, $iColumn), $oWorksheet.Cells($iLastRow, $iLastCol))
            If $bValue = 1 Then
                $oRange.Value = $aTransposed
            Else
                $oRange.Formula = $aTransposed
            EndIf
            If @error Then Return SetError(6, @error, 0)
        EndIf
    EndIf
    Return 1
EndFunc   ;==>_Excel_RangeWrite

EDIT: Referenced UBounds instead of calling them 8 times.

Edited March 15, 2013 by GMK

[water]

Thanks GMK!

I hope to release a new alpha version with your changes quite soon.

[nitekram]

OK, maybe you addressed this already, and I missed what you were saying, but I have the same issue, each time. If I have already opened excel docs and I run your UDF

expandcollapse popup

_COMError_Notify(2)

Func _COMError_Notify($iDebug, $sDebugFile = Default)

Static Local $avDebugState[3] = [0, "", 0] ; Debugstate, Debugfile and AutoIt.Error object
If $sDebugFile = Default Or $sDebugFile = "" Then $sDebugFile = @ScriptDir & "\COMError_Debug.txt"
If Not IsInt($iDebug) Or $iDebug < -1 Or $iDebug > 3 Then Return SetError(1, 0, 0)
Switch $iDebug
Case -1
Return $avDebugState
Case 0
$avDebugState[0] = 0
$avDebugState[1] = ""
$avDebugState[2] = 0
Case Else
If $iDebug = 2 And $sDebugFile = "" Then Return SetError(4, 0, 0)
; A COM error handler will be initialized only if one does not exist
If ObjEvent("AutoIt.Error") = "" Then
$avDebugState[2] = ObjEvent("AutoIt.Error", "__COMError_Handler") ; Creates a custom error handler
If @error <> 0 Then Return SetError(2, @error, 0)
$avDebugState[0] = $iDebug
$avDebugState[1] = $sDebugFile
Return SetError(0, 1, 1)
ElseIf ObjEvent("AutoIt.Error") = "__COMError_Handler" Then
Return SetError(0, 0, 1) ; COM error handler already set by a previous call to this function
Else
Return SetError(3, 0, 0) ; COM error handler already set to another function
EndIf
EndSwitch
Return

EndFunc ;==>_COMError_Notify

Func __COMError_Handler($oCOMError)

Local $sTitle = "AutoIt COM error handler"
Local $avDebugState = _COMError_Notify(-1)
Local $sError = "Error encountered in " & @ScriptName & ":" & @CRLF & _
" @AutoItVersion = " & @AutoItVersion & @CRLF & _
" @AutoItX64 = " & @AutoItX64 & @CRLF & _
" @Compiled = " & @Compiled & @CRLF & _
" @OSArch = " & @OSArch & @CRLF & _
" @OSVersion = " & @OSVersion & @CRLF & _
" Scriptline = " & $oCOMError.Scriptline & @CRLF & _
" NumberHex = " & Hex($oCOMError.Number, 8) & @CRLF & _
" Number = " & $oCOMError.Number & @CRLF & _
" WinDescription = " & StringStripWS($oCOMError.WinDescription, 2) & @CRLF & _
" Description = " & StringStripWS($oCOMError.Description, 2) & @CRLF & _
" Source = " & $oCOMError.Source & @CRLF & _
" HelpFile = " & $oCOMError.HelpFile & @CRLF & _
" HelpContext = " & $oCOMError.HelpContext & @CRLF & _
" LastDllError = " & $oCOMError.LastDllError & @CRLF
Switch $avDebugState[0]
Case 1
MsgBox(64, $sTitle & '1', $sError)
ConsoleWrite($sTitle & " - " & $sError & @CRLF)
FileWrite(@ScriptDir & '\error_1.txt', @YEAR & "." & @MON & "." & @MDAY & " " & @HOUR & ":" & @MIN & ":" & @SEC & " " & $sTitle & @CRLF & $sError)
Case 2
MsgBox(64, $sTitle & '2', $sError)
ConsoleWrite($sTitle & @CRLF & $sError)
FileWrite(@ScriptDir & '\error_2.txt', @YEAR & "." & @MON & "." & @MDAY & " " & @HOUR & ":" & @MIN & ":" & @SEC & " " & $sTitle & @CRLF & $sError)
Case 3
MsgBox(64, $sTitle & '3', $sError)
FileWrite($avDebugState[1], @YEAR & "." & @MON & "." & @MDAY & " " & @HOUR & ":" & @MIN & ":" & @SEC & " " & $sTitle & _
" - " & $sError & @CRLF)
FileWrite(@ScriptDir & '\error_3.txt', @YEAR & "." & @MON & "." & @MDAY & " " & @HOUR & ":" & @MIN & ":" & @SEC & " " & $sTitle & @CRLF & $sError)

EndSwitch

EndFunc ;==>__COMError_Handler


#include "Excel Rewrite.au3"
#include <array.au3>

Const $sPath = '\\cscsvrndc001\users\mthompson49\shared\' ; 'c:\'


$ExcelFile = FileOpenDialog('Look GCAR report spreadsheet', $sPath, "Excel (*.xls;*.xlsx)", 1 + 2)
Global $oExcel = _Excel_Open(False)
If @error <> 0 Then Exit MsgBox(16, "Excel UDF: _Excel_Open " & $ExcelFile, "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Global $oWorkbook = _Excel_BookOpen($oExcel, $ExcelFile, True)
If @error Then
MsgBox(16, "Excel UDF: _Excel_BookOpen " & $ExcelFile, "Error opening '" & $ExcelFile & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Else
$aArrayExcel = _Excel_RangeRead($oExcel, $oWorkbook, Default, "A7:CC1200")
If @error Then MsgBox(16, "Excel UDF: _Excel_RangeRead " & $ExcelFile, "Error reading '" & $ExcelFile & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
EndIf

_ArrayDisplay($aArrayExcel)

_Excel_BookClose($oExcel, $oWorkbook)
_Excel_Close($oExcel)

to open not visable - it hides my already opened excel docs - should it do that? I can reproduce each time.

@AutoItVersion = 3.3.8.1

@AutoItX64 = 0

@Compiled = 0

@OSArch = X86

@OSVersion = WIN_XP

EDIT - also if I close my script the files I have opened remain non visible but still running in the back ground, as I see the temp file that is created and am able to open all my files when I open just one of the temp files

edit, fixed spelling

Edited March 13, 2013 by nitekram

[water]

I know we have been discussing this subject before. But can't remember the result.

I guess you open the other workbooks ("my already opened excel docs") manually. As _Excel_Open tries to connect to an already running instance of Excel by default and the invisible parameter is a property of the Excel application the whole application becomes invisible - including your already opened workbooks.

User

_Excel_Open(False, Default, Default, Default, Default, True)

to solve this problem by starting a new instance of the Excel application. Edited March 13, 2013 by water