Jump to content
Sign in to follow this  
joeloyzaga

worksheet select dialog

Recommended Posts

joeloyzaga

I have an excel spreadsheet that I want to be able to select which worksheet to process - much like a file select dialog but for which worksheet I want to process/open. Is there a command to do this or a function to call?

it has to be some sort of gui li8ke the fileselect dialog

Joe

Share this post


Link to post
Share on other sites
kylomas

joeloyzaga,

This should give you the idea for a sheet selection dialog...

; *** Start added by AutoIt3Wrapper ***
#include <GUIConstantsEx.au3>
; *** End added by AutoIt3Wrapper ***

#include <Constants.au3>
#include <Excel.au3>
#include <Array.au3>

#AutoIt3Wrapper_Add_Constants=n

;---------------------------------------------------------------------------------------------------
; create an Excel test file
;---------------------------------------------------------------------------------------------------

Local $ExcelFile = @DesktopDir & '\testfile.xls', $ret

If FileExists($ExcelFile) Then
    $ret = FileDelete($ExcelFile)
    If $ret = 0 Then
        MsgBox($mb_systemmodal, '*** ERROR ***', 'File not deleted...file = ' & $ExcelFile)
        Exit
    EndIf
EndIf

Local $oExcel = _ExcelBookNew()

If @error = 1 Then
    MsgBox($mb_ok, 'Excel Error', 'Excel Object does not exist' & @LF & 'Object = ' & $oExcel)
    Exit
EndIf

$ret = _ExcelBookSaveAs($oExcel, $ExcelFile, "xls", 0, 1)

If $ret = 0 Then
    Switch @error
        Case 1
            ConsoleWrite('Object does not exist' & @LF)
        Case 2
            ConsoleWrite('Invalid file type string' & @LF)
        Case 3
            ConsoleWrite('File exists and overwrite flag not set' & @LF)
    EndSwitch
EndIf

_ExcelBookClose($oExcel, 1)

;---------------------------------------------------------------------------------------
; run function _select_sheet and display the sheet name selected
;---------------------------------------------------------------------------------------

msgbox($mb_ok,'My Excel Sheet', _select_sheet($excelfile))

;---------------------------------------------------------------------------------------
; select sheet function
;---------------------------------------------------------------------------------------

func _select_sheet($excel_file)

    $oExcel = _ExcelBookOpen($ExcelFile, 0)

    If $oExcel = 0 Then
        Switch @error
            Case 1
                ConsoleWrite('Object create failed' & @LF)
            Case 2
                ConsoleWrite('File Open Failed...File = ' & $ExcelFile & @LF)
        EndSwitch
    EndIf

    Local $aMySheets = _ExcelSheetList($oExcel)

    local $gui010   =   guicreate('Excel Sheet Selector')
    local $lst010   =   guictrlcreatelist('',20,20,100,50)

    For $1 = 1 To UBound($aMySheets) - 1
        guictrlsetdata($lst010,$aMySheets[$1])
    Next

    _ExcelBookClose($oExcel, 1)

    guisetstate()

    local $savesheet

    while 1
        switch guigetmsg()
            case $gui_event_close
                Exit
            case $lst010
                $savesheet = guictrlread($lst010)
                guidelete($gui010)
                return $savesheet
        endswitch
    wend

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

Share this post


Link to post
Share on other sites
joeloyzaga

this is my code where I select the spreqadsheet but it still lists an empty sheet - ????

