Activate excel window

[Twinlinked]

Hello,

I posted some code earlier that was not working properly,

But i think i have a more fundamental problem then i thought.

I do not seem to be able to write AutoIt code that will activate a second excel workbook. when i use WinActivate to activate either excel window that is open, only the last window to be selected is activated (if any at all)

could someone give me a basic example of the code i should be using? are there any special setting i need to use i have tried Opt("WinTitleMatchMode" to no avail

thanks very much,

A.

[water]

It depends on what you are trying to do but you could use the Excel functions that are builtin into AutoIt.

No fiddling with window titles etc. just call _Excel* functions to do what you want.

Can you describe what you want to do?

[Twinlinked]

all i am try to do is activate one work book, copy, then activate a second workbook and paste special

turns out i can activate the windows if they are in separate instances of excel but then i cannot past special..

A.

[timbo]

Hi there,

I've knocked together something that may help you. As Water said, you should check out the "_Excel" functions in the help file, they really will help you going further.

That said, I actually have done things the hard way and didn't use any of the "_Excel" functions (I have my reasons). But you should really use the "_Excel" functions unless you have a good understanding of VBA (Visual Basic for Applications).

Try this (no need to change/add anything, just copy-paste into an au3 file and run it):

expandcollapse popup

Global $WorkBook1=""    ;e.g. "C:\sample1.xls" (Optional)
Global $SheetName1=""   ;e.g. "Sheet1" (Optional)

Global $WorkBook2=""    ;e.g. "C:\sample2.xls" (Optional)
Global $SheetName2=""   ;e.g. "Sheet1" (Optional)

Global $oExcel = ObjCreate("Excel.Application")

$oExcel.visible=1
If $WorkBook1="" Then   ;If a workbook was not specifed above ($WorkBook1), then create a new workbook for example purposes
    $oExcel.WorkBooks.Add   ;Add a new blank workbook to excel
    Sleep(1000)

    For $Row = 1 To 10  ;Make some sample data
        For $Column = 1 To 10
            $oExcel.Activesheet.Cells($Row, $Column).Value=($Row*$Column)
        Next
    Next
    Sleep(1000)
Else    ;If a workbook was specifed above ($WorkBook2), then open it and select the appropriate sheet
    $oExcel.WorkBooks.Open($WorkBook1)
    Sleep(1000)
    $oExcel.Sheets($SheetName1).Select
    Sleep(1000)
EndIf

$oExcel.Activesheet.Cells.Copy  ;Copy the content from the first Workbook
Sleep(1000)

If $WorkBook2="" Then   ;If a workbook was not specifed above ($WorkBook2), then create a new workbook for example purposes
    $oExcel.WorkBooks.Add
    Sleep(1000)
Else    ;If a workbook was specifed above ($WorkBook2), then open it and select the appropriate sheet
    $oExcel.WorkBooks.Open($WorkBook2)
    Sleep(1000)
    $oExcel.Sheets($SheetName2).Select
    Sleep(1000)
EndIf

$oExcel.ActiveSheet.Cells(1, 1).Select
Sleep(1000)
$oExcel.ActiveSheet.Cells(1, 1).PasteSpecial(0xFFFFEFBD)    ;Paste Values the copied content from the first Workbook into the second Workbook

$oExcel.ActiveWorkBook.SaveAs(@ScriptDir&"\CopiedSheet_"&@YEAR&@MON&@MDAY&"_"&@HOUR&@MIN&@SEC&".xls") ;Save the file in the same folder as this script
;$oExcel.ActiveWorkBook.Save ; Alternately you could just save the existing file

The above is an example, and as a result it has alot of unessesary code. The following is a stripped down version of the code above:

Global $WorkBook1=""    ;e.g. "C:\sample1.xls" (Required)
Global $SheetName1=""   ;e.g. "Sheet1" (Required)

Global $WorkBook2=""    ;e.g. "C:\sample2.xls" (Required)
Global $SheetName2=""   ;e.g. "Sheet1" (Required)

Global $oExcel = ObjCreate("Excel.Application")

$oExcel.visible=1
$oExcel.WorkBooks.Open($WorkBook1)
$oExcel.Sheets($SheetName1).Select

$oExcel.Activesheet.Cells.Copy  ;Copy the content from the first Workbook

$oExcel.WorkBooks.Open($WorkBook2)
$oExcel.Sheets($SheetName2).Select

$oExcel.ActiveSheet.Cells(1, 1).PasteSpecial(0xFFFFEFBD)    ;Paste Values the copied content from the first Workbook into the second Workbook

$oExcel.ActiveWorkBook.SaveAs(@ScriptDir&"\CopiedSheet_"&@YEAR&@MON&@MDAY&"_"&@HOUR&@MIN&@SEC&".xls") ;Save the file in the same folder as this script
;$oExcel.ActiveWorkBook.Save ; Alternately you could just save the existing file

Now don't tell me I should have used "With $oExcel ... EndWith". I thought the code would be easier to understand without it.

Hope this helps.

Edit: This script will open Excel and the WorkBooks for you, you shouldn't have your Excel files open when you run these.

-Timbo

Edited July 14, 2011 by timbo

[Twinlinked]

Hi there,

I've knocked together something that may help you. As Water said, you should check out the "_Excel" functions in the help file, they really will help you going further.

That said, I actually have done things the hard way and didn't use any of the "_Excel" functions (I have my reasons). But you should really use the "_Excel" functions unless you have a good understanding of VBA (Visual Basic for Applications).

Try this (no need to change/add anything, just copy-paste into an au3 file and run it):

expandcollapse popup

Global $WorkBook1=""    ;e.g. "C:\sample1.xls" (Optional)
Global $SheetName1=""   ;e.g. "Sheet1" (Optional)

Global $WorkBook2=""    ;e.g. "C:\sample2.xls" (Optional)
Global $SheetName2=""   ;e.g. "Sheet1" (Optional)

Global $oExcel = ObjCreate("Excel.Application")

$oExcel.visible=1
If $WorkBook1="" Then   ;If a workbook was not specifed above ($WorkBook1), then create a new workbook for example purposes
    $oExcel.WorkBooks.Add   ;Add a new blank workbook to excel
    Sleep(1000)

    For $Row = 1 To 10  ;Make some sample data
        For $Column = 1 To 10
            $oExcel.Activesheet.Cells($Row, $Column).Value=($Row*$Column)
        Next
    Next
    Sleep(1000)
Else    ;If a workbook was specifed above ($WorkBook2), then open it and select the appropriate sheet
    $oExcel.WorkBooks.Open($WorkBook1)
    Sleep(1000)
    $oExcel.Sheets($SheetName1).Select
    Sleep(1000)
EndIf

$oExcel.Activesheet.Cells.Copy  ;Copy the content from the first Workbook
Sleep(1000)

If $WorkBook2="" Then   ;If a workbook was not specifed above ($WorkBook2), then create a new workbook for example purposes
    $oExcel.WorkBooks.Add
    Sleep(1000)
Else    ;If a workbook was specifed above ($WorkBook2), then open it and select the appropriate sheet
    $oExcel.WorkBooks.Open($WorkBook2)
    Sleep(1000)
    $oExcel.Sheets($SheetName2).Select
    Sleep(1000)
EndIf

$oExcel.ActiveSheet.Cells(1, 1).Select
Sleep(1000)
$oExcel.ActiveSheet.Cells(1, 1).PasteSpecial(0xFFFFEFBD)    ;Paste Values the copied content from the first Workbook into the second Workbook

$oExcel.ActiveWorkBook.SaveAs(@ScriptDir&"\CopiedSheet_"&@YEAR&@MON&@MDAY&"_"&@HOUR&@MIN&@SEC&".xls") ;Save the file in the same folder as this script
;$oExcel.ActiveWorkBook.Save ; Alternately you could just save the existing file

The above is an example, and as a result it has alot of unessesary code. The following is a stripped down version of the code above:

Global $WorkBook1=""    ;e.g. "C:\sample1.xls" (Required)
Global $SheetName1=""   ;e.g. "Sheet1" (Required)

Global $WorkBook2=""    ;e.g. "C:\sample2.xls" (Required)
Global $SheetName2=""   ;e.g. "Sheet1" (Required)

Global $oExcel = ObjCreate("Excel.Application")

$oExcel.visible=1
$oExcel.WorkBooks.Open($WorkBook1)
$oExcel.Sheets($SheetName1).Select

$oExcel.Activesheet.Cells.Copy  ;Copy the content from the first Workbook

$oExcel.WorkBooks.Open($WorkBook2)
$oExcel.Sheets($SheetName2).Select

$oExcel.ActiveSheet.Cells(1, 1).PasteSpecial(0xFFFFEFBD)    ;Paste Values the copied content from the first Workbook into the second Workbook

$oExcel.ActiveWorkBook.SaveAs(@ScriptDir&"\CopiedSheet_"&@YEAR&@MON&@MDAY&"_"&@HOUR&@MIN&@SEC&".xls") ;Save the file in the same folder as this script
;$oExcel.ActiveWorkBook.Save ; Alternately you could just save the existing file

Now don't tell me I should have used "With $oExcel ... EndWith". I thought the code would be easier to understand without it.

Hope this helps.

Edit: This script will open Excel and the WorkBooks for you, you shouldn't have your Excel files open when you run these.

-Timbo

Wow, Thx,

the first one seems to work well,

now i just need to figure out how to get it to activate workbook "template"

and then activate workbook "MSR..." the workbook always starts with MSR but will have a different set of words after those 3 letters.

i was really hoping winactivate would work for this..

A.

[timbo]

Wow, Thx,

the first one seems to work well,

now i just need to figure out how to get it to activate workbook "template"

and then activate workbook "MSR..." the workbook always starts with MSR but will have a different set of words after those 3 letters.

i was really hoping winactivate would work for this..

A.

Did you happen to see this part of the code:

Global $WorkBook1=""    ;e.g. "C:\sample1.xls" (Optional)
Global $SheetName1=""   ;e.g. "Sheet1" (Optional)

Global $WorkBook2=""    ;e.g. "C:\sample2.xls" (Optional)
Global $SheetName2=""   ;e.g. "Sheet1" (Optional)

It says they're "Optional" as that is an example script, so it will generate data to copy on the fly. If you specify your own files, it will use them.

So, try adding the full path to your workbook "template" into the "$WorkBook1" variable (as well as adding the sheet name that you would like copied into the "$SheetName1" variable), then add the full path to the workbook "MSR..." into the "$WorkBook2" variable (with sheet name to copy to in the "$SheetName2" variable). I presume that if you can generate the "MSR..." file name for use with WinActivate, then you should be able to specify it for the "$WorkBook2" variable.

I know you're experienced with WinActivate, and thus would like to use it, but using the Excel API is the most reliable way to do it (possibly the only way). "Paste Special" only works within the one instance of Excel (As you've noted in a previous post). One instance means WinActivate can't select second spreadsheet cause (I believe) Excel changes the window title on the fly as you switch between files. So, as much as you might like to use WinActivate, it might be time to investigate another method.

