How do I select a Worksheet in Excel

[rony2006]

Hello, 

I heard that the functions for excel are updated in Autoit and totaly changed.

I want to build a script that allows me to autoselect a sheet in a excel. My excel name is "rec - Microsoft Excel"

Until now I have:

expandcollapse popup

WinActivate("rec - Microsoft Excel") ; Activate the Excel window

WinWaitActive("rec - Microsoft Excel") ; Wait until the Excel window is active

WinSetState("rec - Microsoft Excel", "", @SW_MAXIMIZE)




;revenire la sheet 1
Send("^{PGUP}")
Send("^{PGUP}")
Send("^{PGUP}")
Send("^{PGUP}")
Send("^{PGUP}")
Send("^{PGUP}")
Send("^{PGUP}")


Sleep (500)
;SB
If $OCR1 >= 18 And $OCR1 <= 20 Then

Send("^{PGDN}")

Endif


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

Send("^{PGDN}")
Send("^{PGDN}")

Endif

If $OCR1 >= 12 And $OCR1 <= 14 Then

Send("^{PGDN}")
Send("^{PGDN}")
Send("^{PGDN}")

Endif

If $OCR1 >= 10 And $OCR1 <= 12 Then

Send("^{PGDN}")
Send("^{PGDN}")
Send("^{PGDN}")
Send("^{PGDN}")

Endif

If $OCR1 >= 9 And $OCR1 <= 10 Then

Send("^{PGDN}")
Send("^{PGDN}")
Send("^{PGDN}")
Send("^{PGDN}")
Send("^{PGDN}")

Endif

If $OCR1 >= 9 And $OCR1 <= 10 Then

Send("^{PGDN}")
Send("^{PGDN}")
Send("^{PGDN}")
Send("^{PGDN}")
Send("^{PGDN}")

Endif

If $OCR1 >= 6 And $OCR1 <= 8 Then

Send("^{PGDN}")
Send("^{PGDN}")
Send("^{PGDN}")
Send("^{PGDN}")
Send("^{PGDN}")
Send("^{PGDN}")

Endif

If $OCR1 >= 5 And $OCR1 <= 6 Then

Send("^{PGDN}")
Send("^{PGDN}")
Send("^{PGDN}")
Send("^{PGDN}")
Send("^{PGDN}")
Send("^{PGDN}")
Send("^{PGDN}")

Endif

But is not working to well with sending "ctrl" and page up or down for selecting the sheet. 

How I can do this using the autoit functions?

 

 

[alien4u]

You need to try Excel UDF is the best way to do what you want and not in the way you are trying.
Sample Code that read entire Worksheet to an array:

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

Local $oExcel = _Excel_Open()
If @error Then
    MsgBox(0, "Error", "Error creating Excel object")
    _Excel_Close($oExcel)
    Exit
EndIf
 Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\Excel1.xlsx", Default, Default)
 If @error Then
     MsgBox(0, "Error", "Error opening the workbook")
     _Excel_Close($oExcel)
     Exit
 EndIf

 Local $aResult = _Excel_RangeRead($oWorkbook, Default, Default, 1)
 If @error Then
     MsgBox(0, "Error", "Unable to read workbook")
     _Excel_BookClose($oWorkbook)
     Exit
 EndIf

From the Code you should read from the Help File this:
_Excel_Open()
_Excel_BookOpen()
_Excel_RangeRead()

Regards
Alien.

[rony2006]

@alien4u

 

Thanks.

 

I saw that the working code is something like:

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

Local $oExcel = _Excel_Open()
If @error Then
    MsgBox(0, "Error", "Error creating Excel object")
    _Excel_Close($oExcel)
    Exit
