broliukaz Posted February 17, 2014 Share Posted February 17, 2014 Hi, maybe someone can help me? I use this: $XlsArray = _ExcelReadSheetToArray($oxlsServices,8,2,0,8) _ArrayDisplay($XlsArray, "Array") to start read from 8 row, 2nd column, and read all rows till 8 column. But this is not working at all, the array is empty at all. If I changed to: $XlsArray = _ExcelReadSheetToArray($oxlsServices,8,2,0,7) _ArrayDisplay($XlsArray, "Array") everything works fine, but there is no last column in the array. Excel file has 8 columns, starting from "B" (A column is empty). what I'm doing wrong? Link to comment Share on other sites More sharing options...
water Posted February 17, 2014 Share Posted February 17, 2014 What's the value of @error and @extended after you've called the function? 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...
broliukaz Posted February 17, 2014 Author Share Posted February 17, 2014 What's the value of @error and @extended after you've called the function? @error = 1 @extended = 0 Link to comment Share on other sites More sharing options...
water Posted February 17, 2014 Share Posted February 17, 2014 @error = 1 means: Specified object does not exist What do you get when you run: $XlsArray = _ExcelReadSheetToArray($oxlsServices,8,2) _ArrayDisplay($XlsArray, "Array") 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...
broliukaz Posted February 17, 2014 Author Share Posted February 17, 2014 @error = 1 means: Specified object does not exist What do you get when you run: $XlsArray = _ExcelReadSheetToArray($oxlsServices,8,2) _ArrayDisplay($XlsArray, "Array") I get the array with 7 columns, there is no last, 8 column. that's why I'm trying to use: $XlsArray = _ExcelReadSheetToArray($oxlsServices,8,2,0,8) If I add one more column and use: $XlsArray = _ExcelReadSheetToArray($oxlsServices,8,2) then I see the 8th column, but not the 9th (the new column). Link to comment Share on other sites More sharing options...
water Posted February 17, 2014 Share Posted February 17, 2014 You mean you opened the workbook and added a 9th column and when you reed the worksheet you don't get the 9th column? 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...
water Posted February 17, 2014 Share Posted February 17, 2014 I just tried and get @error = 3 (Count parameter out of range), @extended = 1 (Column count out of range). A few questions: Which version of AutoIt do you run? Which version of Excel do you run? The format of the workbook is XLS or XLSX? 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...
kylomas Posted February 17, 2014 Share Posted February 17, 2014 @water - I just ran his parms against the data generated by the help file example and it works perfectly. @broliukaz - Can you provide an example of the spreadsheet? Also, answer water's questions. Forum Rules Procedure for posting code "I like pigs. Dogs look up to us. Cats look down on us. Pigs treat us as equals." - Sir Winston Churchill Link to comment Share on other sites More sharing options...
broliukaz Posted February 17, 2014 Author Share Posted February 17, 2014 (edited) You mean you opened the workbook and added a 9th column and when you reed the worksheet you don't get the 9th column? Yes, exactly. But I have get the 8th column, which I have don't get before. It seems like it reads columns like: "columns = columns - 1" I'm using: AutiIT version: 3.3.10.2 Excel 2013 (Windows 7 32bit) file format is *.xls the file sample: https://dl.dropboxusercontent.com/u/13615378/Warranty_services.xls Edited February 17, 2014 by broliukaz Link to comment Share on other sites More sharing options...
water Posted February 17, 2014 Share Posted February 17, 2014 Unfortunately Dropbox is locked here. Can you please post the file here? The code of your script - or at least a reproducer - would be fine. 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...
broliukaz Posted February 17, 2014 Author Share Posted February 17, 2014 (edited) Oh, I'm sorry! Is there any way to upload a file or picture here? the function: Func SearchForService() Local $oxlsServices = _ExcelBookOpen($xlsServices, $fVisible) ; Open xls file Sleep(100) Local $XlsArray = _ExcelReadSheetToArray($oxlsServices,8,2,0,8) ;make an array from excel _ArrayDisplay($XlsArray, "Array") ; display the array $readSN = String($readSN) $readSN = StringStripWS($readSN,8) ; remove spaces Local $snPos = _ArraySearch($XlsArray, $readSN,0,0,0,0,1) ; searching for SN If $snPos < 0 Then Local $ArrayError If @error = 1 Then $ArrayError = "Array is not an array" If @error = 2 Then $ArrayError = "Array is not a 1 or 2 dimensional array" If @error = 3 Then $ArrayError = "Array is empty" If @error = 4 Then $ArrayError = "$iStart is greater than $iEnd" If @error = 6 Then $ArrayError = "Value was not found in array" If @error = 7 Then $ArrayError = "Array has too many dimensions" ;~ MsgBox(0, "Not Found", $readSN & ' was not found. $snPos: ' & $snPos & " Error: " & $ArrayError) GUICtrlSetData($ServiceBox,"SN: " & $readSN & ' - no service found in the "Warranty_services.xls" file') Else GUICtrlSetData($ServiceBox,"SN:"& $readSN & " has a service: " & @CRLF& _ $XlsArray[$snPos][3] &@CRLF & _ "PN: "& $XlsArray[$snPos][1] & @CRLF& _ "Warranty from: "& $XlsArray[$snPos][7] & @CRLF & _ "Warranty to: " & $XlsArray[$snPos][8] & @CRLF & _ "RF LOT NR: "& $XlsArray[$snPos][4] & @CRLF & _ "Project name: "& $XlsArray[$snPos][5] & @CRLF& _ "Contract Nr: "& $XlsArray[$snPos][6] & @CRLF) EndIf _ExcelBookClose($oxlsServices,0,0) $oxlsServices.Application.Quit $oxlsServices = 0 FileClose($xlsServices) EndFunc Edited February 17, 2014 by broliukaz Link to comment Share on other sites More sharing options...
water Posted February 17, 2014 Share Posted February 17, 2014 Sorry, but can't you upload the xls file? Click on Full Editor / Attachments. 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...
kylomas Posted February 17, 2014 Share Posted February 17, 2014 broliukaz, Opened your spreadsheet and added a 9TH column then changed the parms to '($oExcel,8,2,0,9)' and it is returning everything starting from row 8 column 2 through the end of data (including the column I just added) Forum Rules Procedure for posting code "I like pigs. Dogs look up to us. Cats look down on us. Pigs treat us as equals." - Sir Winston Churchill Link to comment Share on other sites More sharing options...
water Posted February 17, 2014 Share Posted February 17, 2014 May I suggest to have a look at my rewrite of the Excel UDF (download link can be found in my signature)? It will replace the Excel UDF in the next beta version of AutoIt. 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...
kylomas Posted February 17, 2014 Share Posted February 17, 2014 How is $readSN populated? Can you show enough code to get this to run? Forum Rules Procedure for posting code "I like pigs. Dogs look up to us. Cats look down on us. Pigs treat us as equals." - Sir Winston Churchill Link to comment Share on other sites More sharing options...
broliukaz Posted February 17, 2014 Author Share Posted February 17, 2014 Thakn you! Here is the file Warranty_services.xls Link to comment Share on other sites More sharing options...
broliukaz Posted February 17, 2014 Author Share Posted February 17, 2014 How is $readSN populated? Can you show enough code to get this to run? This small program is near 500 lines, and more few files... I think, you don't want to read him all the $readSN is entered in inputbox and its working correctly. The search is working too, the only problem is the array with -1 column I dont need to search in the last column, but I need to display it, when found the $readSN number. Link to comment Share on other sites More sharing options...
kylomas Posted February 17, 2014 Share Posted February 17, 2014 I think, you don't want to read him all Yes, it is a bit early here (6:30AM) This is working for me (changed the code somewhat)... expandcollapse popup; After opening a workbook and returning its object identifier, ; Fill some cells and Read the Values into an Array, using various paramaters. #include <Excel.au3> #include <Array.au3> #include <MsgBoxConstants.au3> #include <GUIConstantsEx.au3> #AutoIt3Wrapper_Add_Constants=n local $xlsServices = @scriptdir & '\warranty_services.xls' local $gui010 = guicreate('',500,200) Local $aSize = WinGetClientSize($gui010) local $readSN = guictrlcreateinput('',20,20,400,20) local $servicebox = guictrlcreatelabel('',20,50,400,250) guisetstate() while 1 switch guigetmsg() case $GUI_EVENT_CLOSE Exit case $readSN SearchForService(stringstripws(guictrlread($readSN),3)) EndSwitch wend Func SearchForService($str) $oxlsServices = _ExcelBookOpen($xlsServices,0) ; Open xls file Sleep(100) Local $XlsArray = _ExcelReadSheetToArray($oxlsServices,8,2,0,8) ;make an array from excel Local $snPos = _ArraySearch($XlsArray, $str) ; searching for SN If $snPos < 0 Then Local $ArrayError If @error = 1 Then $ArrayError = "Array is not an array" If @error = 2 Then $ArrayError = "Array is not a 1 or 2 dimensional array" If @error = 3 Then $ArrayError = "Array is empty" If @error = 4 Then $ArrayError = "$iStart is greater than $iEnd" If @error = 6 Then $ArrayError = "Value was not found in array" If @error = 7 Then $ArrayError = "Array has too many dimensions" ;~ MsgBox(0, "Not Found", $readSN & ' was not found. $snPos: ' & $snPos & " Error: " & $ArrayError) GUICtrlSetData($ServiceBox,"SN: " & $str & ' - no service found in the "Warranty_services.xls" file') Else GUICtrlSetData($ServiceBox,"SN:"& $str & " has a service: " & @CRLF& _ $XlsArray[$snPos][3] &@CRLF & _ "PN: "& $XlsArray[$snPos][1] & @CRLF& _ "Warranty from: "& $XlsArray[$snPos][7] & @CRLF & _ "Warranty to: " & $XlsArray[$snPos][8] & @CRLF & _ "RF LOT NR: "& $XlsArray[$snPos][4] & @CRLF & _ "Project name: "& $XlsArray[$snPos][5] & @CRLF& _ "Contract Nr: "& $XlsArray[$snPos][6] & @CRLF) EndIf _ExcelBookClose($oxlsServices,0,0) EndFunc kylomas Forum Rules Procedure for posting code "I like pigs. Dogs look up to us. Cats look down on us. Pigs treat us as equals." - Sir Winston Churchill Link to comment Share on other sites More sharing options...
kylomas Posted February 17, 2014 Share Posted February 17, 2014 Streamlined your function a bit... expandcollapse popup#include <Excel.au3> #include <Array.au3> #include <MsgBoxConstants.au3> #include <GUIConstantsEx.au3> #AutoIt3Wrapper_Add_Constants=n Local $xlsServices = @ScriptDir & '\warranty_services.xls' Local $gui010 = GUICreate('', 500, 200) Local $aSize = WinGetClientSize($gui010) Local $readSN = GUICtrlCreateInput('', 20, 20, 400, 20) Local $servicebox = GUICtrlCreateLabel('', 20, 50, 400, 250) GUISetState() While 1 Switch GUIGetMsg() Case $GUI_EVENT_CLOSE Exit Case $readSN SearchForService(StringStripWS(GUICtrlRead($readSN), 3)) EndSwitch WEnd Func SearchForService($str) $oxlsServices = _ExcelBookOpen($xlsServices, 0) ; Open xls file Local $XlsArray = _ExcelReadSheetToArray($oxlsServices, 8, 2, 0, 8) ;make an array from excel local $snpos = _ArraySearch($XlsArray, $str) ; searching for SN switch @error case 1,2,3,4,7 GUICtrlSetData($servicebox, "Parameter Error - See Help File for @error = "& @error) case 6 GUICtrlSetData($servicebox, "SN: " & $str & ' - no service found in the "Warranty_services.xls" file') case 0 GUICtrlSetData($servicebox, "SN:" & $str & " has a service: " & @CRLF & _ $XlsArray[$snPos][3] & @CRLF & _ "PN: " & $XlsArray[$snPos][1] & @CRLF & _ "Warranty from: " & $XlsArray[$snPos][7] & @CRLF & _ "Warranty to: " & $XlsArray[$snPos][8] & @CRLF & _ "RF LOT NR: " & $XlsArray[$snPos][4] & @CRLF & _ "Project name: " & $XlsArray[$snPos][5] & @CRLF & _ "Contract Nr: " & $XlsArray[$snPos][6] & @CRLF) endswitch _ExcelBookClose($oxlsServices, 0, 0) EndFunc ;==>SearchForService Forum Rules Procedure for posting code "I like pigs. Dogs look up to us. Cats look down on us. Pigs treat us as equals." - Sir Winston Churchill Link to comment Share on other sites More sharing options...
broliukaz Posted February 17, 2014 Author Share Posted February 17, 2014 Streamlined your function a bit... expandcollapse popup#include <Excel.au3> #include <Array.au3> #include <MsgBoxConstants.au3> #include <GUIConstantsEx.au3> #AutoIt3Wrapper_Add_Constants=n Local $xlsServices = @ScriptDir & '\warranty_services.xls' Local $gui010 = GUICreate('', 500, 200) Local $aSize = WinGetClientSize($gui010) Local $readSN = GUICtrlCreateInput('', 20, 20, 400, 20) Local $servicebox = GUICtrlCreateLabel('', 20, 50, 400, 250) GUISetState() While 1 Switch GUIGetMsg() Case $GUI_EVENT_CLOSE Exit Case $readSN SearchForService(StringStripWS(GUICtrlRead($readSN), 3)) EndSwitch WEnd Func SearchForService($str) $oxlsServices = _ExcelBookOpen($xlsServices, 0) ; Open xls file Local $XlsArray = _ExcelReadSheetToArray($oxlsServices, 8, 2, 0, 8) ;make an array from excel local $snpos = _ArraySearch($XlsArray, $str) ; searching for SN switch @error case 1,2,3,4,7 GUICtrlSetData($servicebox, "Parameter Error - See Help File for @error = "& @error) case 6 GUICtrlSetData($servicebox, "SN: " & $str & ' - no service found in the "Warranty_services.xls" file') case 0 GUICtrlSetData($servicebox, "SN:" & $str & " has a service: " & @CRLF & _ $XlsArray[$snPos][3] & @CRLF & _ "PN: " & $XlsArray[$snPos][1] & @CRLF & _ "Warranty from: " & $XlsArray[$snPos][7] & @CRLF & _ "Warranty to: " & $XlsArray[$snPos][8] & @CRLF & _ "RF LOT NR: " & $XlsArray[$snPos][4] & @CRLF & _ "Project name: " & $XlsArray[$snPos][5] & @CRLF & _ "Contract Nr: " & $XlsArray[$snPos][6] & @CRLF) endswitch _ExcelBookClose($oxlsServices, 0, 0) EndFunc ;==>SearchForService I try both of your scripts (copy - paste to new file, not my program) , but both shows the same error... Is this really working for you? I added the screenshot: 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