Also, which "Paste Special" option are you trying to use; Formulas, Values, Formats...? My script is using Values.

-Timbo

[rover]

Hello,

I posted some code earlier that was not working properly,

But i think i have a more fundamental problem then i thought.

I do not seem to be able to write AutoIt code that will activate a second excel workbook. when i use WinActivate to activate either excel window that is open, only the last window to be selected is activated (if any at all)

could someone give me a basic example of the code i should be using? are there any special setting i need to use i have tried Opt("WinTitleMatchMode" to no avail

thanks very much,

A.

@Twinlinked

As Water and timbo said, use the Excel COM interface to do this.

Use the WorkBooks().Activate and WorkSheets().Activate methods to tab between workbooks/sheets.

A COM error handler to trap otherwise fatal errors and IsObj() checking is essential to working with COM.

Read the COM Error Handling section of Obj/COM Reference in the help file.

(If you don't already know)

Excel com reference pages: http://msdn.microsoft.com/en-us/library/ee861528.aspx

Example opens template and two msr workbooks and copies from template to each msr workbook.

I am unsure as to how you pastespecial more than once without having to recopy cells from template.xls.

Extra Sleep()s added to show activity.

PS: I just noticed bogQ mentioned using activate in your other thread as I was about to post this example

BTW, It is advisable to not open multiple topics on the same issue.

This is seen by the forum Mods as an attempt to circumvent the 24 hour limit between post bumps.

expandcollapse popup

Opt('MustDeclareVars', 1)

;COM error handler
Global $fCOMConsoleDebug = True ; set to false for your final production code

Global $g_eventerror = 0 ; to be checked to know if com error occurs. Must be reset after handling.
Global $oMyError = ObjEvent("AutoIt.Error", "MyErrFunc") ; Install a custom COM error handler
If IsObj($oMyError) = 0 Then Exit ConsoleWrite("!Error creating COM Error handler." & @LF)

Global $XLS1 = @ScriptDir & "\template.xls"
Global $WorkBook1 = "template.xls"
Global $SheetName1 = "Sheet 1" ; 'Sheet Xn' or number of sheet (Xn)

Global $XLS2 = @ScriptDir & "\msr-1.xls"
Global $WorkBook2 = "msr-1.xls"
Global $SheetName2 = "Sheet 1"

Global $XLS3 = @ScriptDir & "\msr-2.xls"
Global $WorkBook3 = "msr-2.xls"
Global $SheetName3 = "Sheet 1"

Global $sCurWkBk, $sCurSheet

Global $oExcel = ObjCreate("Excel.Application")
If Not IsObj($oExcel) Then
    ConsoleWrite("!COM Error: Excel.Application" & @LF)
    Exit
EndIf
$oExcel.visible = 1


$oExcel.WorkBooks.Open($XLS1)
If $g_eventerror = 1 Then ;example of using COM error return
    ;add error handling here
    ;2nd fileexist test?, exit, consolewrite, msgbox, log to file, ignore and continue on to next file etc.
    $g_eventerror = 0 ; after error handled, reset $g_eventerror
EndIf
Sleep(1000)
$oExcel.WorkBooks.Open($XLS2)
Sleep(1000)
$oExcel.WorkBooks.Open($XLS3);third workbook now active
Sleep(1000)

;TEMPLATE
$oExcel.WorkBooks($WorkBook1).Activate ;activate first workbook
$oExcel.WorkSheets($SheetName1).Activate;Calling this method is equivalent to clicking the sheet's tab.

;possibly use as error checking to verify correct workbook/sheet is active
$sCurWkBk = $oExcel.ActiveWorkBook.Name
ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ') : ActiveWorkBook = ' & $sCurWkBk & @crlf)
$sCurSheet = $oExcel.ActiveSheet.Name
ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ') : ActiveSheet = ' & $sCurSheet & @crlf)

