Sign in to follow this  
Followers 0
gauravvogue

how to enable specific excel sheet from multiple open excel sheets

25 posts in this topic

hi ,

I am using this program to open an excel sheet then running a macro ( getting successful till here)  but now i want to activate one excel sheet name "Market Data" and copy paste some data here but not hapeening.

Please help

 

$oExcel = _ExcelBookOpen ("C:MarketTmacro.xlsm")
Sleep(200)
$oExcel.Run("Macro1")
Sleep(200)
Send("^+{DOWN}")
Sleep(200)
Send("+{RIGHT 5}")
Sleep(200)
Send("^c")
Sleep(200)
_ExcelSheetActivate($oTmacro, $vMarket Data)

Share this post


Link to post
Share on other sites

Hi,

Please use code tags when posting code.

You have incorrect parms in _ExcelSheetActivate.  Try the following after supplying the correct sheet name...

$oExcel = _ExcelBookOpen ("C:\Market\Tmacro.xlsm")
 Sleep(200)
 $oExcel.Run("Macro1")
 Sleep(200)
 Send("^+{DOWN}")
 Sleep(200)
 Send("+{RIGHT 5}")
 Sleep(200)
 Send("^c")
 Sleep(200)
 _ExcelSheetActivate($oExcel, "my sheet name") ; or a variable containg your sheet name

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

hi,

I am opening a macro after opening from a macro file saved in C drive ( till here no issue)

I am facing a problem in activating an excel sheet which i have opened already in the program at initial stage.

How can i open a specific excel sheet????? is that the wrong code

" _ExcelSheetActivate($oExcel, "Market Data") "

Please help !

 

 

$oExcel = _ExcelBookOpen ("C:MarketTmacro.xlsm")
Sleep(200)
$oExcel.Run("Macro1")
Sleep(200)
Send("^+{DOWN}")
Sleep(200)
Send("+{RIGHT 5}")
Sleep(200)
Send("^c")                                      
Sleep(200)
_ExcelSheetActivate($oExcel, "Market Data")
sleep(200)

Share this post


Link to post
Share on other sites

Hi I have used the below code but still its not working....

if i check by using autoit window info then the title of my window is "Microsoft Excel - Market Data" so i kept it but the file name is only "Market Data.

i have tried three different commands but none is working

1. _ExcelSheetActivate($oExcel, "Microsoft Excel - Market Data")                     ********in auto it window info this is the title

2. _ExcelSheetActivate($oExcel, "Market Data - Microsoft Excel")                      *******if i open the file then this is the title

3._ExcelSheetActivate($oExcel, "Market Data")                                                  **********but the file name is only Market Data

$oExcel = _ExcelBookOpen ("C:MarketTmacro.xlsm")
Sleep(200)
$oExcel.Run("Macro1")
Sleep(200)
Send("^+{DOWN}")
Sleep(200)
Send("+{RIGHT 5}")
Sleep(200)
Send("^c")                                    
Sleep(200)
_ExcelSheetActivate($oExcel, "Microsoft Excel - Market Data")

Share this post


Link to post
Share on other sites

you don't want the title, you want the sheet name.  If you can open the workbook then you can see the sheet name.


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

Please stick to one thread for this problem...


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

sir
 
you are absolutely right but this is not activating the sheet i want to activate. Only three sheets are open at the moment
 
 

$oExcel = _ExcelBookOpen ("C:\Market\Tmacro.xlsm")
Sleep(200)
$oExcel.Run("Macro1")
Sleep(200)
Send("^+{DOWN}")
Sleep(200)
Send("+{RIGHT 5}")
Sleep(200)
Send("^c")                                    
Sleep(200)
_ExcelSheetActivate($oExcel, "Market Data")

Share this post


Link to post
Share on other sites

Can you post an example of the workbook and what your expected results are?


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

#cs ----------------------------------------------------------------------------

 AutoIt Version: 3.3.8.1
 Author:         myName

 Script Function:
    Template AutoIt script.

#ce ----------------------------------------------------------------------------

; Script Start - Add your code below here

#include <Excel.au3>

sleep(500)
send("#r")
send("excel{enter}")
sleep(500)
MouseClick("left", 27, 36, 1)       ;----------------------to click on file--------------------------     
sleep(200)
MouseClick("left", 58, 96, 1)        ;---------------------to click on save as----------------------
sleep(200)
MouseClick("left", 353, 89, 1)        ;--------------------to click on top column------------------
sleep(400)
send("{BS}")
sleep(800)
Send("C:\Market{enter}")
sleep(200)
MouseClick("left", 303, 386, 2)          ;--------------------------to type file name----------
sleep(200)
send("{BS}")
sleep(500)
Send("Market Data")
sleep(500)
Send("{ENTER}")
sleep(500)
if winwaitactive("Confirm Save As") Then
   MouseClick("left", 741, 379, 1)
