Jump to content
gogomarkni

Get excel sheets name to GUICtrlCreateCombo

Recommended Posts

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

 

Share this post


Link to post
Share on other sites
water

Check the returncode of _Excel_SheetList


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
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)

Share this post


Link to post
Share on other sites
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,

 

Share this post


Link to post
Share on other sites
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 by MikahS
expltn

Snips & Scripts


My Snips: graphCPUTemp ~ getENVvars
My 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

 

Share this post


Link to post
Share on other sites
water

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 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
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 ==========================

 

Share this post


Link to post
Share on other sites
water

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 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

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

×