Twinlinked Posted July 13, 2011 Posted July 13, 2011 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 Posted July 13, 2011 Posted July 13, 2011 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? My UDFs and Tutorials: Spoiler UDFs: Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs: Excel - Example Scripts - Wiki Word - Wiki Tutorials: ADO - Wiki WebDriver - Wiki
Twinlinked Posted July 13, 2011 Author Posted July 13, 2011 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 Posted July 14, 2011 Posted July 14, 2011 (edited) 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 popupGlobal $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 Posted July 14, 2011 Author Posted July 14, 2011 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 popupGlobal $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 Posted July 14, 2011 Posted July 14, 2011 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 Posted July 15, 2011 Posted July 15, 2011 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 popupOpt('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 I see fascists...
abberration Posted April 16, 2019 Posted April 16, 2019 (edited) wrong post, sorry Edited April 16, 2019 by abberration Easy MP3 | Software Installer | Password Manager
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now