rony2006

How do I select a Worksheet in Excel

41 posts in this topic

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:

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?

 

 

Share this post


Link to post
Share on other sites



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.

Share this post


Link to post
Share on other sites

@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.

Share this post


Link to post
Share on other sites

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

Share this post


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

Share this post


Link to post
Share on other sites

@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

Share this post


Link to post
Share on other sites

#7 ·  Posted (edited)

I tried this:

#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 by rony2006

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

@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

 

 

 

 

Share this post


Link to post
Share on other sites

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.

 

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

@alien4u Ok, thank you very much.

I wait your suggestions.

Share this post


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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-02-03 - Version 1.4.7.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-03 - Version 1.2.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
Tutorials:
ADO - Wiki

 

Share this post


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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-02-03 - Version 1.4.7.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-03 - Version 1.2.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
Tutorials:
ADO - Wiki

 

Share this post


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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-02-03 - Version 1.4.7.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-03 - Version 1.2.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
Tutorials:
ADO - Wiki

 

Share this post


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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-02-03 - Version 1.4.7.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-03 - Version 1.2.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

@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?

Share this post


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

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