Update the Excel UDF that comes with AutoIt

[GMK]

What about adding a few lines to have the visibility be the same as the attached window?

expandcollapse popup

; #FUNCTION# ====================================================================================================================
; Name...........: _Excel_Open
; Description ...: Connects to an existing Excel instance or creates a new Excel application object.
; Syntax.........: _Excel_Open([$bVisible = True[, $bDisplayAlerts = False[, $bScreenUpdating = True[, $bInteractive = True, [, $iCalculation = $xlCalculationAutomatic[, $bForceNew = False]]]]]])
; Parameters ....: $bVisible        - Optional: True specifies that the application will be visible (default = True)
;                 $bDisplayAlerts  - Optional: False suppresses all prompts and alert messages while opening a workbook (default = False)
;                 $bScreenUpdating - Optional: False suppresses screen updating to speed up your script (default = True)
;                 $bInteractive    - Optional: If False, Excel blocks all keyboard and mouse input by the user (except input to dialog boxes) (default = True)
;                 $iCalculation    - Optional: $xlCalculationManual suppresses calculation to speed up your script. Can be any of the XlCalculation enumeration (default = $xlCalculationAutomatic)
;                                              $xlCalculationAutomatic = -4105 (Excel controls recalculation)
;                                              $xlCalculationManual = -4135 (Calculation is done when the user requests it)
;                                              $xlCalculationSemiautomatic = 2 (Excel controls recalculation but ignores changes in tables)
;                 $bForceNew       - Optional: True forces to create a new Excel instance even if there is already a running instance (default = False)
; Return values .: Success - Returns the Excel application object. Sets @extended to:
;                 |0 - Excel was already running
;                 |1 - Excel was not running or $bForceNew was set to True. Excel has been started by this function
;                 Failure - Returns 0 and sets @error
;                 |1 - Error returned by ObjCreate. @extended is set to the error code returned by ObjCreate
; Author ........: water
; Modified ......: GMK
; Remarks .......: If $bDisplayAlerts is set to False and a message requires a response, Excel chooses the default response.
;+
;                 To enhance performance set $bScreenUpdating to False. The screen will not be updated until you set $oExcel.ScreenUpdating = True.
;+
;                 Blocking user input will prevent the user from interfering with the AutoIt script. If set to False, don't forget to set it back to True.
;+
;                 To enhance performance set $iCalculation to $xlCalculationManual. The workbooks will not be recalculated until you set $oExcel.Calculation = $xlCalculationAutomatic
; Related .......:
; Link ..........:
; Example .......: Yes
; ===============================================================================================================================
Func _Excel_Open($bVisible = Default, $bDisplayAlerts = Default, $bScreenUpdating = Default, $bInteractive = Default, $iCalculation = Default, $bForceNew = Default)
    Local $oExcel, $bApplCloseOnQuit = False
    If $bVisible = Default Then $bVisible = True
    If $bDisplayAlerts = Default Then $bDisplayAlerts = False
    If $bScreenUpdating = Default Then $bScreenUpdating = True
    If $bInteractive = Default Then $bInteractive = True
    If $iCalculation = Default Then $iCalculation = $xlCalculationAutomatic
    If $bForceNew = Default Then $bForceNew = False
    If Not $bForceNew Then
        If ProcessExists("excel.exe") Then
            $oExcel = ObjGet("", "Excel.Application")
            If Not @error Then $bVisible = $oExcel.Visible
        EndIf
    EndIf
    If $bForceNew Or Not IsObj($oExcel) Then
        $oExcel = ObjCreate("Excel.Application")
        If @error Or Not IsObj($oExcel) Then Return SetError(1, @error, 0)
        $bApplCloseOnQuit = True
    EndIf
    __Excel_CloseOnQuit($bApplCloseOnQuit)
    $oExcel.Visible = $bVisible
    $oExcel.DisplayAlerts = $bDisplayAlerts
    $oExcel.ScreenUpdating = $bScreenUpdating
    $oExcel.Interactive = $bInteractive
    $oExcel.Calculation = $iCalculation
    Return SetError(0, $bApplCloseOnQuit, $oExcel)
EndFunc   ;==>_Excel_Open

EDIT: Corrected mistakes.

Edited March 13, 2013 by GMK

[nitekram]

Opening with the same visibility would not work, as I do not want to see the file - just want to get array from it...

