Sign in to follow this  
Followers 0
Andreu

_Excel UDF - ReadSheetToArray from Attached Workbook

5 posts in this topic

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

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

_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 by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

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 :P )

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 by Andreu

Share this post


Link to post
Share on other sites

Is $oExcel the application or workbook object?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0