$oExcel.Sheets($SheetName1).Select(True) ;select first sheet
$oExcel.Activesheet.Cells.Copy ;Copy the content from the first Workbook


Sleep(2000)


;MSR 1
$oExcel.WorkBooks($WorkBook2).Activate ;activate second workbook
$oExcel.WorkSheets($SheetName2).Activate;Calling this method is equivalent to clicking the sheet's tab.
$oExcel.Sheets($SheetName2).Select(True) ;select first sheet

;possibly use as error checking to verify correct workbook/sheet is active

$sCurWkBk = $oExcel.ActiveWorkBook.Name
ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ') : ActiveWorkBook = ' & $sCurWkBk & @crlf)
$sCurSheet = $oExcel.ActiveSheet.Name
ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ') : ActiveSheet = ' & $sCurSheet & @crlf)

$oExcel.ActiveSheet.Cells(1, 1).Select
Sleep(1000)
$oExcel.ActiveSheet.Cells(1, 1).PasteSpecial(0xFFFFEFBD) ;Paste Values the copied content from the first Workbook into the second Workbook
Sleep(1000)
$oExcel.ActiveSheet.Cells(1, 1).Select ;deselect all
Sleep(1000)
$oExcel.ActiveWorkBook.Save ; save the existing file
$oExcel.ActiveWorkBook.Close