@Water - you must have updated your excel udf, as I only have 4 parameters in the one that I am using - BUT that did do the trick and resolved my issue with them being hidden - thanks

Here is what I am working with

expandcollapse popup

; #FUNCTION# ====================================================================================================================
; Name...........: _Excel_Open
; Description ...: Connects to an existing Excel instance or creates a new Excel Application Object.
; Syntax.........: _Excel_Open([$bVisible = True[, $bDisplayAlerts = False[, $bScreenUpdating = True[, $bForceNew = False]]]])
; Parameters ....: $bVisible        - Optional: True specifies that the application will be visible (default = True)
;                 $bDisplayAlerts  - Optional: False suppresses all prompts and alert messages while opening the workbook (default = False)
;                 $bScreenUpdating - Optional: False suppresses screen updating to speed up your script (default = True)
;                 $bForceNew       - Optional: True forces to create a new Excel instance even if there is already a running instance (default = False)
; Return values .: Success - Returns the Excel application object. Sets @extended to:
;                 |0 - Excel was already running
;                 |1 - Excel was not running or $bForceNew was set to True. Excel has been started by this function
;                 Failure - Returns 0 and sets @error
;                 |1 - Error returned by ObjCreate. @extended is set to the error code returned by ObjCreate
; Author ........: water
; Modified ......:
; Remarks .......: If $bDisplayAlerts is set to False and a message requires a response, Excel chooses the default response.
; Related .......:
; Link ..........:
; Example .......: Yes
; ===============================================================================================================================
Func _Excel_Open($bVisible = Default, $bDisplayAlerts = Default, $bScreenUpdating = Default, $bForceNew = Default)
 Local $oExcel, $bApplCloseOnQuit = False
 If $bVisible = Default Then $bVisible = True
 If $bDisplayAlerts = Default Then $bDisplayAlerts = False
 If $bScreenUpdating = Default Then $bScreenUpdating = True
 If $bForceNew = Default Then $bForceNew = False
 If Not $bForceNew Then $oExcel = ObjGet("", "Excel.Application")
 If $bForceNew Or @error Then
  $oExcel = ObjCreate("Excel.Application")
  If @error Or Not IsObj($oExcel) Then Return SetError(1, @error, 0)
  $bApplCloseOnQuit = True
 EndIf
 __Excel_CloseOnQuit($bApplCloseOnQuit)
 $oExcel.Visible = $bVisible
 $oExcel.DisplayAlerts = $bDisplayAlerts
 $oExcel.ScreenUpdating = $bScreenUpdating
 Return SetError(0, $bApplCloseOnQuit, $oExcel)
EndFunc   ;==>_Excel_Open

Edited March 13, 2013 by nitekram

[nitekram]

I do realize, you are still working on the Alpha release - so no worries.

[GMK]

So, if Excel is already running, you just want the workbook to open hidden, but the application should stay visible. Am I correct?

[nitekram]

So, if Excel is already running, you just want the workbook to open hidden, but the application should stay visible. Am I correct?

Yes, I have multiple excel docs opened, they must remain opened, and visible. My script, will open a different excel doc, that I do not want to see, as I am just putting that info into an array to be searched and then it populates my query in another area of my script. After I open that file, the array remains constant, and I never want to see the file again, and I close it with my script once the array has been poplulated, and then I use that array for the rest of the day.

[nitekram]

Maybe what should be the case in the code, is to default the condition, if script asks for it to be invisible then check if other instances are running and if so, then start a new process?

edit made it clear

Edited March 13, 2013 by nitekram

[GMK]

Perhaps in addition to the $bVisible parameter in _Excel_Open, we could have a similar parameter in _Excel_BookOpen.

[water]

Unfortunately there is no property to set a Workbook to invisible. It's a property of the application.

I will think about the default behaviour of _Excel_Open.

Thanks a lot for the discussion - it's very much appreciated!

[GMK]

How about this?

expandcollapse popup