EndIf
 Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\stg\rec.xlsx", Default, Default)
 If @error Then
     MsgBox(0, "Error", "Error opening the workbook")
     _Excel_Close($oExcel)
     Exit
 EndIf

 ;Local $aResult = _Excel_RangeRead($oWorkbook, Default, Default, 1)
 If @error Then
     MsgBox(0, "Error", "Unable to read workbook")
     _Excel_BookClose($oWorkbook)
     Exit
 EndIf

 
 $oWorkbook.Sheets(5).Activate ; 5 can be the number or name of the sheet to be activated

I dont need _ Excel_RangeRead() because it returns a 2D array with the content of the cells and I dont need this.

[rony2006]

But now i dont know what to do.

Lets say that my excel is minimized, I dont need to open it again.

How can I activate the window and after that to select the sheet using:

$oWorkbook.Sheets(5).Activate ; 5 can be the number or name of the sheet to be activated

[alien4u]

1 hour ago, rony2006 said:

I dont need _ Excel_RangeRead() because it returns a 2D array with the content of the cells and I dont need this.

This code works for you? What do you need exactly? or what do you want to do?

Regards
Alien.

[rony2006]

@alien4u The code is working ok. Thanks.

But I want to open manually my excel and when I run the autoit script only to maximize it and put focuse on it and then to be able to change the sheets.

So, I think i just need to use:

$oWorkbook = i dont know what to write here to focus and maximize my excel file

$oWorkbook.Sheets(5).Activate ; 5 can be the number or name of the sheet to be activated

[rony2006]

I tried this:

expandcollapse popup

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









Local $oExcel = _Excel_Open()
If @error Then
    MsgBox(0, "Error", "Error creating Excel object")
    _Excel_Close($oExcel)
    Exit
EndIf
 Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\stg\rec.xlsx", Default, Default)
 If @error Then
     MsgBox(0, "Error", "Error opening the workbook")
     _Excel_Close($oExcel)
     Exit
 EndIf

 ;Local $aResult = _Excel_RangeRead($oWorkbook, Default, Default, 1)
 If @error Then
     MsgBox(0, "Error", "Unable to read workbook")
     _Excel_BookClose($oWorkbook)
     Exit
 EndIf

 
 
 While 1
 
 
 $oWorkbook.Sheets("12-10 SB").Activate ; x can be the number or name of the sheet to be activated
 Sleep (1000)
  $oWorkbook.Sheets("25-21 SB").Activate ; x can be the number or name of the sheet to be activated
   Sleep (1000)
 Wend

To open the excel in the beginning of my script, do some stuff and then in a while loop to select sheets.

Is working but I get the following error:

 

 

Edited April 23, 2016 by rony2006

[alien4u]

You don't maximize or minimize an Excel file, you Maximize or Minimize a Window.
 

#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 = "Excel1.xlsx"
 Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\" & $excelfilename, Default, Default)
 If @error Then
     MsgBox(0, "Error", "Error opening the workbook")
     _Excel_Close($oExcel)
     Exit
 EndIf
 
 ;Your code or Tasks here:

$excelhwd = WinWait("Microsoft Excel - " & $excelfilename)

WinSetState($excelhwd, "", @SW_MAXIMIZE)
WinSetState($excelhwd, "", @SW_MINIMIZE)

Regards
Alien

[alien4u]

I still can't understand what are you trying to do, please post the whole thing, what you maybe think I obviously must know I don't.
Example:
- Want to Open Excel File
- Want to Make THIS thing.
- Want to move to next Sheet.
- Want to make another THING.
- Finally I will do this THING.

Regards
Alien.

[rony2006]

@alien4u First: this line should be:

$excelhwd = WinWait($excelfilename & " - Microsoft Excel")

in Office 2013, the title of window is name - Microsoft Excel

Want I want to do:

1. User Input 2 variable: $OCR1 and $value ($OCR1 is a number between 1 and 25)

2. Clicks a button

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

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

 

 

 

 

[alien4u]

I don't have time now but I will back to you as soon as I reach home, your approach is not the best one.

