Jump to content

worksheet select dialog

joeloyzaga
 Share

Recommended Posts

joeloyzaga Prodigy

joeloyzaga

Posted
Posted

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

Link to comment
Share on other sites
kylomas Universalist

kylomas

Posted
Posted

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

Link to comment
Share on other sites
joeloyzaga Prodigy

joeloyzaga

Posted
  • Author
Posted

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
Link to comment
Share on other sites
kylomas Universalist

kylomas

Posted
Posted (edited)

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

Link to comment
Share on other sites
joeloyzaga Prodigy

joeloyzaga

Posted
  • Author
Posted

$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
Link to comment
Share on other sites
kylomas Universalist

kylomas

Posted
Posted

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

Link to comment
Share on other sites
joeloyzaga Prodigy

joeloyzaga

Posted
  • Author
Posted

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
Link to comment
Share on other sites
water Universalist

water

Posted
Posted

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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
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
 Share
Go to topic listing

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...