gassis Posted March 21, 2014 Share Posted March 21, 2014 expandcollapse popup#AutoIt3Wrapper_AU3Check_Stop_OnWarning=Y #AutoIt3Wrapper_AU3Check_Parameters=-d -w 1 -w 2 -w 3 -w 4 -w 5 -w 6 #include <Excel Rewrite.au3> #include <Constants.au3> ; Create application object , they must all have different numbers. Each workbook and function should have one. Global $oAppl = _Excel_Open() Global $oApp2 = _Excel_Open() Global $oApp3 = _Excel_Open() Global $oApp4 = _Excel_Open() Global $oApp5 = _Excel_Open() Global $oApp6 = _Excel_Open() Global $oApp7 = _Excel_Open() Global $oApp8 = _Excel_Open() Global $oApp9 = _Excel_Open() Global $oApp10 = _Excel_Open() Global $oApp11 = _Excel_Open() Global $oApp12 = _Excel_Open() Global $0App13 = _Excel_Open() Validator($oApp2) DashGen($oAppl) SSSG($oApp4) GCA($oApp5) SystemSales($oApp6) RegionGCA($oApp7) RegionSSSG($oApp8) RegionSystem($oApp9) UnitSSSG($oApp10) UnitSystem($oApp11) Market($oApp12) StoreCount($oApp3) ;put your function code below the validator, it must go first to push all other changes. Func Validator($oApp2) ; Open the Main File / validaot that will push the updates to all other files Local $sWorkbook = "S:\Finance\Automated-Reports\Source Files\KFC Report for TB\Validator.xlsm" Local $oWorkbook = _Excel_BookOpen($oApp2, $sWorkbook) ;Local $oWorkbook = _Excel_BookOpen($oAppl, $sWorkbook, Default, Default, True) ;Force Excel Window to be active WinActivate("Microsoft Excel - Validator.xlsm") WinWaitActive("Microsoft Excel - Validator.xlsm") Sleep(4000); Runs the macro to update the Pivot , looks to see if the new period is in the cube. if not nothing will happen . ControlSend($sWorkbook,"","","{ALT}LPM") ControlSend($sWorkbook,"","","UpdateCurrent") ControlSend($sWorkbook,"","","!R") sleep(10000) WinWaitActive( "Microsoft Excel" ) ControlSend("{ENTER}") Sleep(4000); Runs the macro to update the Pivot , looks to see if the new period is in the cube. if not nothing will happen . ControlSend("!LPM") ControlSend("RemoveBrand") ControlSend("!R") sleep(10000) WinWaitActive( "Microsoft Excel" ) ControlSend("{ENTER}") Local $sResult = _Excel_RangeRead($oWorkbook, Default, "A1") ;Excel_RangeWrite($oWorkbook, Default, $sResult, "A3") IF $sResult = "Don't Update" Then Global $Var_1 =_Excel_RangeRead($oWorkbook, Default, "A2") WinActive("Microsoft Excel - Validator.") ; Runs the macro to update the Pivot so that validator is now both sheets on same period ControlSend("!LPM") ControlSend("UpdateAll") ControlSend("!R") sleep(10000) WinWaitActive( "Microsoft Excel" ) ControlSend("{ENTER}") ;the Main , this will save the file to the new period so that both of them now show the same period WinActive("Microsoft Excel - Validator.xlsm") ControlSend("^s") Sleep(5000) Else _Excel_BookClose($oWorkbook, True ) Sleep(3000) ControlSend("!fx") Exit EndIf ; MAJOR KEY , you must make your variable equal to to the global varaible . it is the one that will tell your models which period to use. EndFunc Func DashGen($oAppl) ; Open the Main File/ generator for dashboard Local $sWorkbook = "S:\Finance\Automated-Reports\Source Files\KFC Report for TB\PH-KFC_Dashboard_Generator.xlsm" Local $oWorkbook1 = _Excel_BookOpen($oAppl, $sWorkbook) ;Local $oWorkbook = _Excel_BookOpen($oAppl, $sWorkbook, Default, Default, True) ; Force Excel Window to be active ;WinActivate ( "Microsoft Excel - PH-KFC_Dashboard_Generator.xlsm" ) WinWaitActive("Microsoft Excel - PH-KFC_Dashboard_Generator.xlsm") sleep(5000) ; Copy current Period # and increases it in 1 , this will make the value of the period to match the validator. If you use another function you need to make the $SResult a different number ;Local $sResult2 = _Excel_RangeRead($oWorkbook, Default, "B1") Local $sResult2 = $Var_1 _Excel_RangeWrite($oWorkbook1, Default, $sResult2, "B1") ; Runs the macro to update the Pivot table ControlSend("!LPM") ControlSend("UpdatePivot") ControlSend("!R") sleep(3000000) WinWaitActive( "Microsoft Excel" ) ControlSend("{ENTER}") ; Runs the macro to update the Final Sheet ControlSend("!LPM") ControlSend("FinalStep") ControlSend("!R") sleep(10000) WinWaitActive( "Microsoft Excel" ) ControlSend("{ENTER}") ; Saves the Main File WinWaitActive("Microsoft Excel - PH-KFC_Dashboard_Generator.xlsm") ControlSend("^s") Sleep(5000) ;Generates the file to ControlSend to TB ControlSend("!FA") WinWaitActive("Save As") ControlSend ("S:\Finance\Automated-Reports\Period Reports\P" & $sResult2 & "\P" & $sResult2 & " - Dashboard.xlsm" ) ControlSend("!S") Sleep(5000) ControlSend("{TAB}") ControlSend("{ENTER}") ;Close excel??? _Excel_BookClose($oWorkbook1, True ) Sleep(3000) ;ControlSend("!fx") EndFunc Func SSSG($oApp4) Local $sWorkbook = "S:\Finance\Automated-Reports\Sales Reports\Per Brand_Same Store Sales & Transactions.xlsm" Local $oWorkbook4 = _Excel_BookOpen($oApp4, $sWorkbook) ;Local $oWorkbook = _Excel_BookOpen($oAppl, $sWorkbook, Default, Default, True) ; Force Excel Window to be active WinActivate ( "Microsoft Excel - Per Brand_Same Store Sales & Transactions.xlsm" ) ; Runs the macro to update the Pivot table ControlSend("!LPM") ControlSend("Unhide") ControlSend("!R") sleep(20000) WinWaitActive( "Microsoft Excel" ) ControlSend("{ENTER}") Local $sResult4 = _Excel_RangeRead($oWorkbook4, Default, "F1") $sResult4 = $Var_1 _Excel_RangeWrite($oWorkbook4, Default, $sResult4, "F1") ; Runs the macro to update the Pivot table ControlSend("!LPM") ControlSend("UpdatePivot") ControlSend("!R") sleep(20000) WinWaitActive( "Microsoft Excel" ) ControlSend("{ENTER}") ; Runs the macro to update the Pivot table ControlSend("!LPM") ControlSend("Hide") ControlSend("!R") sleep(10000) WinWaitActive( "Microsoft Excel" ) ControlSend("{ENTER}") ; Saves the Main File WinWaitActive("Microsoft Excel - Per Brand_Same Store Sales & Transactions.xlsm") ControlSend("^s") Sleep(5000) ;Generates the file to ControlSend to TB ControlSend("!FA") WinWaitActive("Save As") ControlSend ("S:\Finance\Automated-Reports\Period Reports\P" & $sResult4 & "\P" & $sResult4 & " - Per Brand_Same Store Sales & Transactions.xlsm" ) ControlSend("!S") Sleep(5000) ControlSend("{TAB}") ControlSend("{ENTER}") _Excel_BookClose($oWorkbook4, True ) Sleep(3000) EndFunc Func GCA($oApp5) Local $sWorkbook = "S:\Finance\Automated-Reports\Sales Reports\Per Brand_Same Store GCA & WPSA.xlsm" Local $oWorkbook5 = _Excel_BookOpen($oApp5, $sWorkbook) ;Local $oWorkbook = _Excel_BookOpen($oAppl, $sWorkbook, Default, Default, True) ; Force Excel Window to be active WinActivate ( "Microsoft Excel - Per Brand_Same Store GCA & WPSA.xlsm" ) ; Runs the macro to update the Pivot table ControlSend("!LPM") ControlSend("Unhide") ControlSend("!R") sleep(20000) WinWaitActive( "Microsoft Excel" ) ControlSend("{ENTER}") Local $sResult5 = _Excel_RangeRead($oWorkbook5, Default, "F1") $sResult5 = $Var_1 _Excel_RangeWrite($oWorkbook5, Default, $sResult5, "F1") ; Runs the macro to update the Pivot table ControlSend("!LPM") ControlSend("UpdatePivot") ControlSend("!R") sleep(20000) WinWaitActive( "Microsoft Excel" ) ControlSend("{ENTER}") ; Runs the macro to update the Pivot table ControlSend("!LPM") ControlSend("Hide") ControlSend("!R") sleep(10000) WinWaitActive( "Microsoft Excel" ) ControlSend("{ENTER}") ; Saves the Main File WinWaitActive("Microsoft Excel - Per Brand_Same Store GCA & WPSA.xlsm") ControlSend("^s") Sleep(5000) ;Generates the file to ControlSend to TB ControlSend("!FA") WinWaitActive("Save As") ControlSend ("S:\Finance\Automated-Reports\Period Reports\P" & $sResult5 & "\P" & $sResult5 & " - Per Brand_Same Store GCA & WPSA.xlsm" ) ControlSend("!S") Sleep(5000) ControlSend("{TAB}") ControlSend("{ENTER}") _Excel_BookClose($oWorkbook5, True ) Sleep(3000) EndFunc Func SystemSales($oApp6) Local $sWorkbook = "S:\Finance\Automated-Reports\Sales Reports\Per Brand_System Sales & Transactions.xlsm" Local $oWorkbook6 = _Excel_BookOpen($oApp6, $sWorkbook) ;Local $oWorkbook = _Excel_BookOpen($oAppl, $sWorkbook, Default, Default, True) ; Force Excel Window to be active WinActivate ( "Microsoft Excel - Per Brand_System Sales & Transactions.xlsm" ) ; Runs the macro to update the Pivot table ControlSend("!LPM") ControlSend("Unhide") ControlSend("!R") sleep(20000) WinWaitActive( "Microsoft Excel" ) ControlSend("{ENTER}") Local $sResult6 = _Excel_RangeRead($oWorkbook6, Default, "F1") $sResult6 = $Var_1 _Excel_RangeWrite($oWorkbook6, Default, $sResult6, "F1") ; Runs the macro to update the Pivot table ControlSend("!LPM") ControlSend("UpdatePivot") ControlSend("!R") sleep(20000) WinWaitActive( "Microsoft Excel" ) ControlSend("{ENTER}") ; Runs the macro to update the Pivot table ControlSend("!LPM") ControlSend("Hide") ControlSend("!R") sleep(10000) WinWaitActive( "Microsoft Excel" ) ControlSend("{ENTER}") ; Saves the Main File WinWaitActive("Microsoft Excel - Per Brand_System Sales & Transactions.xlsm") ControlSend("^s") Sleep(5000) ;Generates the file to ControlSend to TB ControlSend("!FA") WinWaitActive("Save As") ControlSend ("S:\Finance\Automated-Reports\Period Reports\P" & $sResult6 & "\P" & $sResult6 & " - Per Brand_System Sales & Transactions.xlsm" ) ControlSend("!S") Sleep(5000) ControlSend("{TAB}") ControlSend("{ENTER}") _Excel_BookClose($oWorkbook6, True ) Sleep(3000) EndFunc Func RegionGCA($oApp7) Local $sWorkbook = "S:\Finance\Automated-Reports\Sales Reports\Per Region_Same Store GCA & WPSA.xlsm" Local $oWorkbook7 = _Excel_BookOpen($oApp7, $sWorkbook) ;Local $oWorkbook = _Excel_BookOpen($oAppl, $sWorkbook, Default, Default, True) ; Force Excel Window to be active WinActivate ( "Microsoft Excel - Per Region_Same Store GCA & WPSA.xlsm" ) ; Runs the macro to update the Pivot table ControlSend("!LPM") ControlSend("Unhide") ControlSend("!R") sleep(20000) WinWaitActive( "Microsoft Excel" ) ControlSend("{ENTER}") Local $sResult7 = _Excel_RangeRead($oWorkbook7, Default, "F1") $sResult7 = $Var_1 _Excel_RangeWrite($oWorkbook7, Default, $sResult7, "F1") ; Runs the macro to update the Pivot table ControlSend("!LPM") ControlSend("UpdatePivot") ControlSend("!R") sleep(20000) WinWaitActive( "Microsoft Excel" ) ControlSend("{ENTER}") ; Runs the macro to update the Pivot table ControlSend("!LPM") ControlSend("Hide") ControlSend("!R") sleep(10000) WinWaitActive( "Microsoft Excel" ) ControlSend("{ENTER}") ; Saves the Main File WinWaitActive("Microsoft Excel - Per Region_Same Store GCA & WPSA.xlsm") ControlSend("^s") Sleep(5000) ;Generates the file to ControlSend to TB ControlSend("!FA") WinWaitActive("Save As") ControlSend ("S:\Finance\Automated-Reports\Period Reports\P" & $sResult7 & "\P" & $sResult7 & " - Per Region_Same Store GCA & WPSA.xlsm" ) ControlSend("!S") Sleep(5000) ControlSend("{TAB}") ControlSend("{ENTER}") _Excel_BookClose($oWorkbook7, True ) Sleep(3000) EndFunc Func RegionSSSG($oApp8) Local $sWorkbook = "S:\Finance\Automated-Reports\Sales Reports\Per Region_Same Store Sales Transactions.xlsm" Local $oWorkbook8 = _Excel_BookOpen($oApp8, $sWorkbook) ;Local $oWorkbook = _Excel_BookOpen($oAppl, $sWorkbook, Default, Default, True) ; Force Excel Window to be active WinActivate ( "Microsoft Excel - Per Region_Same Store Sales Transactions.xlsm" ) ; Runs the macro to update the Pivot table ControlSend("!LPM") ControlSend("Unhide") ControlSend("!R") sleep(20000) WinWaitActive( "Microsoft Excel" ) ControlSend("{ENTER}") Local $sResult8 = _Excel_RangeRead($oWorkbook8, Default, "F1") $sResult8 = $Var_1 _Excel_RangeWrite($oWorkbook8, Default, $sResult8, "F1") ; Runs the macro to update the Pivot table ControlSend("!LPM") ControlSend("UpdatePivot") ControlSend("!R") sleep(20000) WinWaitActive( "Microsoft Excel" ) ControlSend("{ENTER}") ; Runs the macro to update the Pivot table ControlSend("!LPM") ControlSend("Hide") ControlSend("!R") sleep(10000) WinWaitActive( "Microsoft Excel" ) ControlSend("{ENTER}") ; Saves the Main File WinWaitActive("Microsoft Excel - Per Region_Same Store Sales Transactions.xlsm") ControlSend("^s") Sleep(5000) ;Generates the file to ControlSend to TB ControlSend("!FA") WinWaitActive("Save As") ControlSend ("S:\Finance\Automated-Reports\Period Reports\P" & $sResult8 & "\P" & $sResult8 & " - Per Region_Same Store Sales Transactions.xlsm" ) ControlSend("!S") Sleep(5000) ControlSend("{TAB}") ControlSend("{ENTER}") _Excel_BookClose($oWorkbook8, True ) Sleep(3000) EndFunc Func RegionSystem($oApp9) Local $sWorkbook = "S:\Finance\Automated-Reports\Sales Reports\Per Region_System Sales & Transactions.xlsm" Local $oWorkbook9 = _Excel_BookOpen($oApp9, $sWorkbook) ;Local $oWorkbook = _Excel_BookOpen($oAppl, $sWorkbook, Default, Default, True) ; Force Excel Window to be active WinActivate ( "Microsoft Excel - Per Region_System Sales & Transactions.xlsm" ) ; Runs the macro to update the Pivot table ControlSend("!LPM") ControlSend("Unhide") ControlSend("!R") sleep(20000) WinWaitActive( "Microsoft Excel" ) ControlSend("{ENTER}") Local $sResult9 = _Excel_RangeRead($oWorkbook9, Default, "F1") $sResult9 = $Var_1 _Excel_RangeWrite($oWorkbook9, Default, $sResult9, "F1") ; Runs the macro to update the Pivot table ControlSend("!LPM") ControlSend("UpdatePivot") ControlSend("!R") sleep(20000) WinWaitActive( "Microsoft Excel" ) ControlSend("{ENTER}") ; Runs the macro to update the Pivot table ControlSend("!LPM") ControlSend("Hide") ControlSend("!R") sleep(10000) WinWaitActive( "Microsoft Excel" ) ControlSend("{ENTER}") ; Saves the Main File WinWaitActive("Microsoft Excel - Per Region_System Sales & Transactions.xlsm") ControlSend("^s") Sleep(5000) ;Generates the file to ControlSend to TB ControlSend("!FA") WinWaitActive("Save As") ControlSend ("S:\Finance\Automated-Reports\Period Reports\P" & $sResult9 & "\P" & $sResult9 & " - Per Region_System Sales & Transactions.xlsm" ) ControlSend("!S") Sleep(5000) ControlSend("{TAB}") ControlSend("{ENTER}") _Excel_BookClose($oWorkbook9, True ) Sleep(3000) EndFunc Func UnitSSSG($oApp10) Local $sWorkbook = "S:\Finance\Automated-Reports\Sales Reports\Per Unit_SSS and Transactions.xlsm" Local $oWorkbook10 = _Excel_BookOpen($oApp10, $sWorkbook) ;Local $oWorkbook = _Excel_BookOpen($oAppl, $sWorkbook, Default, Default, True) ; Force Excel Window to be active WinActivate ( "Microsoft Excel - Per Unit_SSS and Transactions.xlsm" ) ; Runs the macro to update the Pivot table ControlSend("!LPM") ControlSend("Unhide") ControlSend("!R") sleep(20000) WinWaitActive( "Microsoft Excel" ) ControlSend("{ENTER}") Local $sResult10 = _Excel_RangeRead($oWorkbook10, Default, "F1") $sResult10 = $Var_1 _Excel_RangeWrite($oWorkbook10, Default, $sResult10, "F1") ; Runs the macro to update the Pivot table ControlSend("!LPM") ControlSend("UpdatePivot") ControlSend("!R") sleep(20000) WinWaitActive( "Microsoft Excel" ) ControlSend("{ENTER}") ; Runs the macro to update the Pivot table ControlSend("!LPM") ControlSend("Hide") ControlSend("!R") sleep(80000) WinWaitActive( "Microsoft Excel" ) ControlSend("{ENTER}") ; Saves the Main File WinWaitActive("Microsoft Excel - Per Unit_SSS and Transactions.xlsm") ControlSend("^s") Sleep(5000) ;Generates the file to ControlSend to TB ControlSend("!FA") WinWaitActive("Save As") ControlSend ("S:\Finance\Automated-Reports\Period Reports\P" & $sResult10 & "\P" & $sResult10 & " - Per Unit_SSS and Transactions.xlsm" ) ControlSend("!S") Sleep(5000) ControlSend("{TAB}") ControlSend("{ENTER}") _Excel_BookClose($oWorkbook10, True ) Sleep(3000) EndFunc Func UnitSystem($oApp11) Local $sWorkbook = "S:\Finance\Automated-Reports\Sales Reports\Per Unit_System Sales and Transactions.xlsm" Local $oWorkbook11 = _Excel_BookOpen($oApp11, $sWorkbook) ;Local $oWorkbook = _Excel_BookOpen($oAppl, $sWorkbook, Default, Default, True) ; Force Excel Window to be active WinActivate ( "Microsoft Excel - Per Unit_System Sales and Transactions.xlsm" ) ; Runs the macro to update the Pivot table ControlSend("!LPM") ControlSend("Unhide") ControlSend("!R") sleep(20000) WinWaitActive( "Microsoft Excel" ) ControlSend("{ENTER}") Local $sResult11 = _Excel_RangeRead($oWorkbook11, Default, "F1") $sResult11 = $Var_1 _Excel_RangeWrite($oWorkbook11, Default, $sResult11, "F1") ; Runs the macro to update the Pivot table ControlSend("!LPM") ControlSend("UpdatePivot") ControlSend("!R") sleep(20000) WinWaitActive( "Microsoft Excel" ) ControlSend("{ENTER}") ; Runs the macro to update the Pivot table ControlSend("!LPM") ControlSend("Hide") ControlSend("!R") sleep(80000) WinWaitActive( "Microsoft Excel" ) ControlSend("{ENTER}") ; Saves the Main File WinWaitActive("Microsoft Excel - Per Unit_System Sales and Transactions.xlsm") ControlSend("^s") Sleep(5000) ;Generates the file to ControlSend to TB ControlSend("!FA") WinWaitActive("Save As") ControlSend ("S:\Finance\Automated-Reports\Period Reports\P" & $sResult11 & "\P" & $sResult11 & " - Per Unit_System Sales and Transactions.xlsm" ) ControlSend("!S") Sleep(5000) ControlSend("{TAB}") ControlSend("{ENTER}") _Excel_BookClose($oWorkbook11, True ) Sleep(3000) EndFunc Func Market($oApp12) Local $sWorkbook = "S:\Finance\Automated-Reports\Sales Reports\Sales_Final_Report_Monthly-Q0F.xlsm" Local $oWorkbook12 = _Excel_BookOpen($oApp12, $sWorkbook) ;Local $oWorkbook = _Excel_BookOpen($oAppl, $sWorkbook, Default, Default, True) ; Force Excel Window to be active WinActivate ( "Microsoft Excel - Sales_Final_Report_Monthly-Q0F.xlsm" ) ; Runs the macro to update the Pivot table ControlSend("!LPM") ControlSend("Unhide") ControlSend("!R") sleep(20000) WinWaitActive( "Microsoft Excel" ) ControlSend("{ENTER}") Local $sResult12 = _Excel_RangeRead($oWorkbook12, Default, "F1") $sResult12 = $Var_1 _Excel_RangeWrite($oWorkbook12, Default, $sResult12, "F1") Sleep(10000) ; Runs the macro to update the Pivot table ControlSend("!LPM") ControlSend("Period_changer") ControlSend("!R") sleep(3000000) WinWaitActive( "Microsoft Excel" ) ControlSend("{ENTER}") ; Runs the macro to update the Pivot table ControlSend("!LPM") ControlSend("Hide") ControlSend("!R") sleep(10000) WinWaitActive( "Microsoft Excel" ) ControlSend("{ENTER}") ; Saves the Main File WinWaitActive("Microsoft Excel - Sales_Final_Report_Monthly-Q0F.xlsm") ControlSend("^s") Sleep(5000) ;Generates the file to ControlSend to TB ControlSend("!FA") WinWaitActive("Save As") ControlSend ("S:\Finance\Automated-Reports\Period Reports\P" & $sResult12 & "\P" & $sResult12 & " - Sales_Final_Report_Monthly-Q0F.xlsm" ) ControlSend("!S") Sleep(5000) ControlSend("{TAB}") ControlSend("{ENTER}") _Excel_BookClose($oWorkbook12, True ) Sleep(3000) EndFunc Func StoreCount($oApp3) Local $sWorkbook = "S:\Finance\Automated-Reports\Sales Reports\Per Brand_Per Region Store Count.xlsm" Local $oWorkbook2 = _Excel_BookOpen($oApp3, $sWorkbook) ;Local $oWorkbook = _Excel_BookOpen($oAppl, $sWorkbook, Default, Default, True) ; Force Excel Window to be active WinActivate ( "Microsoft Excel - Per Brand_Per Region Store Count.xlsm" ) ; Runs the macro to update the Pivot table ControlSend("!LPM") ControlSend("Unhide") ControlSend("!R") sleep(20000) WinWaitActive( "Microsoft Excel" ) ControlSend("{ENTER}") Local $sResult3 = _Excel_RangeRead($oWorkbook2, Default, "F1") $sResult3 = $Var_1 _Excel_RangeWrite($oWorkbook2, Default, $sResult3, "F1") ; Runs the macro to update the Pivot table ControlSend("!LPM") ControlSend("Period_changer") ControlSend("!R") sleep(20000) WinWaitActive( "Microsoft Excel" ) ControlSend("{ENTER}") ; Runs the macro to update the Pivot table ControlSend("!LPM") ControlSend("Hide") ControlSend("!R") sleep(10000) WinWaitActive( "Microsoft Excel" ) ControlSend("{ENTER}") ; Saves the Main File WinWaitActive("Microsoft Excel - Per Brand_Per Region Store Count.xlsm") ControlSend("^s") Sleep(5000) ;Generates the file to ControlSend to TB ControlSend("!FA") WinWaitActive("Save As") ControlSend ("S:\Finance\Automated-Reports\Period Reports\P" & $sResult3 & "\P" & $sResult3 & " - Per Brand_Per Region Store Count.xlsm" ) ControlSend("!S") Sleep(5000) ControlSend("{TAB}") ControlSend("{ENTER}") ;_Excel_Close($oExcel,true,true) ;Close excel??? ;_Excel_BookClose($oWorkbook2, True ) ;Sleep(3000) ControlSend("!fx") EndFunc ; End of Function Im trying to get this script to work on a locked computer. it works fine without the computer being locked. Ive trying the controlsend as you see but i couldnt get it to work like with the normal send. please help Link to comment Share on other sites More sharing options...
jdelaney Posted March 21, 2014 Share Posted March 21, 2014 (edited) This is why: http://www.autoitscript.com/wiki/FAQ#Why_doesn.27t_my_script_work_on_a_locked_workstation.3F Use the excel functions, not the control|Win functions...the API will get around such issues. Edited March 21, 2014 by jdelaney IEbyXPATH-Grab IE DOM objects by XPATH IEscriptRecord-Makings of an IE script recorder ExcelFromXML-Create Excel docs without excel installed GetAllWindowControls-Output all control data on a given window. Link to comment Share on other sites More sharing options...
gassis Posted March 24, 2014 Author Share Posted March 24, 2014 Can you please give me some example of what the excel functions are ? Link to comment Share on other sites More sharing options...
water Posted March 24, 2014 Share Posted March 24, 2014 The Excel functions he is talking about are those listed at the tope of the "Excel Rewrite.au3" include file. If you need more functions you have to write them yourself (I will help if possible). BTW: Calling _Excel_Open 13 times doesn't help because you get 13 references to the same Excel instance. What do you want to achieve? My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki  Link to comment Share on other sites More sharing options...
water Posted March 24, 2014 Share Posted March 24, 2014 BTW: Could you please give meaningful titles to your threads? Everyone on this forum is searching for help My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki  Link to comment Share on other sites More sharing options...
gassis Posted March 24, 2014 Author Share Posted March 24, 2014 What we are trying to do is to have a bunch of excel pivottables update to the right period every month without us having to do so . so we have a excel file that will create the vairable number of that period and then that vairable will carry over to each file an update the pivot tables to that period . Link to comment Share on other sites More sharing options...
water Posted March 24, 2014 Share Posted March 24, 2014 (edited) Let's do it step by step. What does the collowing code do? ControlSend($sWorkbook,"","","{ALT}LPM") ControlSend($sWorkbook,"","","UpdateCurrent") ControlSend($sWorkbook,"","","!R") sleep(10000) WinWaitActive( "Microsoft Excel" ) ControlSend("{ENTER}") Edit: I have installed a german Excel so the shortcuts don't work for me. Edited March 24, 2014 by water My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki  Link to comment Share on other sites More sharing options...
jdelaney Posted March 24, 2014 Share Posted March 24, 2014 Alt LPM opens the macros window. http://stackoverflow.com/questions/10232150/run-excel-macro-from-outside-excel-using-vbscript-from-command-line I'm certain there is an excel rewrite function for that though...but the above shows the VBA way, which can be easily converted to autoit. IEbyXPATH-Grab IE DOM objects by XPATH IEscriptRecord-Makings of an IE script recorder ExcelFromXML-Create Excel docs without excel installed GetAllWindowControls-Output all control data on a given window. Link to comment Share on other sites More sharing options...
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