Else
EndIf
send("#r")
send("C:\M and S{enter}")
sleep(200)
WinWaitActive("M and S")
sleep(200)
WinSetState("M and S", "", @SW_MAXIMIZE)
sleep(200)
WinActivate("M and S")
sleep(200)                                 ;---------------------------to click on 1st sheet---------------
MouseClick("left", 249, 128, 1)
Sleep(200)                                 ; ---------------------------to Rename 1st sheet as aaaa-----------------
Send("{F2}")
Sleep(200)
Send("aaaa")
Sleep(200)
Send("{ENTER}")
Sleep(200)
$oExcel = _ExcelBookOpen ("C:\Market\Tmacro.xlsm")
Sleep(200)
$oExcel.Run("Macro1")
Sleep(200)
Send("^+{DOWN}")
Sleep(200) 
Send("+{RIGHT 5}")
Sleep(200)
Send("^c")                                      ;------------------------to copy the 6 cells data----------------
Sleep(200)
_ExcelSheetActivate($oExcel, "Market Data")

hi..this is my complete program. First of all i am creating a excel sheet name "Market Data" then i am going to folder C:M and S and renaming the top excel sheet there and after that i am opening a macro and executing it and selecting 6 cells data and copying it ( till here no issues)

Now as i have said above there will be three sheets which will open by now. 1st.Market Data, 2nd Macro file, 3rd...it will open the file which i have rename.....................now i just want to activate sheet name "Market data" and paste data into that. I hope i am able to clarify my problem. Please need your help in this........................Just one problem...unable to activate specific excel sheet

Share this post


Link to post
Share on other sites

If you have access to the workbook you do not need to do all this external manipulation.  You can open the workbook and use the Excel UDF to do all that you require.


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

so you mean to say that the code i have used is right but it will not work in my program.... I was hoping for some kind of solution here. IF i would be able to activate my excel sheet then it would have been good and easy for me to right my complete program.....

Workbook was already open here just need to know how could i activate the specific excel sheet.......

what is this excel UDF procedure....can you guide me here step by step as how would i do that.... I am new to excel stuff using autoit.

I will be greatful to you. Thanks

But if sheet can be activated y single command in my program....

All people...any suggestions are welcome. 

Share this post


Link to post
Share on other sites

What is your return code from _ExcelSheetActivate($oExcel, "Market Data")?

You also do not have an error handler.


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

it is not giving any error though and if i add one more command after this _ExcelSheetActivate($oExcel, "Market Data") for example if i will activate one open window so it will activate one window but excel sheet is not gettign activated and it seems like my program is skipping this line.

I have downloaded new version 3.3.10 now .

I have added one more line in the end to activate "M and S" window and it is success but having problem with activating excel sheet :sweating: . Please anyone who can help.

$oExcel = _ExcelBookOpen ("C:\Market\Tmacro.xlsm")
Sleep(200)
$oExcel.Run("Macro1")
Sleep(200)
_ExcelSheetActivate($oExcel, "Market Data")
Sleep(200)
WinActivate("M and S")
Sleep(200)

it is getting executing but not this one only. I have downloaded the recent version now 3.3.10.2

Share this post


Link to post
Share on other sites

I have downloaded new version 3.3.10 now .

I have added one more line in the end to activate "M and S" window and it is success but having problem with activating excel sheet :sweating: . Please anyone who can help.

$oExcel = _ExcelBookOpen ("C:MarketTmacro.xlsm") Sleep(200) $oExcel.Run("Macro1") Sleep(200) _ExcelSheetActivate($oExcel, "Market Data") Sleep(200) WinActivate("M and S") Sleep(200)

it is getting executing but not executing single line only that is it is not activating excel sheet.

#include <Excel.au3>

sleep(500)
send("#r")
Sleep(100)
send("excel{enter}")
sleep(1000)
MouseClick("left", 27, 36, 1)       ;----------------------to click on file--------------------------
sleep(200)
MouseClick("left", 58, 96, 1)        ;---------------------to click on save as----------------------
sleep(200)
MouseClick("left", 353, 89, 1)        ;--------------------to click on top column------------------
sleep(400)
send("{BS}")
sleep(800)
Send("C:\Market{enter}")
sleep(200)
MouseClick("left", 303, 386, 2)          ;--------------------------to type file name----------
sleep(200)
send("{BS}")
sleep(500)
Send("Market Data")
sleep(500)
Send("{ENTER}")
sleep(500)
if winwaitactive("Confirm Save As") Then
   MouseClick("left", 741, 379, 1)
