gogomarkni Posted May 18, 2015 Share Posted May 18, 2015 hi,I create a Create a Button and ComboBox,want to when click the button and select one excel file,the sheets name will list into the ComboBox,but I always get the error result: ==> Array variable has incorrect number of subscripts or subscript dimension range exceeded.: $combo_items &= $array[$i]&"|" $combo_items &= ^ ERRORCan someone help me to find where is wrong?and is it possible to get $array = _Excel_SheetList($oWorkBook) with open the excel file??thanks, $button_srcfile =GUICtrlCreateButton("Single File", 20, 10, 120, 20) GUICtrlSetFont(-1, 9, 400, 0, "Arial") GUICtrlSetOnEvent(-1, "SrcFilePressed") $path_src=GUICtrlCreateInput("",140,10,360,20) $Label1 = GUICtrlCreateLabel("Sheet Name", 50, 35, 120, 20) GUICtrlSetFont(-1, 9, 400, 0, "Arial") $input_src_sheet = GUICtrlCreateCombo("", 140, 35, 360, 20) Func SrcFilePressed() Local $srcfile=FileOpenDialog("Select result Excel File",@ScriptDir &"\","Excel (*.xls;*.xlsx)",1+4) GUICtrlSetData($path_src, $srcfile) $oExcel = _Excel_Open() $oWorkBook = _Excel_BookOpen($oExcel,$srcfile) $array = _Excel_SheetList($oWorkBook) ;_ArrayDisplay($array) $combo_items = "" For $i = 1 to UBound($array) - 1 If $i = UBound($array) - 1 Then $combo_items &= $array[i] Else $combo_items &= $array[i]&"|" Endif Next GUICtrlSetData($input_src_sheet, $combo_items) EndFunc ;==>SrcFilePressed Link to comment Share on other sites More sharing options...
water Posted May 18, 2015 Share Posted May 18, 2015 Check the returncode of _Excel_SheetList 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...
gogomarkni Posted May 18, 2015 Author Share Posted May 18, 2015 I don't quit understand how to check returncode of _Excel_SheetList ,but _ArrayDisplay($array) is displayed all worksheets name correctly.$array = _Excel_SheetList($oWorkBook) _ArrayDisplay($array) Link to comment Share on other sites More sharing options...
gogomarkni Posted May 18, 2015 Author Share Posted May 18, 2015 Maybe I already find what's wrong , the _Excel_SheetList will be 2D array @@so $combo_items &=$array[ ][ ] , I'm trying how to modify it . sorry for my poor English and typoIs it possible to get $array = _Excel_SheetList($oWorkBook) "without" open the excel file??at least open excel file in the background ?thanks, Link to comment Share on other sites More sharing options...
MikahS Posted May 18, 2015 Share Posted May 18, 2015 (edited) a two-dimensional zero based array with the following information: 0 - Name of the worksheet 1 - Object of the worksheet This is what is returned from _Excel_SheetList. If you were trying to read the name of the worksheet it is as such:Local $sheetList = _Excel_SheetList($oWorkBook) MsgBox(0, "", "My name is: " & $sheetList[0][0])EDIT: You must have a workbook object, so you must open excel before you call _Excel_SheetList. Edited May 18, 2015 by MikahS expltn Snips & Scripts My Snips: graphCPUTemp ~ getENVvarsMy Scripts: Short-Order Encrypter - message and file encryption V1.6.1 ~ AuPad - Notepad written entirely in AutoIt V1.9.4 Feel free to use any of my code for your own use. Forum FAQ Link to comment Share on other sites More sharing options...
water Posted May 18, 2015 Share Posted May 18, 2015 Please check the help file for function _Excel_Open to see how to start Excel invisible. 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...
TesterITITIT Posted May 19, 2015 Share Posted May 19, 2015 Hi All,I just joined autoIT forums, and need to use autoit to do the following , anyone have any scripts or advice? I am using the below script to try to do this1) Excel1 with col a,b,c,d,e2) run autoit script3) script auto create excel2 with col a,c,e columns onlyI am facing problems with the line in red below, as i do not know how to select only column a, c and e only Start of Script ==========================#include <Excel.au3>#include <MsgBoxConstants.au3>; Create application object and open an example workbookLocal $oAppl = _Excel_Open()If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended); Open Workbook 2Local $oWorkbook2 = _Excel_BookOpen($oAppl, @ScriptDir & "\Extras\_Excel3.xls", True)If @error Then MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example", "Error opening workbook '" & @ScriptDir & "\Extras\_Excel3.xls'." & @CRLF & "@error = " & @error & ", @extended = " & @extended) _Excel_Close($oAppl) ExitEndIf; Open Workbook 1Local $oWorkbook1 = _Excel_BookOpen($oAppl, @ScriptDir & "\Extras\_Excel2.xls", True)If @error Then MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example", "Error opening workbook '" & @ScriptDir & "\Extras\_Excel2.xls'." & @CRLF & "@error = " & @error & ", @extended = " & @extended) _Excel_Close($oAppl) ExitEndIf; *****************************************************************************; Copy a single cell from another workbook. Pass the source range as object.; *****************************************************************************Local $oRange = $oWorkbook2.Worksheets(1).Range;("A:A","C:C")_Excel_RangeCopyPaste($oWorkbook1.Worksheets(1), $oRange, "A1")If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example 2", "Error copying cells." & @CRLF & "@error = " & @error & ", @extended = " & @extended)MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example 2", "Range 'A1' from workbook _Excel3.xls successfully copied to 'G15'.")End of Script ========================== Link to comment Share on other sites More sharing options...
water Posted May 19, 2015 Share Posted May 19, 2015 Use the function 3 times to copy each column individualy Local $oRange = $oWorkbook2.Worksheets(1).Range("A:A") 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...
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