Excel workbook is an object, and not [SOLVED]

[SorryButImaNewbie]

Hello wise and respected users of this forum!

I  haven't used autoit for a long time, but now I try to use it to automate a process in excel (I may be better of using VBA, but I would like to refresh my knowladge)

I can open/close excel documents, and can interact with them with Send key commands, but I can't really use the _Excel functions

 

Func ExcelRead()
    ;Local $oExcel = WinGetHandle("Excel")
    ;WinActivate($oExcel)
    ;sleep(1000)
    Local $oExcel = ObjGet("", "Excel.Application")
    WinActivate($oExcel)
    WinActivate("Excel")
    ;$oExcel.Visible = 1
    ;$oExcel.workbooks.add

        If IsObj($oExcel) Then
            MsgBox($MB_SYSTEMMODAL, "", "The variable is an object")
        Else
            MsgBox($MB_SYSTEMMODAL, "", "The variable is not an object")
        EndIf

    Local $aResult = _Excel_RangeRead($oExcel, 1, "A1", 1)
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example 2", "Error reading from workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example 2", "Data successfully read." & @CRLF & "Please click 'OK' to display the formulas of cells A1:C1 of sheet 2.")
    _ArrayDisplay($aResult, "Excel UDF: _Excel_RangeRead Example 2 - Cells A1:C1 of sheet 2")

    #cs
    Local $ValamiExcel = WinGetHandle("Excel")
    Sleep(1000)
    WinActivate($ValamiExcel)
    Local $oRange = $ValamiExcel.ActiveSheet.Range("A").EntireColumn
    #ce
EndFunc

This is one of my function and I have a problem here.

IsObj tells me the $oExcel is an object, right after that I got the 

1 - $oWorkbook is not an object or not a workbook object

 error

Honestly I'm not sure what to do with that, I may have some problems with excel terms (workbook is the "entire" excel file that can have different worksheets right?), but i think my code should work and can't really work around this object not object problem.

Thank you for any insight or help that you can share with me! and of course have a nice day

 

Edited July 28, 2016 by SorryButImaNewbie

[water]

I suggest to:

• use the Excel UDF for all Excel related functions if possible. So I would replace

ObjGet("", "Excel.Application")

with

_Excel_Open

• Do not mix COM and GUI automation. Means drop all Win* functions.
• _Excel_RangeRead returns a string when reading a single cell and a 2D array when reading a range of cells.
• Your script would hence look like:

  Func ExcelRead()
      Local $oExcel = _Excel_Open()
      If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Error", "Excel open failed." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
      Local $oWorkbook = _Excel_BookOpen($oExcel, "C:\temp\test.xlsx")
      If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Error", "Excel book open failed." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
      Local $aResult = _Excel_RangeRead($oWorkbook, 1, "A1", 1)
      If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Error", "Error reading from workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
      MsgBox($MB_SYSTEMMODAL, "Excel", "Data successfully read." & @CRLF & "Please click 'OK' to display the value cell A1.")
      If IsArray($aResult) Then
          _ArrayDisplay($aResult, "Excel: Cell A1")
      Else
          MsgBox($MB_SYSTEMMODAL, "Excel", "Value ofr Cell A1: " & $aResult)
      EndIf
  EndFunc

   

[SorryButImaNewbie]

Thank you Master water!

I can read your code, I think, but can you explain it to me why do we need the $oWorkbook = _Excel_BookOpen part?

As I understand it would open an already existing worksheet right? Isn't  we already have one if we use _Excel_Open? Do I have to specifiy the sheet?

 

[water]

The new Excel UDF cleanly separates functions. That's one of the script breaking changes as described here.

_Excel_Open: Start up Excel or connect to a running instance. Returns the appication object
_Excel_BookOpen: Opens an existing Workbook. Returns the Workbook object.

 

[SorryButImaNewbie]

Aaaaahhhh

I see, and thanks for the link, and the help!