Update the Excel UDF that comes with AutoIt

[DW1]

Hi water,

Does the new UDF comes with the ability to create and read excel form control? I had look thru and I don't think there is any. Correct me if I'm wrong.

At the moment, I need to create a combo form in the excel and place it at a specific cell and then read the value. Any advise or example you can give me on this? TIA 

Here is my take on the three functions:

_Excel_ControlAdd_Combo

_Excel_ControlAdd_ComboItem

_Excel_ControlRead_Combo

expandcollapse popup

Func _Excel_ControlAdd_Combo($oWorkbook, $vWorksheet, $vRangeOrLeft, $iTop = Default, $iWidth = Default, $iHeight = Default)
    Local $Return, $iPosLeft, $iPosTop
    If Not IsObj($oWorkbook) Or ObjName($oWorkbook, 1) <> "_Workbook" Then Return SetError(1, 0, 0)
    If Not IsObj($vWorksheet) Then
        If $vWorksheet = Default Then
            $vWorksheet = $oWorkbook.ActiveSheet
        Else
            $vWorksheet = $oWorkbook.WorkSheets.Item($vWorksheet)
        EndIf
        If @error Or Not IsObj($vWorksheet) Then Return SetError(2, @error, 0)
    ElseIf ObjName($vWorksheet, 1) <> "_Worksheet" Then
        Return SetError(2, @error, 0)
    EndIf
    If IsNumber($vRangeOrLeft) Then
        $iPosLeft = $vRangeOrLeft
        $iPosTop = $iTop
    Else
        If Not IsObj($vRangeOrLeft) Then
            $vRangeOrLeft = $vWorksheet.Range($vRangeOrLeft)
            If @error Or Not IsObj($vRangeOrLeft) Then Return SetError(3, @error, 0)
        EndIf
        $iPosLeft = $vRangeOrLeft.Left
        $iPosTop = $vRangeOrLeft.Top
    EndIf
    If IsNumber($vRangeOrLeft) Or ($vRangeOrLeft.Columns.Count = 1 And $vRangeOrLeft.Rows.Count = 1) Then
        If $iWidth = Default Or $iHeight = Default Then Return SetError(5, 0, 0)
        $Return = $vWorksheet.DropDowns.Add($iPosLeft, $iPosTop, $iWidth, $iHeight)
        If @error Then Return SetError(4, @error, 0)
    Else
        Local $iRw = $vRangeOrLeft.Width
        Local $iRh = $vRangeOrLeft.Height
        $Return = $vWorksheet.DropDowns.Add($iPosLeft, $iPosTop, $vRangeOrLeft.Width, $vRangeOrLeft.Height)
        If @error Then Return SetError(4, @error, 0)
    EndIf
    Return $Return
EndFunc   ;==>_Excel_ControlAdd_Combo

Func _Excel_ControlAdd_ComboItem(ByRef $hCombo, $sValue)
    If Not IsObj($hCombo) Then Return SetError(1, @error, 0)
    $hCombo.AddItem($sValue)
    If @error Then Return SetError(2, @error, 0)
EndFunc   ;==>_Excel_ControlAdd_ComboItem

Func _Excel_ControlRead_Combo(ByRef $hCombo)
    Local $Return, $iIndex
    If Not IsObj($hCombo) Then Return SetError(1, @error, 0)
    $iIndex = $hCombo.Value
    If $iIndex = 0 Then Return -1
    $Return = $hCombo.List($iIndex)
    If @error Then Return SetError(2, @error, 0)
    Return $Return
EndFunc   ;==>_Excel_ControlRead_Combo

 

Example included below.

expandcollapse popup

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

; Create application object and open an example workbook
Global $oAppl = _Excel_Open()
If @error <> 0 Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: Testing _Excel_ControlAdd_Combo function.", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
; Create new Workbook
Global $oWorkbook = _Excel_BookNew($oAppl)
If @error <> 0 Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: Testing _Excel_ControlAdd_Combo function Example", "Error creating workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oAppl)
    Exit
EndIf

Local $hEx1, $hEx2, $hEx3

$hEx1 = Example1($oWorkbook)
$hEx2 = Example2($oWorkbook)
$hEx3 = Example3($oWorkbook)
Example4($hEx1)
Example5($hEx1)

Exit

