how to enable specific excel sheet from multiple open excel sheets

[gauravvogue]

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)

[kylomas]

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

[karanmoonlight]

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)

[karanmoonlight]

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

[kylomas]

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

[kylomas]

Please stick to one thread for this problem...

[karanmoonlight]

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

[kylomas]

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

[karanmoonlight]

expandcollapse popup

#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

[kylomas]

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.

[karanmoonlight]

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. 

[kylomas]

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

You also do not have an error handler.

[karanmoonlight]

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

[karanmoonlight]

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

expandcollapse popup

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

[kylomas]

Perhaps someone will be along shortly...

[water]

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.

[karanmoonlight]

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 !

[water]

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 February 20, 2014 by water

[karanmoonlight]

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 

expandcollapse popup

#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

[water]

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 February 20, 2014 by water