How do I select a Worksheet in Excel

[alien4u]

@rony2006
In the mean time you could try this to Copy one Cell Value from one Worksheet to another and also the cell format, meaning background color.

 

; Copy From Worksheet2 the Cell B2(this include value and format)
_Excel_RangeCopyPaste($oWorkbook.Worksheets(2), "B2")

; Paste the value on Worksheet1 Cell A1 
_Excel_RangeCopyPaste($oWorkbook.Worksheets(1), Default, "A1", Default, $xlPasteValues)

; Paste the format(background color an so) on Worksheet1 Cell A1
_Excel_RangeCopyPaste($oWorkbook.Worksheets(1), Default, "A1", Default, $xlPasteFormats) ; paste the format to the target range

And here I have another question maybe @water could assist here:
For Example:
_Excel_RangeCopyPaste($oWorkbook.Worksheets(2), "B2")

How I can pass a variable value to $oWorkbook.Worksheets($value)
Let say User Input Worksheet value and then you pass that to the object $oWorkbook.Worksheets()
I don't know how to do this.

Regards
Alien.

[MichaelHB]

@alien4u You can do like this:

$sAlienSheet = "Alien"
_Excel_RangeCopyPaste($oWorkbook.Worksheets($sAlienSheet), "A1", "C1", Default, -4104) ;-4104 will copy everything

And try this Excel_RangeFind modification:

expandcollapse popup

Func __Excel_RangeFind($oWorkbook, $sSearch, $vRange = Default, $iLookIn = Default, $iLookAt = Default, $bMatchcase = Default)
    If Not IsObj($oWorkbook) Or ObjName($oWorkbook, 1) <> "_Workbook" Then Return SetError(1, 0, 0)
    If StringStripWS($sSearch, 3) = "" Then Return SetError(2, 0, 0)
    If $iLookIn = Default Then $iLookIn = $xlValues
    If $iLookAt = Default Then $iLookAt = $xlPart
    If $bMatchcase = Default Then $bMatchcase = False
    Local $oMatch, $sFirst = "", $bSearchWorkbook = False, $oSheet
    If $vRange = Default Then
        $bSearchWorkbook = True
        $oSheet = $oWorkbook.Sheets(1)
        $vRange = $oSheet.UsedRange
    ElseIf IsString($vRange) Then
        $vRange = $oWorkbook.Activesheet.Range($vRange)
        If @error Then Return SetError(3, @error, 0)
    EndIf
    Local $aResult[100][4], $iIndex = 0, $iIndexSheets = 1, $iNumberOfSheets = $oWorkbook.Worksheets.Count
    While 1
        $oMatch = $vRange.Find($sSearch, Default, $iLookIn, $iLookAt, Default, Default, $bMatchcase)
        If @error Then Return SetError(4, @error, 0)
        If IsObj($oMatch) Then
            $sFirst = $oMatch.Address
            While 1
                $aResult[$iIndex][0] = $oMatch.Worksheet.Name
                $aResult[$iIndex][1] = $oMatch.Address
                $aResult[$iIndex][2] = $oMatch.Value
                $aResult[$iIndex][3] = $oMatch.Formula
                $iIndex = $iIndex + 1
                If Mod($iIndex, 100) = 0 Then ReDim $aResult[UBound($aResult, 1) + 100][4]
                $oMatch = $vRange.Findnext($oMatch)
                If Not IsObj($oMatch) Or $sFirst = $oMatch.Address Then ExitLoop
            WEnd
        EndIf
        If Not $bSearchWorkbook Then ExitLoop
        $iIndexSheets = $iIndexSheets + 1
        If $iIndexSheets > $iNumberOfSheets Then ExitLoop
        $sFirst = ""
        $oSheet = $oWorkbook.Sheets($iIndexSheets)
        If @error Then ExitLoop
        $vRange = $oSheet.UsedRange
    WEnd
    ReDim $aResult[$iIndex][6]
    Return $aResult
EndFunc

 

[water]

That's not a problem with the Excel UDF but with the version of AutoIt you run. Version 3.3.12.0 works fine, version 3.3.14.2 causes the problem.

[BrewManNH]

I'm getting the same error message as @alien4u using 3.3.14.0 even adding the missing error handler line in the RangeFind function doesn't fix it, it just errors out on a different line instead. Windows 7 x64, Office 2007 also on this machine.

[alien4u]

7 hours ago, MichaelHB said:

@alien4u You can do like this:

$sAlienSheet = "Alien"
_Excel_RangeCopyPaste($oWorkbook.Worksheets($sAlienSheet), "A1", "C1", Default, -4104) ;-4104 will copy everything

 

EDIT NOTE: This still not working as you suggest.
First, what Worksheets() need is Sheet number, not sheet name if you try what you suggest you will realize it does not work like that. I already tried that before I post.

