_Excel UDF - ReadSheetToArray from Attached Workbook

[Andreu]

Hello!

Been some time since I've posted here... I've been doing a lot of work in Excel recently at work, and while I'm very fluent in VB and have gotten by with everything I needed with it so far... There are certain limitations I wish to overcome when I'm doing our business reports at work.

What I'm Trying To Do:

Attach to a already open Excel workbook, and read the active sheet to an array.

The Problem:

This is where I believe the problem is originating

#include <Excel.au3>
_ExcelReadSheetToArray($oExcel [, $iStartRow = 1 [, $iStartColumn = 1 [, $iRowCnt = 0 [, $iColCnt = 0 [, $iColShift = False]]]]])


Parameters
$oExcel Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()

The first parameter, is an object returned by BookOpen and BookNew. What about BookAttach?

Here is my code, being what I've tried so far... Using the object from BookAttach doesn't seem to work.

#include <Excel.au3>
#include <Array.au3>

HotKeySet("{INSERT}", "_Begin")
HotKeySet("{END}", "_End")

While 1
   Sleep(250)
WEnd

Func _Begin()
   Local $sTitle = WinGetTitle("[Active]")
   Local $oExcel = _ExcelBookAttach($sTitle, "Title")
   If @Error = 1 Then MsgBox(0, "Error", "Error: (Failure) - Unable to attach."); ErrChk

   $aArray = _ExcelReadSheetToArray($oExcel, 1, 1, 1) ;Read 2 Columns
   _ArrayDisplay($aArray, "Read 2 Columns")
EndFunc

Func _End()
   Exit
EndFunc

More experienced insight is greatly appreciated!

-Thanks,

Andreu

[water]

_ExcelBookAttach returns a workbook object.

As _ExcelReadSheetoToArray needs an application object you need to do something like this:

#include <Excel.au3>
#include <Array.au3>

HotKeySet("{INSERT}", "_Begin")
HotKeySet("{END}", "_End")

While 1
   Sleep(250)
WEnd

Func _Begin()
   Local $sTitle = WinGetTitle("[Active]")
   Local $oWorkbook = _ExcelBookAttach($sTitle, "Title")
   If @Error = 1 Then MsgBox(0, "Error", "Error: (Failure) - Unable to attach."); ErrChk

   $aArray = _ExcelReadSheetToArray($oWorkbook.Parent, 1, 1, 1) ;Read 2 Columns
   _ArrayDisplay($aArray, "Read 2 Columns")
EndFunc

Func _End()
   Exit
EndFunc

Or you could have a look at my rewrite of the Excel UDF. Both UDFs are not compatible but the rewrite supports the new file formats and is more flexible.

Edited June 22, 2013 by water

[Andreu]

I don't seem to be able to switch which sheet is active with the present UDF... I've tried many variations, and this is always my result:

>"C:\Program Files (x86)\AutoIt3\SciTE\..\autoit3.exe" /ErrorStdOut "C:\Users\Dreu\Desktop\Adherence\Adherence.au3"
C:\Program Files (x86)\AutoIt3\Include\Excel.au3 (694) : ==> The requested action with this object has failed.:
Return $oExcel.Activesheet.Cells($sRangeOrRow, $iColumn).Value
Return $oExcel.Activesheet.Cells($sRangeOrRow, $iColumn)^ ERROR
>Exit code: 1    Time: 10.812

Here is the function that is calling it:

Func AssocData()
   _ExcelSheetActivate($oExcel, "CSR-DETAIL")
   If @Error <> 0 Then
      MsgBox(0, "Error", "Error Switching Sheets: " & @Error)
   EndIf
EndFunc

The @Error is not even triggering...

EDIT:
Just opened the UDF and line 694 is pertaining to a _ExcelReadCell call o.O ... let me look into this a bit more, may have jumped the gun accusing _ExcelSheetActivate. (The bug just started when I decided to incorporate switching sheets, so I assumed )

EDIT2:

$Test = _ExcelReadCell($oExcel, $row, 3)
If @Error <> 0 Then MsgBox(0, "Error", "338049 - " & @Error) 

I have added similar error checking to EVERY occurence of _ExcelReadCell to try and pinpoint the problem... 

Edited June 22, 2013 by Andreu

[water]

Is $oExcel the application or workbook object?

[Andreu]

I rewrote the entire script, mostly for optimization reasons... but also to change the way it opened the spreadsheets from the ground up (had to incorporate a GUI for user input as to which regions are pulled from the report)

Somewhere through this process I resolved my problem.

$oExcel was opened by the function below. This function never changed, as I reused it in the new script. 

Func _OpenWorkBooks()
   $sFilePath1 = @ScriptDir & "\" & $sTitle
   _SetStatus("Site ("&$site&") - Opening workbook: " & $sFilePath1)
   $oExcel = _ExcelBookOpen($sFilePath1, 1)
   If @error = 1 Then
      _SetStatus("Error Description: Unable to create the Excel Object")
      MsgBox(0, "Error!", "367001")
      Exit
   ElseIf @error = 2 Then
      _SetStatus("Error Description: The file does not exist. Ensure ADH files are in the program directory.")
      MsgBox(0, "Error!", "200876")
      Exit
   EndIf
   _ExcelSheetActivate($oExcel, "Managers Tab")
   $startRow = _GetStartingRow()
   $endRow = _GetEndingRow()
EndFunc