;back to template to recopy, unless there is a method to using pastespecial that keeps copy in clipboard for re-pasting
;TEMPLATE
$oExcel.WorkBooks($WorkBook1).Activate ;activate first workbook
$oExcel.WorkSheets($SheetName1).Activate;Calling this method is equivalent to clicking the sheet's tab.

;possibly use as error checking to verify correct workbook/sheet is active
$sCurWkBk = $oExcel.ActiveWorkBook.Name
ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ') : ActiveWorkBook = ' & $sCurWkBk & @crlf)
$sCurSheet = $oExcel.ActiveSheet.Name
ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ') : ActiveSheet = ' & $sCurSheet & @crlf)

$oExcel.Sheets($SheetName1).Select(True) ;select first sheet
$oExcel.Activesheet.Cells.Copy ;Copy the content from the first Workbook


Sleep(1000)


;MSR 2
$oExcel.WorkBooks($WorkBook3).Activate ;activate third workbook
$oExcel.WorkSheets($SheetName3).Activate;Calling this method is equivalent to clicking the sheet's tab.
$oExcel.Sheets($SheetName3).Select(True) ;select first sheet

$sCurWkBk = $oExcel.ActiveWorkBook.Name
ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ') : ActiveWorkBook = ' & $sCurWkBk & @crlf)
$sCurSheet = $oExcel.ActiveSheet.Name
ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ') : ActiveSheet = ' & $sCurSheet & @crlf)

$oExcel.ActiveSheet.Cells(1, 1).Select
Sleep(1000)
$oExcel.ActiveSheet.Cells(1, 1).PasteSpecial(0xFFFFEFBD) ;Paste Values the copied content from the first Workbook into the third Workbook
Sleep(1000)
$oExcel.ActiveSheet.Cells(1, 1).Select ;deselect all
Sleep(1000)
$oExcel.ActiveWorkBook.Save ; save the existing file
$oExcel.ActiveWorkBook.Close ;close active workbook


Sleep(2000)
$oExcel.Application.Quit
Exit

; COM error handler
Func MyErrFunc()
    ;NOTE: do not use consolewrite in production code
    ;optionally add logging to file instead of consolewrite
    Local $HexNumber = Hex($oMyError.number, 8)
    If $fCOMConsoleDebug = True Then
        ConsoleWrite("! We intercepted a COM Error !" & @CRLF & _
            "Number is: " & $HexNumber & @CRLF & _
            "ScriptLine: " & $oMyError.scriptline & @CRLF & _
            "Windescription is: " & StringStripWS($oMyError.description, 3) & @CRLF)
    EndIf
    $g_eventerror = 1 ; something to check for when this function returns
EndFunc   ;==>MyErrFunc

[abberration]

wrong post, sorry

Edited April 16, 2019 by abberration