Else
EndIf
send("#r")
Sleep(200)
send("C:\M and S{enter}")
sleep(200)
WinWaitActive("M and S")
sleep(200)
WinSetState("M and S", "", @SW_MAXIMIZE)
sleep(200)
WinActivate("M and S")
sleep(200)                                 ;---------------------------to click on 1st sheet---------------
MouseClick("left", 249, 128, 1)
Sleep(200)                                 ; ---------------------------to Rename 1st sheet as aaaa-----------------
Send("{F2}")
Sleep(200)
Send("aaaa")
Sleep(200)
Send("{ENTER}")
Sleep(200)
$oExcel = _ExcelBookOpen ("C:\Market\Tmacro.xlsm")
Sleep(200)
$oExcel.Run("Macro1")
Sleep(200)
Send("^+{DOWN}")
Sleep(200)
Send("+{RIGHT 5}")
Sleep(200)
Send("^c")
Sleep(200)
_ExcelSheetActivate($oExcel, "Market Data")
Sleep(200)
WinActivate("M and S")
Sleep(200)

Share this post


Link to post
Share on other sites

Perhaps someone will be along shortly...


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

You shouldn't mix COM and GUI automation.

The current UDF isn't very good in automating multiple sheets at the same time.

If you like you can try my rewrite of the Excel UDF (which I hope will replace the current Excel UDF in AutoIt quite soon).

I will try to answer all questions.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.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
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

water sir,

I have downloaded the BETA version now and have also downloaded the "excel rewrite beta.zip" file.It contains multiple files when i unzippped it. DO i need to replace some files in some path or drive?????

Can you guide me step by step what do i need to do now so that i will be able to get the result from this command

_ExcelSheetActivate($oExcel, "Market Data")

I needed to design some program to ease my office work. It would be great if you can tell me any method by which i can activate specific excel sheet when 3-4 sheets are open at the same time. thank you !

Share this post


Link to post
Share on other sites

#18 ·  Posted (edited)

Most of the files are help files and example scripts.

Simply copy the "Excel Rewrite.au3" and "ExcelConstants.au3" to the directory where your script resides.

I will post a script how to open a workbook and activate a specific sheet here quite soon.

Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.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
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

hi.... i have copied  "Excel Rewrite.au3" and "ExcelConstants.au3 into the path (E:COMMANDS) where i have saved my compile script/program. but still this command dosent give any result 

_ExcelSheetActivate($oExcel, "Market Data").

I will wait for the solution from your side if you will find something. thanks. I am also pasting 

#include <Excel.au3>

sleep(500)
send("#r")
Sleep(100)
send("excel{enter}")
sleep(1000)
MouseClick("left", 27, 36, 1)       ;----------------------to click on file--------------------------
sleep(200)
MouseClick("left", 58, 96, 1)        ;---------------------to click on save as----------------------
sleep(200)
MouseClick("left", 353, 89, 1)        ;--------------------to click on top column------------------
sleep(400)
send("{BS}")
sleep(800)
Send("C:\Market{enter}")
sleep(200)
MouseClick("left", 303, 386, 2)          ;--------------------------to type file name----------
sleep(200)
send("{BS}")
sleep(500)
Send("Market Data")
sleep(500)
Send("{SPACE}")
Sleep(200)
MouseClick("left", 662, 367, 1)          ;------------------to save file as excel 97 workbook-------------
Sleep(200)
Send("{UP 22}")
Sleep(200)
Send("{ENTER}")
sleep(200)
Send("{ENTER}")
sleep(200)
if winwaitactive("Confirm Save As") Then
   MouseClick("left", 741, 379, 1)
Else
EndIf
send("#r")
Sleep(200)
send("C:\M and S{enter}")
sleep(200)
WinWaitActive("M and S")
sleep(200)
WinSetState("M and S", "", @SW_MAXIMIZE)
sleep(200)
WinActivate("M and S")
sleep(200)                                 ;---------------------------to click on 1st sheet---------------
MouseClick("left", 249, 128, 1)
Sleep(200)                                 ; ---------------------------to Rename 1st sheet as aaaa-----------------
Send("{F2}")
Sleep(200)
Send("aaaa")
Sleep(200)
Send("{ENTER}")
Sleep(200)
$oExcel = _ExcelBookOpen ("C:\Market\Tmacro.xlsm")
Sleep(200)
$oExcel.Run("Macro1")
Sleep(200)
_ExcelSheetActivate($oExcel, "Market Data")

my full program here

Share this post


Link to post
Share on other sites

#20 ·  Posted (edited)

First: You can't mix "Excel.au3" and "Excel Rewrite.au3"

Second: Excel.au3 only works with the active sheet, Excel Rewrite can work with any sheet.

 

So can we do it step by step?

#include <MsgBoxConstants.au3>
#include <Excel rewrite.au3>
$oExcel = _Excel_Open()
If @error Then Exit Msgbox($MB_SYSTEMMODAL, "Error", "Error opening Excel application. @error = " & @error & ", @extended = " & @extended)
$oWorkbook = _Excel_BookOpen($oExcel, "C:\Market\Tmacro.xlsm")
If @error Then Exit Msgbox($MB_SYSTEMMODAL, "Error", "Error opening Excel workbook. @error = " & @error & ", @extended = " & @extended)
What would you do next? Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.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
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

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  
Followers 0