Andreu Posted June 21, 2013 Share Posted June 21, 2013 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 Link to comment Share on other sites More sharing options...
water Posted June 21, 2013 Share Posted June 21, 2013 (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 June 22, 2013 by water My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Andreu Posted June 22, 2013 Author Share Posted June 22, 2013 (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 ) 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 Link to comment Share on other sites More sharing options...
water Posted June 22, 2013 Share Posted June 22, 2013 Is $oExcel the application or workbook object? My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Andreu Posted June 23, 2013 Author Share Posted June 23, 2013 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 Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now