Example:
User Input 2 variable: $OCR1 and $value ($OCR1 is a number between 1 and 25)
-OK
2. Clicks a button
-OK
3. Maximize (set focus, show .etc) rec.xlsx (let's say that I open manually this file)
-You can do this with _Excel_UDF is better.
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)
- No need to select a Sheet you can search the specific sheet for specific data with _Excel_UDF again is better that way.
5. send CTRL + F
-No need to search this way.
6. Paste the value of $value
-No need to do this.
7. send ENTER
-No need to do this
8. Copy the founded cell (with color)
- No need to do this you can do it with _Excel_UDF and again is better...
9. Paste the cell all the time in A1
- No need to do this is better with _Excel_UDF
10. Using pixel function: get the color of A1
- No need to do this.

Plus: All this can be done with Excel HIDE, no need to show excel for anything.
1- You could ask the user for enter the Input Values.
2- Do your work.
3- Show a Result to the user.
DONE.

Regards
Alien.

 

[rony2006]

@alien4u

Were I can find all the information about this excel udf?

Here seems that is not complete: https://www.autoitscript.com/wiki/Excel_UDF

[alien4u]

Hi @rony2006
Sadly Excel UDF documentation is not Complete as you said.
And _Excel_RangeFind() is not working properly, I'm unable to make it work with the examples with the help file.

What I do yesterday was to read the entire WorkSheet to an Array and search that array for the user input so you can copy that value and paste it on your desire Cell.

I have to do few more test and I will let you know what I end with.

Regards
Alien.

[rony2006]

@alien4u Ok, thank you very much.

I wait your suggestions.

[water]

On 22.4.2016 at 8:40 PM, rony2006 said:

I heard that the functions for excel are updated in Autoit and totaly changed.

Did you read the section "Script Breaking Change" in the History / ChangeLog?
Details about the Excel UDF changes can be found there: https://www.autoitscript.com/autoit3/docs/script_breaking_changes_excel_udf.htm

[water]

1 hour ago, alien4u said:

Sadly Excel UDF documentation is not Complete as you said.

The Excel documentation consists of two parts:

• The documentation you find in the help file. This is complete.
• The extended documentation in the wiki. This is still work in progress.

What is missing in your opinion?

[water]

1 hour ago, alien4u said:

And _Excel_RangeFind() is not working properly, I'm unable to make it work with the examples with the help file.

What does this mean? The examples work because I tested them (with Office 2010 on Windows 7). Could you please give more information about your Office information?

[water]

23 hours ago, rony2006 said:

@alien4u First: this line should be:

$excelhwd = WinWait($excelfilename & " - Microsoft Excel")

in Office 2013, the title of window is name - Microsoft Excel

Want I want to do:

1. User Input 2 variable: $OCR1 and $value ($OCR1 is a number between 1 and 25)
2. Clicks a button
3. Maximize (set focus, show .etc) rec.xlsx (let's say that I open manually this file)
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

I suggest not to mix using functions of the Excel UDF and automating the GUI. What you want can be done using the UDFs function.

[MichaelHB]

@rony2006 Can you explain a little more about this:

 

• 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)

        The sheet name is a number right? Like "10" for example, or is something like  "25-21" ?

 

 

 

• 9. Paste the cell all the time in A1

        Paste in A1 of what sheet ? In $OCR1 sheet? Are you doing this just to get the cell color ?

        Is It possible to have more than one $value in the same $OCR1 sheet?

[alien4u]

1 hour ago, water said:

What does this mean? The examples work because I tested them (with Office 2010 on Windows 7). Could you please give more information about your Office information?

Hi @water thanks you for your work and thanks for asking.

Here Win7 Ultimate x64 and Office 2007.

Every example from Help file for _Excel_RangeFind() I got this:
"C:\Program Files (x86)\AutoIt3\Include\Excel.au3" (656) : ==> The requested action with this object has failed.:
$aResult[$iIndex][1] = $oMatch.Name.Name
$aResult[$iIndex][1] = $oMatch^ ERROR

Thanks in advance if you can take a look in to this.

Regards
Alien.