; *****************************************************************************
; Example 1
; Add a combo control that will fill the specified range.
; *****************************************************************************
Func Example1($oWorkbook)
    Local $oRet = _Excel_ControlAdd_Combo($oWorkbook, Default, "B2:G3")
    If @error <> 0 Then Return MsgBox($MB_SYSTEMMODAL, "Excel UDF: Testing _Excel_ControlAdd_Combo function Example 1", "Error adding combo control." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: Testing _Excel_ControlAdd_Combo function Example 1", "Added combo control at 'B2:G3', filling the size of the range.")
    Return $oRet
EndFunc   ;==>Example1

; *****************************************************************************
; Example 2
; Add a combo control at a specified cell location but set a specific width and height.
; *****************************************************************************
Func Example2($oWorkbook)
    Local $oRet = _Excel_ControlAdd_Combo($oWorkbook, Default, "B7", Default, 200, 20)
    If @error <> 0 Then Return MsgBox($MB_SYSTEMMODAL, "Excel UDF: Testing _Excel_ControlAdd_Combo function Example 2", "Error adding combo control." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: Testing _Excel_ControlAdd_Combo function Example 2", "Added combo control at 'B7' 200 in width, 20 in height.")
    Return $oRet
EndFunc   ;==>Example2

; *****************************************************************************
; Example 3
; Add a combo control at a specified left and top location while specifying width and height.
; *****************************************************************************
Func Example3($oWorkbook)
    Local $oRet = _Excel_ControlAdd_Combo($oWorkbook, Default, 200, 250, 300, 25)
    If @error <> 0 Then Return MsgBox($MB_SYSTEMMODAL, "Excel UDF: Testing _Excel_ControlAdd_Combo function Example 3", "Error adding combo control." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: Testing _Excel_ControlAdd_Combo function Example 3", "Added combo control at 200 left, 250 top, 300 in width, 25 in height.")
    Return $oRet
EndFunc   ;==>Example3

; *****************************************************************************
; Example 4
; Add comboitems to a combobox
; *****************************************************************************
Func Example4($hControl)
    _Excel_ControlAdd_ComboItem($hControl, "testing option1")
    If @error <> 0 Then MsgBox($MB_SYSTEMMODAL, "Excel UDF: Testing _Excel_ControlAdd_ComboItem", "Error adding comboitem." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_ControlAdd_ComboItem($hControl, "testing option2")
    If @error <> 0 Then MsgBox($MB_SYSTEMMODAL, "Excel UDF: Testing _Excel_ControlAdd_ComboItem", "Error adding comboitem." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_ControlAdd_ComboItem($hControl, "testing option3")
    If @error <> 0 Then MsgBox($MB_SYSTEMMODAL, "Excel UDF: Testing _Excel_ControlAdd_ComboItem", "Error adding comboitem." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
EndFunc   ;==>Example4

; *****************************************************************************
; Example 5
; Read a combobox and display the result in a MsgBox
; *****************************************************************************
Func Example5($hControl)
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: Testing _Excel_ControlAdd_ComboItem", "Added options to first example combobox." & @CRLF & @CRLF & "Please select one and click OK to have the selected option read.")
    Local $sResult = _Excel_ControlRead_Combo($hControl)
    If @error <> 0 Then MsgBox($MB_SYSTEMMODAL, "Excel UDF: Testing _Excel_ControlRead_Combo", "Error reading combo." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    If $sResult = -1 Then $sResult = "YOU DID NOT SELECT AN ITEM"
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: Testing _Excel_ControlAdd_ComboItem", "You selected: " & $sResult)
EndFunc   ;==>Example5

Func _Excel_ControlRead_Combo(ByRef $hCombo)
    Local $Return, $iIndex
    If Not IsObj($hCombo) Then Return SetError(1, @error, 0)
    $iIndex = $hCombo.Value
    If $iIndex = 0 Then Return -1
    $Return = $hCombo.List($iIndex)
    If @error Then Return SetError(2, @error, 0)
    Return $Return
EndFunc   ;==>_Excel_ControlRead_Combo

Func _Excel_ControlAdd_ComboItem(ByRef $hCombo, $sValue)
    If Not IsObj($hCombo) Then Return SetError(1, @error, 0)
    $hCombo.AddItem($sValue)
    If @error Then Return SetError(2, @error, 0)
EndFunc   ;==>_Excel_ControlAdd_ComboItem

Func _Excel_ControlAdd_Combo($oWorkbook, $vWorksheet, $vRangeOrLeft, $iTop = Default, $iWidth = Default, $iHeight = Default)
    Local $Return, $iPosLeft, $iPosTop
    If Not IsObj($oWorkbook) Or ObjName($oWorkbook, 1) <> "_Workbook" Then Return SetError(1, 0, 0)
    If Not IsObj($vWorksheet) Then
        If $vWorksheet = Default Then
            $vWorksheet = $oWorkbook.ActiveSheet
        Else
            $vWorksheet = $oWorkbook.WorkSheets.Item($vWorksheet)
        EndIf
        If @error Or Not IsObj($vWorksheet) Then Return SetError(2, @error, 0)
    ElseIf ObjName($vWorksheet, 1) <> "_Worksheet" Then
        Return SetError(2, @error, 0)
    EndIf
    If IsNumber($vRangeOrLeft) Then
        $iPosLeft = $vRangeOrLeft
        $iPosTop = $iTop
    Else
        If Not IsObj($vRangeOrLeft) Then
            $vRangeOrLeft = $vWorksheet.Range($vRangeOrLeft)
            If @error Or Not IsObj($vRangeOrLeft) Then Return SetError(3, @error, 0)
        EndIf
        $iPosLeft = $vRangeOrLeft.Left
        $iPosTop = $vRangeOrLeft.Top
    EndIf
    If IsNumber($vRangeOrLeft) Or ($vRangeOrLeft.Columns.Count = 1 And $vRangeOrLeft.Rows.Count = 1) Then
        If $iWidth = Default Or $iHeight = Default Then Return SetError(5, 0, 0)
        $Return = $vWorksheet.DropDowns.Add($iPosLeft, $iPosTop, $iWidth, $iHeight)
        If @error Then Return SetError(4, @error, 0)
    Else
        Local $iRw = $vRangeOrLeft.Width
        Local $iRh = $vRangeOrLeft.Height
        $Return = $vWorksheet.DropDowns.Add($iPosLeft, $iPosTop, $vRangeOrLeft.Width, $vRangeOrLeft.Height)
        If @error Then Return SetError(4, @error, 0)
    EndIf
    Return $Return
EndFunc   ;==>_Excel_ControlAdd_Combo

Edited March 11, 2014 by DW1

[water]

DW1,

thanks for the functions and the examples!

Do you think this form control functions should be added to the new Excel UDF or should they be placed in a separate Excel form control UDF? I would prefer the latter because it looks like not too many users are in need of such functions.

[DW1]

DW1,

thanks for the functions and the examples!

Do you think this form control functions should be added to the new Excel UDF or should they be placed in a separate Excel form control UDF? I would prefer the latter because it looks like not too many users are in need of such functions.

Separate for sure, as that would be a sizable UDF all on its own.

If it is going to become a UDF on its own, whoever is going to own it will want to rewrite the functions to be more like dealing with controls in AutoIt most likely.

[Dave10910]

Hi,
 
I'm having some issues with _excel_rangecopypaste. No matter what I seem to do I can't get it to paste values instead of a formula.

#include <MsgBoxConstants.au3>
#include <Excel Rewrite3.au3>


#AutoIt3Wrapper_run_debug_mode=Y

Global $oAppl = _Excel_Open()

Global $excel = _Excel_bookopen($oAppl, "C:\Users\' & @username & "\Desktop\TrackerTesting.xlsx")

_Excel_RangeCopyPaste($excel.Worksheets("Mapped"), "M2", "Q2", True, Default,$xlPasteValues, True)

Is there something I should be changing here?

Thanks for your time.

[water]

Separate for sure, as that would be a sizable UDF all on its own.

If it is going to become a UDF on its own, whoever is going to own it will want to rewrite the functions to be more like dealing with controls in AutoIt most likely.

Agree 100%.

Let's see how many users require such functions. Then I might decide to start an UDF as you described.

[water]

Thanks for your time.

Is there something I should be changing here?

According to the remarks in the function:

"If $vSourceRange and $vTargetRange are specified parameters $iPaste, $iOperation, $bSkipBlanks and $bTranspose are ignored".

Means:

You have to call _Excel_RangeCopyPaste two times. First to copy the range to the clipboard, then to paste the value to the target range.

See example 5 in _Excel_RangeCopyPaste.au3

[Dave10910]

According to the remarks in the function:

"If $vSourceRange and $vTargetRange are specified parameters $iPaste, $iOperation, $bSkipBlanks and $bTranspose are ignored".

Means:

You have to call _Excel_RangeCopyPaste two times. First to copy the range to the clipboard, then to paste the value to the target range.

See example 5 in _Excel_RangeCopyPaste.au3

 

Oh DUH!

Thanks for pointing that out.

[nitekram]

I missed something...my company upgraded me to windows 7, and I had been working with this, and it had been working. Now I get an error code

+>Beginning Import of EXCEL File... TIME = 14:09:55
!>14:10:01 AutoIt3.exe ended.rc:-1073741819
+>14:10:01 AutoIt3Wrapper Finished..
>Exit code: -1073741819    Time: 23.892

I have the newest version of autoit 3.3.10.2 installed. Has this been added to that version?

[water]

I'm using Windows 7 and AutoIt 3.3.10.2 myself without any problems.

Can you post the script you run?

[water]

Do you run Windows 7 32 or 64 bit?

Which version of Excel do you run?

Which bitness of Excel do you run (32/64 bit)?

[nitekram]

windows 7 32 bit service pack 1, excel is 2007

; Create application object and open an example workbook
    ; #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: False suppresses calculation to speed up your script. Can be any of the XlCalculation enumeration (default = $xlCalculationAutomatic)
    ;                  $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.
    ;                  To enhance performance set $bScreenUpdating to False. The screen will not be updated until you set $oExcel.ScreenUpdating = True.
    ;                  To enhance performance set $iCalculation to $xlCalculationManual. The workbooks will not be recalculated until you set $oExcel.Calculation = $xlCalculationAutomatic
    ;                  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.
    ; Related .......:
    ; Link ..........:
    ; Example .......: Yes
    ; ===============================================================================================================================
    Local $oOExcel = _Excel_Open(False, Default, Default, True, False, True) ;_Excel_Open(False)
    If @error <> 0 Then
        ;Return
        MsgBox(16, "Excel UDF: _Excel_Open " & $ExcelFile, "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    EndIf

Edited March 21, 2014 by nitekram

[water]

When I look at your first post then it seems that AutoIt crashes.

Can you post the whole script that causes problems?

I don't see the line "Beginning Import of EXCEL File" in the code you posted.

[nitekram]

shoot, so sorry, doing to many things at the same time, like trying to get all my applicaitons to work in Windows 7

expandcollapse popup

ConsoleWrite(@CRLF & '+>Beginning Import of EXCEL File... TIME = ' & @HOUR & ':' & @MIN & ':' & @SEC & @CRLF)

    ; #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
    ;                                If set to "Selection" or an abbreviation of it all selected cells of the active sheet will be used
    ;                  $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, an 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
    ; Remarks .......: Only the first selection will be returned by $vRange = "Selection"
    ; Related .......:
    ; Link ..........:
    ; Example .......: Yes
    ; ===============================================================================================================================
    $aArray = _Excel_RangeRead($oOExcel, $oWorkbook, Default, Default)

    $bReadExcelFileNG = True

    ; #FUNCTION# ====================================================================================================================
    ; Name...........: _Excel_BookClose
    ; Description ...: Closes the specified workbook.
    ; Syntax.........: _Excel_BookClose($oWorkbook[, $bSave = True])
    ; Parameters ....: $oWorkbook - Workbook object
    ;                  $bSave     - If True the workbook will be saved before closing (default = True)
    ; Return values .: Success - Returns 1
    ;                  Failure - Returns 0 and sets @error:
    ;                  |1 - $oWorkbook is not an object
    ;                  |2 - Error occurred when saving the workbook. @extended is set to the error code returned by the Save method
    ;                  |3 - Error occurred when closing the workbook. @extended is set to the error code returned by the Close method
    ; Author ........: SEO <locodarwin at yahoo dot com>
    ; Modified.......: 07/17/2008 by bid_daddy; litlmike, water
    ; Remarks .......: None
    ; Related .......:
    ; Link ..........:
    ; Example .......: Yes
    ; ===============================================================================================================================
    _Excel_BookClose($oWorkbook, False)

[water]

When you search the forum for -1073741819 you will find a lot of hits.

From the code you posted I'm not able to see a possible problem.

[nitekram]

Has this version been uploaded to the current version of AutoIt? If so, can you tell me what the include is? As I am still using your old code, and have your rewrite within my script.

[water]

The rewrite of the Excel UDF is not yet a part of the latest production version (3.3.10.2) nor a beta (3.3.11.x).

But I hope it will soon be part of the next beta.

[nitekram]

Is there any more error checking I can do. I do not understand that it worked fine before upgrade. I am in the mean time going to uninstall the latest version and see if that fixes the issue.

[nitekram]

Ok, running 3.3.8.1 has no problems, completes and no crash.

I cannot supply the excel file but it has a lot of blank cells and is broken down into two sections, the bottom section has no blank cells

the whole thing has CP cells across and 81 going down

The first section is only 39 down

there are a couple of blank rows between the two sections...not sure if this will help in your troulbeshooting.

[water]

The only problems that can arise are COM related. If you check @error after each call of an _Excel_* function you should be fine.

[nitekram]

I put that in there, and did not get any error with either version of AutoIt. Like I said, I downgraded the version of AutoIt and that worked, so not sure what is going on.