; *** Start added by AutoIt3Wrapper ***
#include <GUIConstantsEx.au3>
; *** End added by AutoIt3Wrapper ***
#include <Constants.au3>
#include <Excel.au3>
#include <Array.au3>
#AutoIt3Wrapper_Add_Constants=n
;---------------------------------------------------------------------------------------------------
; create an Excel test file
;---------------------------------------------------------------------------------------------------
;Local $ExcelFile = @DesktopDir & '\testfile.xls', $ret
Local $message = "Hold down Ctrl or Shift to choose multiple files."
Local  $ExcelFile = FileOpenDialog($message, @WindowsDir & "\", "Images (*.xls)", 1 + 4)
If @error Then
    MsgBox(4096, "", "No File(s) chosen",5)
Else
    $ExcelFile = StringReplace( $ExcelFile, "|", @CRLF)
    MsgBox(4096, "", "You chose " &  $ExcelFile,1)
EndIf
If FileExists($ExcelFile) Then
    $ret = FileDelete($ExcelFile)
    If $ret = 0 Then
        MsgBox($mb_systemmodal, '*** ERROR ***', 'File not deleted...file = ' & $ExcelFile)
        Exit
    EndIf
EndIf
Local $oExcel = _ExcelBookNew()
If @error = 1 Then
    MsgBox($mb_ok, 'Excel Error', 'Excel Object does not exist' & @LF & 'Object = ' & $oExcel)
    Exit
EndIf
$ret = _ExcelBookSaveAs($oExcel, $ExcelFile, "xls", 0, 1)
If $ret = 0 Then
    Switch @error
        Case 1
            ConsoleWrite('Object does not exist' & @LF)
        Case 2
            ConsoleWrite('Invalid file type string' & @LF)
        Case 3
            ConsoleWrite('File exists and overwrite flag not set' & @LF)
    EndSwitch
EndIf
_ExcelBookClose($oExcel, 1)
;---------------------------------------------------------------------------------------
; run function _select_sheet and display the sheet name selected
;---------------------------------------------------------------------------------------
msgbox($mb_ok,'My Excel Sheet', _select_sheet($excelfile))
;---------------------------------------------------------------------------------------
; select sheet function
;---------------------------------------------------------------------------------------
func _select_sheet($excel_file)
    $oExcel = _ExcelBookOpen($ExcelFile, 0)
    If $oExcel = 0 Then
        Switch @error
            Case 1
                ConsoleWrite('Object create failed' & @LF)
            Case 2
                ConsoleWrite('File Open Failed...File = ' & $ExcelFile & @LF)
        EndSwitch
    EndIf
    Local $aMySheets = _ExcelSheetList($oExcel)
    local $gui010   =   guicreate('Excel Sheet Selector')
    local $lst010   =   guictrlcreatelist('',20,20,100,50)
    For $1 = 1 To UBound($aMySheets) - 1
        guictrlsetdata($lst010,$aMySheets[$1])
    Next
    _ExcelBookClose($oExcel, 1)
    guisetstate()
    local $savesheet
    while 1
        switch guigetmsg()
            case $gui_event_close
                Exit
            case $lst010
                $savesheet = guictrlread($lst010)
                guidelete($gui010)
                return $savesheet
        endswitch
    wend
endfunc

Share this post


Link to post
Share on other sites
kylomas

If you are selecting an existing file then you should take out all the junk where I create a test file...

edit:

Your code would look something like this (not tested)...

; *** Start added by AutoIt3Wrapper ***
#include <GUIConstantsEx.au3>
; *** End added by AutoIt3Wrapper ***
#include <Constants.au3>
#include <Excel.au3>
#include <Array.au3>
#AutoIt3Wrapper_Add_Constants=n


Local $message = "Hold down Ctrl or Shift to choose multiple files."
Local  $ExcelFile = FileOpenDialog($message, @WindowsDir & "\", "Images (*.xls)", 1 + 4)
If @error Then
    MsgBox(4096, "", "No File(s) chosen",5)
Else
    $ExcelFile = StringReplace( $ExcelFile, "|", @CRLF)
    MsgBox(4096, "", "You chose " &  $ExcelFile,1)
EndIf

_excelbookopen($ExcelFile,0)

;---------------------------------------------------------------------------------------
; run function _select_sheet and display the sheet name selected
;---------------------------------------------------------------------------------------
msgbox($mb_ok,'My Excel Sheet', _select_sheet($excelfile))
;---------------------------------------------------------------------------------------
; select sheet function
;---------------------------------------------------------------------------------------
func _select_sheet($excel_file)
    $oExcel = _ExcelBookOpen($ExcelFile, 0)
    If $oExcel = 0 Then
        Switch @error
            Case 1
                ConsoleWrite('Object create failed' & @LF)
            Case 2
                ConsoleWrite('File Open Failed...File = ' & $ExcelFile & @LF)
        EndSwitch
    EndIf
    Local $aMySheets = _ExcelSheetList($oExcel)
    local $gui010   =   guicreate('Excel Sheet Selector')
    local $lst010   =   guictrlcreatelist('',20,20,100,50)
    For $1 = 1 To UBound($aMySheets) - 1
        guictrlsetdata($lst010,$aMySheets[$1])
    Next
    _ExcelBookClose($oExcel, 1)
    guisetstate()
    local $savesheet
    while 1
        switch guigetmsg()
            case $gui_event_close
                Exit
            case $lst010
                $savesheet = guictrlread($lst010)
                guidelete($gui010)
                return $savesheet
        endswitch
    wend
endfunc

The example that I posted was just to get you started, the code does not really DO anything. You will have to work that out.

Edited by 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

Share this post


Link to post
Share on other sites
joeloyzaga

$oExcel = _ExcelBookOpen($ExcelFile, 0) - but I've already opened it so I removed it and the code below creates an object - why would it be 0?

$oExcel = _ExcelBookOpen($ExcelFile, 0)
    If $oExcel = 0 Then
        Switch @error
            Case 1
                ConsoleWrite('Object create failed' & @LF)
            Case 2
                ConsoleWrite('File Open Failed...File = ' & $ExcelFile & @LF)
        EndSwitch
    EndIf

Share this post


Link to post
Share on other sites
kylomas

joeloyzaga,

In case the open fails. Your question was how to present a list of sheets in a workbook and I showed you one possible way to do this. You can code your app however you wish.

If you have further questions, post runnable code.

Good Luck,

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

Share this post


Link to post
Share on other sites
joeloyzaga

ok

Share this post


Link to post
Share on other sites
joeloyzaga

this is the working code for an existing spreadsheet

#Region ;**** Directives created by AutoIt3Wrapper_GUI ****
#AutoIt3Wrapper_Outfile_type=a3x
#AutoIt3Wrapper_Outfile=C:\laptop apps\BlueDuck SDA 2.0\examples\readexcelnyworksheetname.a3x
#EndRegion ;**** Directives created by AutoIt3Wrapper_GUI ****
#include <./lib/sda.core.au3>
#include <Excel.au3>
#include <Array.au3>
#include <Constants.au3>
#include <GUIConstantsEx.au3>
#AutoIt3Wrapper_Add_Constants=n
Global $aSheetData
Global $sBrowser
Global $iIndex
$sBrowser = 'iehta'
Global $oExcel
Global $aSheets
Global $sSheet2Process
Global $oTest
Global $oRepo,$Urltoload,$len,$commandfound,$Commandtoprocess,$newcommand
Local $message = "Hold down Ctrl or Shift to choose multiple files."
Local $var = FileOpenDialog($message, @WindowsDir & "\", "Images (*.xls)", 1 + 4)
If @error Then
    MsgBox(4096, "", "No File(s) chosen",5)
Else
    $var = StringReplace($var, "|", @CRLF)
    MsgBox(4096, "", "You chose " & $var,1)
EndIf
;$oExcel = _ExcelBookOpen("C:\JLauto\Resources\DOT.xls")
$oExcel = _ExcelBookOpen($var,0)
;Local $oExcel = _ExcelBookAttach($var)
;$oExcel = _ExcelBookOpen("C:\JLauto\Resources\DOT.xls",0)
If @error Then Exit MsgBox(16, "Error", "_ExcelBookOpen returned error " & @error)
$aSheets = _ExcelSheetList($oExcel)
If @error Then Exit MsgBox(16, "Error", "_ExcelSheetList returned error " & @error)
;---------------------------------------------------------------------------------------
; run function _select_sheet and display the sheet name selected
;---------------------------------------------------------------------------------------
;msgbox($mb_ok,'My Excel Sheet', _select_sheet($ExcelFile))
While 1
      $sSheet2Process =  _select_sheet($var)
   ;$sSheet2Process = InputBox("Prompt", "Please enter the name of the sheet to process", "")
    If @error = 1 Then ExitLoop ; Cancel was pressed
    _ExcelSheetActivate($oExcel, $sSheet2Process) ; Activate the sheet
    If @error Then
        MsgBox(16, "Error", "_ExcelSheetActivate returned error " & @error)
    Else
        $aSheetData = _ExcelReadSheetToArray($oExcel) ; Read the whole sheet into an array
        If @error Then
            MsgBox(16, "Error", "_ExcelSheetActivate returned error " & @error)
        Else
      $oTest = NewTest(@ScriptName)
      ;Start Test
      $oTest.Setup()
      ;Test Report create
      $oTest.Report.Create
            _ProcessSheet($aSheetData)
   $oTest.Report.Close
   $oTest.Browser.stop
        EndIf
    EndIf
WEnd
_ExcelBookClose($oExcel)
Func _ProcessSheet(ByRef $aSheetData)
    ; Process the data in the active sheet
    For $iRow = 3 To $aSheetData[0][0]
        For $iCol = 3 To $aSheetData[0][1] - 1
            If $aSheetData[$iRow][$iCol] = '0' then
    $iCol = $aSheetData[0][1]
   else
    if (StringInStr($aSheetData[$iRow][$iCol],"|") > 0) or (StringInStr($aSheetData[$iRow][$iCol],"(") > 0) then
     if (StringInStr($aSheetData[$iRow][$iCol],"|") > 0) then
      $len = StringLen($aSheetData[$iRow][$iCol])
      $commandfound = StringTrimRight($aSheetData[$iRow][$iCol],($len+1)-StringInStr($aSheetData[$iRow][$iCol],"|") )
      Select
       Case $commandfound = "LaunchApp"
        $Urltoload = StringTrimleft($aSheetData[$iRow][$iCol],StringInStr($aSheetData[$iRow][$iCol],"|") )
        $oTest.NewBrowser($sBrowser,$Urltoload)
        $oTest.Browser.start
        _open("/")
        _windowMaximize()
        _waitForPageToLoad("30")
       Case $commandfound = "isElementPresent"
        $Commandtoprocess = StringTrimleft($aSheetData[$iRow][$iCol],StringInStr($aSheetData[$iRow][$iCol],"|") )
        $newcommand = "//*[" & $Commandtoprocess & "]"
        if  _isElementPresent($newcommand ) == True Then
         MsgBox(0, "", "element is present" & $Commandtoprocess, 1 )
         ;_type("//*[" & $Commandtoprocess & "]","joe loyzaga")
         ;_windowFocus()
        endif
       Case $commandfound = "type"
        $Commandtoprocess = StringTrimleft($aSheetData[$iRow][$iCol],StringInStr($aSheetData[$iRow][$iCol],"|") )
        if  _isElementPresent("//*[@name='q']") == True Then
         _type("//*[" & $Commandtoprocess & "]","joe loyzaga")
         _windowFocus()
        endif
       Case $commandfound = "click"
        $Commandtoprocess = StringTrimleft($aSheetData[$iRow][$iCol],StringInStr($aSheetData[$iRow][$iCol],"|") )
        if  _isElementPresent("//*[@name='btnG']") == True Then
        _click("//*[" & $Commandtoprocess & "]")
        _waitForPageToLoad("3000")
        If _isTextPresent("www.linkedin.com/pub/dir/Joe/fred") = 1 Then
         $oTest.AddStepResult("Step 1","Search for www.linkedin.com/pub/dir/Joe/fred",1)
        Else
         $oTest.AddStepResult("Step 1","Search for www.linkedin.com/pub/dir/Joe/fred",0)
        EndIf
        If _isTextPresent("www.linkedin.com/pub/dir/Joe/Loyzaga") = 1 Then
         $oTest.AddStepResult("Step 1","Search for www.linkedin.com/pub/dir/Joe/Loyzaga",1)
        Else
         $oTest.AddStepResult("Step 1","Search for www.linkedin.com/pub/dir/Joe/Loyzaga",0)
        EndIf
        endif
       Case Else
        MsgBox(0, "", "No preceding case was true!")
      EndSelect
     endif
     if (StringInStr($aSheetData[$iRow][$iCol],"(") > 0) then
      Local $oTest,$oRepo,$Urltoload,$len
      $len = StringLen($aSheetData[$iRow][$iCol])
     endif
    endif
   EndIf
        Next
    Next
EndFunc   ;==>_ProcessSheet

Func setUp()
  ;$oTest.NewBrowser('firefox','http://www.google.com/')
  ;$oTest.Browser.start
        _createReport(@ScriptName) ;Set the result report
        ;_construct($sBrowser ,"http://www.google.com/")
        _start()
EndFunc
Func tearDown()
        ;_reportClose()
        _stop()
EndFunc
;---------------------------------------------------------------------------------------
; select sheet function
;---------------------------------------------------------------------------------------
func _select_sheet($excel_file)
    Local $oExcel = _ExcelBookOpen($excel_file, 0)
;Local $oExcel = _ExcelBookAttach($excel_file)
    If $oExcel = 0 Then
        Switch @error
            Case 1
                ConsoleWrite('Object create failed' & @LF)
            Case 2
                ConsoleWrite('File Open Failed...File = ' & $oExcel & @LF)
        EndSwitch
    EndIf
    Local $aMySheets = _ExcelSheetList($oExcel)
    local $gui010   =   guicreate('Excel Sheet Selector')
    local $lst010   =   guictrlcreatelist('',20,20,100,50)
    For $1 = 1 To UBound($aMySheets) - 1
        guictrlsetdata($lst010,$aMySheets[$1])
    Next
    _ExcelBookClose($oExcel, 1)
    guisetstate()
    local $savesheet
    while 1
        switch guigetmsg()
            case $gui_event_close
                Exit
            case $lst010
                $savesheet = guictrlread($lst010)
                guidelete($gui010)
                return $savesheet
        endswitch
    wend
endfunc

Share this post


Link to post
Share on other sites
water

Please use [autoit][/autoit] tags to enclose your code. So you get syntax highlighting and a scrollable box which makes the code much more readable!


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.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
Sign in to follow this  

×