; #FUNCTION# ====================================================================================================================
; Name...........: _Excel_BookOpen
; Description ...: Opens an existing workbook and returns its object identifier.
; Syntax.........: _Excel_BookOpen($oExcel, $sFilePath[, $bReadOnly = False[, $bVisible = Default[, $sPassword = Default[, $sWritePassword = Default]]])
; Parameters ....: $oExcel       - Excel application object where you want to open the workbook
;                 $sFilePath      - Path and filename of the file to be opened
;                 $bReadOnly      - Optional: Flag, whether to open the workbook as read-only (True or False) (default = False)
;                 $bVisible    - Optional: True specifies that the workbook window will be visible (default = True)
;                 $sPassword      - Optional: The password that was used to read-protect the workbook, if any (default is none)
;                 $sWritePassword - Optional: The password that was used to write-protect the workbook, if any (default is none)
; Return values .: Success - Returns workbook object identifier
;                 Failure - Returns 0 and sets @error:
;                 |1 - $oExcel is not an object
;                 |2 - Specified $sFilePatch does not exist
;                 |3 - Unable to open $sFilePath. @extended is set to the error code returned by the Open method
;                 Failure - Returns workbook object identifier and sets @error:
;                 |4 - Readwrite access could not be granted. Workbook might be open by another users/task.
; Author ........: SEO <locodarwin at yahoo dot com>
; Modified.......: litlmike, water, GMK
; Remarks .......: If you set $bReadOnly = False but the document can't be opened read-write @error is set to 4 and
;                 the workbook object identifier is returned (all other errors returns 0).
;                 You can alter the workbook but you have to use _Excel_BookSaveAs to save it to another location or with another name.
; Related .......: _Excel_BookAttach, _Excel_BookOpenText
; Link ..........:
; Example .......: Yes
; ===============================================================================================================================
Func _Excel_BookOpen($oExcel, $sFilePath, $bReadOnly = Default, $bVisible = Default, $sPassword = Default, $sWritePassword = Default)
    Local $oWorkbook
    If Not FileExists($sFilePath) Then Return SetError(2, 0, 0)
    If Not IsObj($oExcel) Then Return SetError(1, @error, 0)
    If $bReadOnly = Default Then $bReadOnly = False
    If $bVisible = Default Then $bVisible = True
    $oWorkbook = $oExcel.Workbooks.Open($sFilePath, Default, $bReadOnly, Default, $sPassword, $sWritePassword)
    If Not $bVisible Then $oExcel.Activewindow.Visible = False
    If @error Then Return SetError(3, @error, 0)
    ; Select the first visible worksheet
    If $bVisible Then
        For $i = 1 To $oWorkbook.Sheets.Count
            If $oWorkbook.Sheets($i).Visible = $xlSheetVisible Then
                $oWorkbook.Sheets($i).Select()
                ExitLoop
            EndIf
        Next
    EndIf
    ; If a read-write workbook was opened read-only then return an error
    If $bReadOnly = False And $oWorkbook.Readonly = True Then Return SetError(4, 0, $oWorkbook)
    Return $oWorkbook
EndFunc   ;==>_Excel_BookOpen

[water]

I modified the function a bit and will add the following code to the UDF:

expandcollapse popup

; #FUNCTION# ====================================================================================================================
; Name...........: _Excel_BookOpen
; Description ...: Opens an existing workbook and returns its object identifier.
; Syntax.........: _Excel_BookOpen($oExcel, $sFilePath[, $bReadOnly = False[, $bVisible = True[, $sPassword = Default[, $sWritePassword = Default]]]])
; Parameters ....: $oExcel       - Excel application object where you want to open the workbook
;                 $sFilePath      - Path and filename of the file to be opened
;                 $bReadOnly      - Optional: Flag, whether to open the workbook as read-only (True or False) (default = False)
;                 $bVisible    - Optional: True specifies that the workbook window will be visible (default = True)
;                 $sPassword      - Optional: The password that was used to read-protect the workbook, if any (default is none)
;                 $sWritePassword - Optional: The password that was used to write-protect the workbook, if any (default is none)
; Return values .: Success - Returns workbook object identifier
;                 Failure - Returns 0 and sets @error:
;                 |1 - $oExcel is not an object
;                 |2 - Specified $sFilePatch does not exist
;                 |3 - Unable to open $sFilePath. @extended is set to the error code returned by the Open method
;                 Failure - Returns workbook object identifier and sets @error:
;                 |4 - Readwrite access could not be granted. Workbook might be open by another users/task.
; Author ........: SEO <locodarwin at yahoo dot com>
; Modified.......: litlmike, water, GMK
; Remarks .......: If you set $bReadOnly = False but the document can't be opened read-write @error is set to 4 and
;                 the workbook object identifier is returned (all other errors returns 0).
;                 You can alter the workbook but you have to use _Excel_BookSaveAs to save it to another location or with another name.
; Related .......: _Excel_BookAttach, _Excel_BookOpenText
; Link ..........:
; Example .......: Yes
; ===============================================================================================================================
Func _Excel_BookOpen($oExcel, $sFilePath, $bReadOnly = Default, $bVisible = Default, $sPassword = Default, $sWritePassword = Default)
    Local $oWorkbook
    If Not FileExists($sFilePath) Then Return SetError(2, 0, 0)
    If Not IsObj($oExcel) Then Return SetError(1, @error, 0)
    If $bReadOnly = Default Then $bReadOnly = False
    If $bVisible = Default Then $bVisible = True
    $oWorkbook = $oExcel.Workbooks.Open($sFilePath, Default, $bReadOnly, Default, $sPassword, $sWritePassword)
    If @error Then Return SetError(3, @error, 0)
    If Not $bVisible Then
        $oExcel.Activewindow.Visible = False
    Else
        ; Select the first visible worksheet
        For $i = 1 To $oWorkbook.Sheets.Count
            If $oWorkbook.Sheets($i).Visible = $xlSheetVisible Then
                $oWorkbook.Sheets($i).Select()
                ExitLoop
            EndIf
        Next
    EndIf
    ; If a read-write workbook was opened read-only then return an error
    If $bReadOnly = False And $oWorkbook.Readonly = True Then Return SetError(4, 0, $oWorkbook)
    Return $oWorkbook
EndFunc   ;==>_Excel_BookOpen

Any comments?

[GMK]

Looks good to me. Sorry--I should have seen that SetError following the Open command and corrected it as you did.

[water]

No problem.

The next thing I'm gonna do is to implement your modified versions of _Excel_RangeRead and _Excel_RangeWrite.

[Spiff59]

The variables used to define $aTransposed remain intact and the array is never redimmed.

I think I'd replace 8 calls to Ubound() with simple references to the existing variables.

[water]

Thanks for the hint! I'm going to change the function accordingly.

[GMK]

The variables used to define $aTransposed remain intact and the array is never redimmed.

I think I'd replace 8 calls to Ubound() with simple references to the existing variables.

Edited referenced post. I can't test it at the moment, but would that make it any faster, or just easier to read? Edited March 15, 2013 by GMK

[water]

The code would be a bit faster, easier to read and it is good coding practice.

Releasing a large array saves memory.

[water]

What about adding a few lines to have the visibility be the same as the attached window?

How about the following behaviour:

• The visibility is only applied to the application object if specified by the user (True or False)
• Setting $bVisible = Default doesn't change the visibility
• When attaching to an already existing instance of Excel visibility wouldn't be changed
• When creating a new instance of Excel visibility would use the Microsoft default

Or do you think when a new instance is created visibility whould be set to False? Because we automate Excel and this should happen in the background (That's what I would vote for!)

What's your opinion?

Edited March 16, 2013 by water

[water]

Can someone with Excel 2003 please test this script? Just to check if the limit is 65536 rows or cells.

Thanks!

#include <Excel.au3>
$oExcel = _ExcelBookNew()
Global $aArray10[32768][2] = [[11,12],[21,22],[31,32],[41,42],[51,52]]
Global $aArray11[65536][2] = [[11,12],[21,22],[31,32],[41,42],[51,52]]
$aArray2 = $oExcel.WorksheetFunction.Transpose($aArray10)
MsgBox(0, "Transpose " & UBound($aArray10, 1) & " rows", "@error: " & @error)
$aArray2 = $oExcel.WorksheetFunction.Transpose($aArray11)
MsgBox(0, "Transpose " & UBound($aArray11, 1) & " rows", "@error: " & @error)
_ExcelBookClose($oExcel, 0)

[BrewManNH]

From the research I've done on this, it appears to be an array limit. Meaning Excel can't handle when a script tries to send it an array with more than 65535 elements. Which is an improvement over Excel 2000 which was limited to 5431 elements.

Edited March 16, 2013 by BrewManNH

[water]

Thanks for the feedback!

So both functions can be made simple if up to 65536 rows are to be processed by just calling the transpose method.

For more rows a loop and processing in chunks needs to be implemented.