But its copy as you suggest even if still not reliable for copying from one Sheet to another Sheet.

Regards
Alien.

Edited April 24, 2016 by alien4u
Fixing previous answer based on mistake from my side.

[water]

The _Excel_RangeFind problem is caused by a bug in the COM error handler of AutoIt 3.3.14.0. Details can be found here: https://www.autoitscript.com/trac/autoit/ticket/3167

[water]

6 hours ago, alien4u said:

First, what Worksheets() need is Sheet number, not sheet name

Not true. You can use use both according to MSDN: https://msdn.microsoft.com/en-us/library/ff838615%28v=office.14%29.aspx
"The name or index number of the object."

 

Edited April 24, 2016 by water

[water]

6 hours ago, alien4u said:

EDIT NOTE: This still not working as you suggest.

Because - according to the help file: "If $vSourceRange and $vTargetRange are specified parameters $iPaste, $iOperation, $bSkipBlanks and $bTranspose are ignored"

[water]

14 hours ago, alien4u said:

And here I have another question maybe @water could assist here:
For Example:
_Excel_RangeCopyPaste($oWorkbook.Worksheets(2), "B2")

How I can pass a variable value to $oWorkbook.Worksheets($value)
Let say User Input Worksheet value and then you pass that to the object $oWorkbook.Worksheets()
I don't know how to do this.

You need to make sure that you pass a number. All Input from a GUI/InputBox is being returned as a string. If you enter "2" then Excel looks for a sheet named "2" not the second sheet as expected.
Try:

Global $sInput = InputBox("Test", "Enter the number of the worksheet:")
MsgBox(0, "", "Return value from InputBox: " & VarGetType($sInput))
MsgBox(0, "", "Return value from InputBox translated to number: " & VarGetType(Number($sInput)))

 

[rony2006]

@MichaelHB

The sheet name is something like  "25-21" but I can change it to a number like "2521" no problem

I want to paste the value in A1 in the sheet were I search for the $OCR1 value. And yes, I want only to get the color of it.

[water]

If you want to access a sheet by name then a string is fine. But if you want to access it by index you need to convert the user input to a string.
Means: No need to change the script in your case.

Edited April 24, 2016 by water

[MichaelHB]

@alien4u I believe that @water has explained, try this and check if it works for you:

Local $oWorkbook = ; grab the Workbook here.

$sAlienSheet1 = "Alien Sheet"
$sAlienSheet2 = "Alien New Sheet"
$oWorkbook.Worksheets($sAlienSheet1).Range("$A$1").Copy ; put the value that you want to copy in A1 of the $sAlienSheet1
$oWorkbook.Worksheets($sAlienSheet2).Range("A1").PasteSpecial(-4104)
Exit

@rony2006 You did not answer all my questions.  But i belive that something like this could work for you. Remeber that you will need to convert the color number to whatever parameter that you are using later (like yellow = 65535 for example).

expandcollapse popup

#include <Excel.au3>
#include <FileConstants.au3>
#include <GUIConstantsEx.au3>

;   1. User Input 2 variable: $sSheetName and $sValueToFind ($sSheetName is a number between 1 and 25)
;   2. Clicks a button

GUICreate("My GUI", 320, 120, @DesktopWidth / 2 - 160, @DesktopHeight / 2 - 45)
Local $sSheetName = GUICtrlCreateInput("Sheet name here", 10, 15, 300, 20)
Local $sValueToFind = GUICtrlCreateInput("Value/Text to find", 10, 45, 300, 20)
Local $idBtn = GUICtrlCreateButton("OK", 135, 85, 60, 20)

GUISetState(@SW_SHOW)

While 1
    Switch GUIGetMsg()
        Case $GUI_EVENT_CLOSE
            GUIDelete()
            Exit
        Case $idBtn
            $sSheetName = GUICtrlRead($sSheetName)
            $sValueToFind = GUICtrlRead($sValueToFind)
            MsgBox(0, "", "The sheet name is: " & $sSheetName & @CRLF & "The value/text to find is: "& $sValueToFind)
            GUIDelete()
            ExitLoop
    EndSwitch
WEnd

