Get excel sheets name to GUICtrlCreateCombo

[gogomarkni]

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 &= ^ ERROR

Can 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

 

[water]

Check the returncode of _Excel_SheetList

[gogomarkni]

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)

[gogomarkni]

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 typo

Is it possible to get $array = _Excel_SheetList($oWorkBook) "without" open the excel file??

at least open excel file in the background ?

thanks,

 

[MikahS]

 

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

[water]

Please check the help file for function _Excel_Open to see how to start Excel invisible.

[TesterITITIT]

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 this

1) Excel1 with col a,b,c,d,e

2) run autoit script

3) script auto create excel2 with col a,c,e columns only

I 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 workbook
Local $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 2
Local $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)
    Exit
EndIf
; Open Workbook 1
Local $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)
    Exit
EndIf

; *****************************************************************************
; 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 ==========================

 

[water]

Use the function 3 times to copy each column individualy  

Local $oRange = $oWorkbook2.Worksheets(1).Range("A:A")