;   3. Maximize (set focus, show .etc) rec.xlsx (let's say that I open manually this file)

Local $ExcelFileName = "rec.xlsx"
Local $oWorkbook = _Excel_BookAttach($ExcelFileName, "FileName")
If @error Then
    Local $oExcel = _Excel_Open()
    If @error Then Exit MsgBox(0, "Excel UDF: _Excel_Open", "Error creating a new Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    Local $sFileOpenDialog = FileOpenDialog("Please select the Excel file", "", "Excel (*.xlsx;*.xlsm;*.xlsb;*.xltx;*.xltm;*.xls;*.xlt;*.xml)", BitOR($FD_FILEMUSTEXIST, $FD_PATHMUSTEXIST))
    If @error Then
        MsgBox(0, "", "No file were selected.")
    Else
        MsgBox(0, "", "You chose the following file:" & @CRLF & $sFileOpenDialog)
    EndIf
    $oWorkbook = _Excel_BookOpen($oExcel, $sFileOpenDialog)
    If @error Then Exit MsgBox(0, "Excel UDF: _Excel_BookOpen", "Error opening '" & $sFileOpenDialog & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    $oWorkbook.Application.WindowState = -4137;xlMaximized
    WinActivate(HWnd($oExcel.HWnd))
EndIf

;   4. Select a sheet acording to $OCR1 (sheets are 25-21/20-18/15-17/12-14/12-10/10-9/8-6and 6-5)
;   5. send CTRL + F
;   6. Paste the value of $value
;   7. send ENTER
;   8. Copy the founded cell (with color)
;   9. Paste the cell all the time in A1
;   10. Using pixel function: get the color of A1

Sleep(1500)

Local $aResult = __Excel_RangeFind($oWorkbook, $sValueToFind)
If Not @error And IsArray($aResult) Then
    For $i = 0 To UBound($aResult)-1
        If $aResult[$i][0] = $sSheetName Then
            Local $iInteriorColor = $oWorkbook.Worksheets($sSheetName).Range($aResult[$i][1]).Interior.Color
            MsgBox(0, "Color Number", "This is the interior color number of the requested cell: " & $iInteriorColor)
            Exit
        EndIf
    Next
    MsgBox(0, "Error", "The value/text (" & $sValueToFind & ") is not in the requested sheet (" & $sSheetName & ").")
EndIf
MsgBox(0, "Error", "I could not find the requested value/text (" & $sValueToFind & ") in this workbook.")
Exit

Func __Excel_RangeFind($oWorkbook, $sSearch, $vRange = Default, $iLookIn = Default, $iLookAt = Default, $bMatchcase = Default)
    If Not IsObj($oWorkbook) Or ObjName($oWorkbook, 1) <> "_Workbook" Then Return SetError(1, 0, 0)
    If StringStripWS($sSearch, 3) = "" Then Return SetError(2, 0, 0)
    If $iLookIn = Default Then $iLookIn = $xlValues
    If $iLookAt = Default Then $iLookAt = $xlPart
    If $bMatchcase = Default Then $bMatchcase = False
    Local $oMatch, $sFirst = "", $bSearchWorkbook = False, $oSheet
    If $vRange = Default Then
        $bSearchWorkbook = True
        $oSheet = $oWorkbook.Sheets(1)
        $vRange = $oSheet.UsedRange
    ElseIf IsString($vRange) Then
        $vRange = $oWorkbook.Activesheet.Range($vRange)
        If @error Then Return SetError(3, @error, 0)
    EndIf
    Local $aResult[100][4], $iIndex = 0, $iIndexSheets = 1, $iNumberOfSheets = $oWorkbook.Worksheets.Count
    While 1
        $oMatch = $vRange.Find($sSearch, Default, $iLookIn, $iLookAt, Default, Default, $bMatchcase)
        If @error Then Return SetError(4, @error, 0)
        If IsObj($oMatch) Then
            $sFirst = $oMatch.Address
            While 1
                $aResult[$iIndex][0] = $oMatch.Worksheet.Name
                $aResult[$iIndex][1] = $oMatch.Address
                $aResult[$iIndex][2] = $oMatch.Value
                $aResult[$iIndex][3] = $oMatch.Formula
                $iIndex = $iIndex + 1
                If Mod($iIndex, 100) = 0 Then ReDim $aResult[UBound($aResult, 1) + 100][4]
                $oMatch = $vRange.Findnext($oMatch)
                If Not IsObj($oMatch) Or $sFirst = $oMatch.Address Then ExitLoop
            WEnd
        EndIf
        If Not $bSearchWorkbook Then ExitLoop
        $iIndexSheets = $iIndexSheets + 1
        If $iIndexSheets > $iNumberOfSheets Then ExitLoop
        $sFirst = ""
        $oSheet = $oWorkbook.Sheets($iIndexSheets)
        If @error Then ExitLoop
        $vRange = $oSheet.UsedRange
    WEnd
    ReDim $aResult[$iIndex][4]
    Return $aResult
EndFunc

 

[rony2006]

@MichaelHB

I made a simple test now and is looks like working ok. I will try to implement the code. I hope I will not have problems.

Thank you very much for the help guy.

 

 

Any way, I think there is some problem with excel udf because I get this error:

Every time I run the following code:

 

expandcollapse popup

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



Local $oExcel = _Excel_Open()
If @error Then
    MsgBox(0, "Error", "Error creating Excel object")
    _Excel_Close($oExcel)
    Exit
EndIf

$excelfilename = "rec.xlsx"
 Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\stg\" & $excelfilename, Default, Default)
 If @error Then
     MsgBox(0, "Error", "Error opening the workbook")
     _Excel_Close($oExcel)
     Exit
 EndIf
 
 
 
 Global $OCR1 = ControlGetText ( "main", "", "[CLASS:Edit; INSTANCE:3 ]" )
 
 

 
 
 
 
 
 
 

;MsgBox (0, " ", $OCR1)
 


While 1

 Global $OCR1 = ControlGetText ( "main", "", "[CLASS:Edit; INSTANCE:3 ]" )



If $OCR1 = 21  Then

  $oWorkbook.Sheets(1).Activate

Endif


If $OCR1 >= 18 And $OCR1 <= 20 Then

  $oWorkbook.Sheets(2).Activate

Endif


If $OCR1 >= 15 And $OCR1 <= 17 Then

  $oWorkbook.Sheets(3).Activate
  
Endif

If $OCR1 = 14 Then

  $oWorkbook.Sheets(4).Activate

Endif

If $OCR1 = 13 Then

  $oWorkbook.Sheets(4).Activate

Endif

If $OCR1 = 12 Then

  $oWorkbook.Sheets(4).Activate

Endif

If $OCR1 = 11 Then

  $oWorkbook.Sheets(5).Activate

Endif

If $OCR1 = 10 Then

  $oWorkbook.Sheets(5).Activate

Endif

If $OCR1 = 9 Then

  $oWorkbook.Sheets(6).Activate

Endif

If $OCR1 = 8 Then

  $oWorkbook.Sheets(7).Activate

Endif

If $OCR1 = 7 Then

  $oWorkbook.Sheets(7).Activate

Endif

If $OCR1 = 6 Then

  $oWorkbook.Sheets(7).Activate

Endif

If $OCR1 = 5 Then

  $oWorkbook.Sheets(8).Activate

Endif




Wend

If $GUI1 is 12, i got selected the sheet 14-12 SB but also msg box with error.

 

But anyway, thanks to MichaelHB now I dont think i need this anymore

[water]

You have line

Global $OCR1 = ControlGetText ( "main", "", "[CLASS:Edit; INSTANCE:3 ]" )

before and in the While loop.
ControlGetText always returns a text. But you use the variable as if it contains a number. What is the value of $OCR1?

As you work with the Excel COM you need to add a COM error handler when running AutoIt > 3.3.12.0

[rony2006]

Ok, thank you.

The value of $OCR1 is a number between 25 and 1.

 

Now I try to implement the code from  MichaelHB

[MichaelHB]

@rony2006 You are welcome.  Probably you will not need the sheet activation anymore, but i suggest you to follow/consider Water suggestion [As you work with the Excel COM you need to add a COM error handler when running AutoIt > 3.3.12.0].

[alien4u]

9 hours ago, water said:

You need to make sure that you pass a number. All Input from a GUI/InputBox is being returned as a string. If you enter "2" then Excel looks for a sheet named "2" not the second sheet as expected.
Try:

Global $sInput = InputBox("Test", "Enter the number of the worksheet:")
MsgBox(0, "", "Return value from InputBox: " & VarGetType($sInput))
MsgBox(0, "", "Return value from InputBox translated to number: " & VarGetType(Number($sInput)))

 

You are right I try this and is working like this:

$user_input_sheet = "2"
$user_input_sheet = Number($user_input_sheet)
_Excel_RangeCopyPaste($oWorkbook.Worksheets($user_input_sheet), "B2", "F1", Default, -4104)


; Also works like this:

$user_input_sheet = "Sheet2"
_Excel_RangeCopyPaste($oWorkbook.Worksheets($user_input_sheet), "B2", "F1", Default, -4104)

It also works like @MichaelHB using Sheet Names.

Thanks you.

Regards
Alien.

[alien4u]

12 hours ago, water said:

The _Excel_RangeFind problem is caused by a bug in the COM error handler of AutoIt 3.3.14.0. Details can be found here: https://www.autoitscript.com/trac/autoit/ticket/3167

@water or @MichaelHB
There is anyway to make RangeFind work on 3.3.14.2? for me is not a good option to downgrade, I don't know so much about adding a COM error handle to fix the problem, some if you could help will be nice to have RangeFind working on 3.3.14.2.

Regards
Alien.

[MichaelHB]

@alien4u There is. Just use the modified function of the RangeFind that i made in #32 for rony2006 (__Excel_RangeFind). Its almost the same, the difference is that i edit the 3 object actions that will give you error in 3.3.14.2, so you will not get the cell name and the comment, just the sheet name, cell address, text of the cell and the formula. So you dont have to change the UDF, just use this function when you need inside your script. 

[MichaelHB]

-

Edited April 24, 2016 by MichaelHB
Double post